Excel Functions¶

Registering modules¶

To register a Python module for use within Excel you use the RegisterPyModule() function:

RegisterPyModule(PythonModulePath, ExecutablePath=None, ModulePrefix=None, UseBackgroundProcess=None, ExtraModuleSearchLocations=None, ExtraDllSearchLocations=None, FunctionNames=None)¶

Registers a Python module for use in Excel.

Parameters:

PythonModulePath (str) – The full path to the Python module to load. This path may also refer to a remote location, see the link below for details.
ExecutablePath (str or None) – Optional location of the Python environment to use.
ModulePrefix (str or None) – Optional prefix to apply to the function names.
UseBackgroundProcess (bool or None) – Should xlSlim Python code run a seperate process?
ExtraModuleSearchLocations (str or None) – Extra locations to be added to the Python module search path (sys.path).
ExtraDllSearchLocations (str or None) – Extra locations to be added to the Windows DLL search path (PATH).
FunctionNames (Excel range or None) – An Excel range specifying which functions to import. By default all functions in a module are imported. A single column range can specify only the functions to import. A two column range can specify the Python functions in the first column and the Excel function names in the second column.
HttpImportProfile (str or None) – Either the full path of an .ini file to use for remote import profiles, or a string with the profile in ini file format.

See Remote Module Importing for details of importing from Github, Bitbucket, Gists and other remote http locations. See Python Environments for details of the ExecutablePath parameter. See Passing lists for an example using the ModulePrefix parameter. See Running Python in a background process for an example using the UseBackgroundProcess parameter. See Advanced Module Importing for an example showing the use of the ExtraModuleSearchLocations and FunctionNames parameters.

Creating modules¶

The CreatePyModule() function can be used to create a Python module from text within Excel:

CreatePyModule(PythonCode, ExecutablePath=None, ModulePrefix=None, ExtraModuleSearchLocations=None, ExtraDllSearchLocations=None, FunctionNames=None)¶

Creates a Python module from text and register the module for use in Excel.

Parameters:

PythonCode (str) – Text to parse and from which to create a Python module.
ExecutablePath (str or None) – Optional location of the Python environment to use.
ModulePrefix (str or None) – Optional prefix to apply to the function names.
ExtraModuleSearchLocations (str or None) – Extra locations to be added to the Python module search path (sys.path).
ExtraDllSearchLocations (str or None) – Extra locations to be added to the Windows DLL search path (PATH).
FunctionNames (Excel range or None) – An Excel range specifying which functions to import. By default all functions in a module are imported. A single column range can specify only the functions to import. A two column range can specify the Python functions in the first column and the Excel function names in the second column.
HttpImportProfile (str or None) – Either the full path of an .ini file to use for remote import profiles, or a string with the profile in ini file format.

See Python Environments for details of the ExecutablePath parameter. See Passing lists for an example using the ModulePrefix parameter. See Advanced Module Importing for an example showing the use of the ExtraModuleSearchLocations and FunctionNames parameters.

Viewing cached objects¶

xlSlim automatically caches Python object. The ViewPyObject() function can be used to view these objects.

ViewPyObject(key)¶

View a cached Python object.

Parameters:	key (str) – An xlSlim cached object handle.
Returns:	An error message if the object was not found or an array of objects suitable for display in Excel.
Return type:	str or list[object]

Creating ranges¶

Often xlSlim functions will expect a list to be passed by using a contiguous Excel range. The CreateRange() function takes a set of non-adjacent ranges and creates a new Excel range object suitable for passing to xlSlim functions.

CreateRange(range1, range2, ... rangen)¶

Creates a new Excel range from non-adjacent cells.

Parameters:	range1 (Excel range) – An Excel range, can be a single cell or range. range2 (Excel range) – An Excel range, can be a single cell or range. rangen (Excel range) – An Excel range, can be a single cell or range.
Returns:	A new Excel range object.
Return type:	Excel range

Note

The inputs ranges 1 to n must be the same size for xlSlim to succesfully stitch them together.

Diagnostics¶

To troubleshoot the Python environment used in Excel you can use the PythonDetails() function:

PythonDetails()¶

Returns comprehensive details about the Python environment, including the module search paths and loaded packages.

Returns:	Details of the Python environment.
Return type:	list[str]

Clear Caches¶

To remove all cached objects you can use the ClearCaches() function:

ClearCaches()¶: Removes all cached objects from xlSlim’s internal memory caches.

Clear Cached Item¶

To remove a specific item from object caches you can use the ClearCacheItem() function:

ClearCacheItem(key)¶

Removes a specific cached object from xlSlim’s internal memory caches.

Parameters:	key (str) – An xlSlim cached object handle.
Returns:	An error message if the object was not found else a message confirming the object removal.
Return type:	str

Use the bundled Python¶

xlSlim has a bundled version of Python 3.11. This bundled Python is used by default. However you can specify another Python environment when registering modules with RegisterPyModule() or CreatePyModule(). The ClearSavedPythonEnv() function will revert back to using the bundled Python.

ClearSavedPythonEnv()¶: Reverts xlSlim to using the bundled Python environment.

Get the current workbook location¶

The WorkbookLocation() function can be used to get the current workbook’s location.

WorkbookLocation()¶

Returns the location of the current Excel workbook. Unlike the standard Excel CELL() function this function is not volatile.

Returns:	The path (excluding filename) of the current workbook.
Return type:	str

Get the log location¶

The LogLocation() function can be used to get the location of xlSlim’s log file.

LogLocation()¶

Returns the location of xlSlim’s log file. The log file is useful for troubleshooting.

Returns:	The path (including filename) of the log file
Return type:	str

Check the licensing status¶

The IsPremiumLicensed() function can be used to check if xlSlim has a valid premium licence. See Licensing.

IsPremiumLicensed()¶

Checks if xlSlim has a premium licence.

Returns:	True if a valid premium licence was found, else false.
Return type:	bool

Check the licence expiry¶

The LicenseExpiry() function can be used to check the expiry date of any existing premium licence.

LicenseExpiry()¶

Gets the expiry of the premium licence.

Returns:	The expiry time of any premium licence found.
Return type:	datetime

Activate a licence¶

The ActivatePremiumLicense() function can be used to request a trial licence or activate a premium licence. See Licensing

ActivatePremiumLicense(key)¶

Gets the expiry of the premium licence.

Parameters:	key (str) – A premium licence key purchased from xlslim.com.
Returns:	The activation result.
Return type:	str

Deactivate a licence¶

The DeactivatePremiumLicense() function can be used to deactivate a premium licence so it can be used on another PC. See Licensing

DeactivatePremiumLicense()¶

Deactivates any current premium licence.

Returns:	The deactivation result.
Return type:	str

Version¶

To get the version of xlSlim used in Excel you can use the XlSlimVersion() function:

XlSlimVersion()¶

Returns the version of xlSlim in use.

Returns:	The xlSlim version in use.
Return type:	str

GetAttr¶

The GetAttr() function efficiently gets attributes from cached Python objects.

GetAttr(cacheKey, attribute, defaultValue)¶

Parameters:	cacheKey (str) – An xlSlim cached object handle. attribute (str) – The name of the attribute to retrieve.
Returns:	An error message if the object was not found. If the attribute exists it is returned, else if a defaultValue was supplied this is returned, failing which an error message is returned.
Return type:	str or object

GetItem¶

The GetItem() function efficiently gets items from cached Python dictionaries.

GetItem(cacheKey, dictKey, defaultValue)¶

Parameters:	cacheKey (str) – An xlSlim cached dictionary handle. dictKey (str) – The key of the item to retrieve.
Returns:	An error message if the cached dictionary was not found. If an item is stored against the key then the item is returned, else if a defaultValue was supplied this is returned, failing which an error message is returned.
Return type:	str or object

Link a Python Function to Key Combinations¶

Excel can be set to run a Python function when specific key combinations are pressed. Refer to the Microsoft OnKey documentation for full details of how to specify the key combinations.

LinkPyFunctionToKeys(keyCombination, functionName)¶

Parameters:	keyCombination (str) – The combination of keys, for example ^+P is Ctrl+Shift+P. functionName (str) – The name of the function to run. The function cannot take any arguments. If the function name is omitted the key combination reverts to normal behaviour. The function must have already been registered with `RegisterPyModule()` or `CreatePyModule()`.
Returns:	A confirmation that the combination of keys have been set or cleared.
Return type:	str

Schedule a Python Function¶

Excel can run a Python function at a specified future time. Refer to the Microsoft OnTime documentation for the conditions under which the scheduled execution will run.

SchedulePyFunction(earliestTime, functionName, latestTime, schedule)¶

Parameters:	earliestTime (datetime) – When to run the function. functionName (str) – The name of the function to run. The function cannot take any arguments. The function must have already been registered with `RegisterPyModule()` or `CreatePyModule()`. latestTime (datetime) – Optionally set latest time at which the function can be run. cancelSchedule (bool) – Optionally set to TRUE to cancel a scheduled run.
Returns:	A confirmation that the function has been scheduled or cleared.
Return type:	str

Add a Python Event Handler to process Excel events¶

Excel raises events when various actions occur, such as activating a worksheet or changing a cell. xlSlim allows you to run Python code in response to these events. The internals are identical to Excel’s VBA event handling. Microsoft’s VBA object and event model documentation is a valuable resource when researching available events.

AddPyEventHandler(evtHandlerClass, wbName, sheetName)¶

Parameters:

evtHandlerClass (str) – The xlSlim cache key of an event handler object. Note that the object itself is not used, only the class data.
wbName (str) – Optionally apply the event handler to this workbook. By default the event handler is applied at the application level.
sheetName (str) – Optionally apply the event handler to this sheet. By default the event handler is applied at the application level.

Update the Right-Click Context Menu¶

The UpdateContextMenu() function can be used add items to the right-click context menu and link these items to functions.

UpdateContextMenu(Name, MenuItems, Image=None)¶

Updates the Excel right-click context menu.

Parameters:

Name (str) – The top level name for the menu.
MenuItems (Excel range) – An Excel range specifying the menu items. The first column must be the menu labels and the second column must be the function names. An optional third column can contain the images for the menu items. The functions cannot take any arguments. The functions must be registered with RegisterPyModule() or CreatePyModule().
Image (str or None) – Optional image to use with the Name.

See Adding Context and Ribbon Menus for an example using the UpdateContextMenu function.

Note

The images are limited to specific images built into Excel. The available images can be found by searching for “imagemso gallery”, at the time of writing this page has a good list of the available images.

Update the Ribbon Menu¶

The UpdateRibbonMenu() function can be used add items to the ribbon menu and link these items to functions.

UpdateRibbonMenu(Name, MenuItems, Image=None)¶

Updates the Excel ribbon menu.

Parameters:

Name (str) – The top level name for the menu.
MenuItems (Excel range) – An Excel range specifying the menu items. The first column must be the menu labels and the second column must be the function names. An optional third column can contain the images for the menu items. The functions cannot take any arguments. The functions must be registered with RegisterPyModule() or CreatePyModule().
Image (str or None) – Optional image to use with the Name.

See Adding Context and Ribbon Menus for an example using the UpdateRibbonMenu function.

Note

The images are limited to specific images built into Excel. The available images can be found by searching for “imagemso gallery”, at the time of writing this page has a good list of the available images.

Activating Interactive Mode¶

The ActivateInteractiveMode() function enables interactive Python mode. In interactive mode any cells beginning with “#py” are treated as Python code and run within Excel. Cells can be executed with Ctrl+Enter and Shift+Enter, just like Jupyter notebooks. All Python cells can be run with Ctrl+R. Double clicking on cells opens a Python code editor.

ActivateInteractiveMode(ExecutablePath=None, ExtraModuleSearchLocations=None, ExtraDllSearchLocations=None)¶

Activates Interactive Python mode.

Parameters:	ExecutablePath (str or None) – Optional location of the Python environment to use. ExtraModuleSearchLocations (str or None) – Extra locations to be added to the Python module search path (sys.path). ExtraDllSearchLocations (str or None) – Extra locations to be added to the Windows DLL search path (PATH).

See Python Environments for details of the ExecutablePath parameter.

Deactivating Interactive Mode¶

The DeactivateInteractiveMode() function disables interactive Python mode. Keyboard shortcuts are reset to their defaults.

DeactivateInteractiveMode()¶: Deactivates Interactive Python mode.

Running DuckDB SQL¶

The DuckDbQuery() function allows SQL statements to be executed within DuckDB.

DuckDbQuery(SQL, DbFile=None)¶

Executes the supplied SQL statements.

Parameters:	SQL (str) – The SQL statements to execute. DbFile (str or None) – If not supplied then the statements run in a temporary in-memory database. Else the function creates or opens a DuckDB database at the supplied location and runs the statements within this database.
Returns:	An error message if the SQL failed or an array of objects suitable for display in Excel.
Return type:	str or list[object]

See DuckDB SQL for details of DuckDB SQL statements.