.. meta:: :description lang=en: xlSlim includes many utility functions that make working with Excel easier. Excel Functions =============== Registering modules ------------------- To register a Python module for use within Excel you use the :func:`RegisterPyModule` function: .. py:function:: RegisterPyModule(PythonModulePath, ExecutablePath=None, ModulePrefix=None, UseBackgroundProcess=None, ExtraModuleSearchLocations=None, ExtraDllSearchLocations=None, FunctionNames=None) Registers a Python module for use in Excel. :param PythonModulePath: The full path to the Python module to load. This path may also refer to a remote location, see the link below for details. :type PythonModulePath: str :param ExecutablePath: Optional location of the Python environment to use. :type ExecutablePath: str or None :param ModulePrefix: Optional prefix to apply to the function names. :type ModulePrefix: str or None :param UseBackgroundProcess: Should xlSlim Python code run a seperate process? :type UseBackgroundProcess: bool or None :param ExtraModuleSearchLocations: Extra locations to be added to the Python module search path (sys.path). :type ExtraModuleSearchLocations: str or None :param ExtraDllSearchLocations: Extra locations to be added to the Windows DLL search path (PATH). :type ExtraDllSearchLocations: str or None :param FunctionNames: 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. :type FunctionNames: Excel range or None :param HttpImportProfile: Either the full path of an .ini file to use for remote import profiles, or a string with the profile in ini file format. :type HttpImportProfile: str or None See :doc:`/user/http_imports` for details of importing from Github, Bitbucket, Gists and other remote http locations. See :doc:`environments` for details of the `ExecutablePath` parameter. See :doc:`/user/passing_arrays` for an example using the `ModulePrefix` parameter. See :doc:`/user/subprocess` for an example using the `UseBackgroundProcess` parameter. See :doc:`/user/imports` for an example showing the use of the `ExtraModuleSearchLocations` and `FunctionNames` parameters. Creating modules ---------------- The :func:`CreatePyModule` function can be used to create a Python module from text within Excel: .. py:function:: 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. :param PythonCode: Text to parse and from which to create a Python module. :type PythonCode: str :param ExecutablePath: Optional location of the Python environment to use. :type ExecutablePath: str or None :param ModulePrefix: Optional prefix to apply to the function names. :type ModulePrefix: str or None :param ExtraModuleSearchLocations: Extra locations to be added to the Python module search path (sys.path). :type ExtraModuleSearchLocations: str or None :param ExtraDllSearchLocations: Extra locations to be added to the Windows DLL search path (PATH). :type ExtraDllSearchLocations: str or None :param FunctionNames: 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. :type FunctionNames: Excel range or None :param HttpImportProfile: Either the full path of an .ini file to use for remote import profiles, or a string with the profile in ini file format. :type HttpImportProfile: str or None See :doc:`environments` for details of the `ExecutablePath` parameter. See :doc:`/user/passing_arrays` for an example using the `ModulePrefix` parameter. See :doc:`/user/imports` for an example showing the use of the `ExtraModuleSearchLocations` and `FunctionNames` parameters. Viewing cached objects ---------------------- xlSlim automatically caches Python object. The :func:`ViewPyObject` function can be used to view these objects. .. py:function:: ViewPyObject(key) View a cached Python object. :param key: An xlSlim cached object handle. :type key: str :return: An error message if the object was not found or an array of objects suitable for display in Excel. :rtype: str or list[object] Creating ranges --------------- Often xlSlim functions will expect a list to be passed by using a contiguous Excel range. The :func:`CreateRange` function takes a set of non-adjacent ranges and creates a new Excel range object suitable for passing to xlSlim functions. .. py:function:: CreateRange(range1, range2, ... rangen) Creates a new Excel range from non-adjacent cells. :param range1: An Excel range, can be a single cell or range. :type range1: Excel range :param range2: An Excel range, can be a single cell or range. :type range2: Excel range :param rangen: An Excel range, can be a single cell or range. :type rangen: Excel range :return: A new Excel range object. :rtype: 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 :func:`PythonDetails` function: .. py:function:: PythonDetails() Returns comprehensive details about the Python environment, including the module search paths and loaded packages. :return: Details of the Python environment. :rtype: list[str] Clear Caches ------------ To remove all cached objects you can use the :func:`ClearCaches` function: .. py: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 :func:`ClearCacheItem` function: .. py:function:: ClearCacheItem(key) Removes a specific cached object from xlSlim's internal memory caches. :param key: An xlSlim cached object handle. :type key: str :return: An error message if the object was not found else a message confirming the object removal. :rtype: str Use the bundled Python ---------------------- xlSlim has a bundled version of Python 3.10. This bundled Python is used by default. However you can specify another Python environment when registering modules with :func:`RegisterPyModule` or :func:`CreatePyModule`. The :func:`ClearSavedPythonEnv()` function will revert back to using the bundled Python. .. py:function:: ClearSavedPythonEnv() Reverts xlSlim to using the bundled Python environment. Get the current workbook location --------------------------------- The :func:`WorkbookLocation()` function can be used to get the current workbook's location. .. py:function:: WorkbookLocation() Returns the location of the current Excel workbook. Unlike the standard Excel `CELL()` function this function is not volatile. :return: The path (excluding filename) of the current workbook. :rtype: str Get the log location -------------------- The :func:`LogLocation()` function can be used to get the location of xlSlim's log file. .. py:function:: LogLocation() Returns the location of xlSlim's log file. The log file is useful for troubleshooting. :return: The path (including filename) of the log file :rtype: str Check the licensing status -------------------------- The :func:`IsPremiumLicensed()` function can be used to check if xlSlim has a valid premium licence. See :doc:`/reference/licensing`. .. py:function:: IsPremiumLicensed() Checks if xlSlim has a premium licence. :return: True if a valid premium licence was found, else false. :rtype: bool Check the licence expiry ------------------------ The :func:`LicenseExpiry()` function can be used to check the expiry date of any existing premium licence. .. py:function:: LicenseExpiry() Gets the expiry of the premium licence. :return: The expiry time of any premium licence found. :rtype: datetime Activate a licence ------------------ .. _xlslim.com: https://www.xlslim.com/ The :func:`ActivatePremiumLicense()` function can be used to request a trial licence or activate a premium licence. See :doc:`/user/licensing` .. py:function:: ActivatePremiumLicense(key) Gets the expiry of the premium licence. :param key: A premium licence key purchased from `xlslim.com`_. :type key: str :return: The activation result. :rtype: str Deactivate a licence -------------------- The :func:`DeactivatePremiumLicense()` function can be used to deactivate a premium licence so it can be used on another PC. See :doc:`/user/licensing` .. py:function:: DeactivatePremiumLicense() Deactivates any current premium licence. :return: The deactivation result. :rtype: str Version ------- To get the version of xlSlim used in Excel you can use the :func:`XlSlimVersion` function: .. py:function:: XlSlimVersion() Returns the version of xlSlim in use. :return: The xlSlim version in use. :rtype: str GetAttr ------- The :func:`GetAttr()` function efficiently gets attributes from cached Python objects. .. py:function:: GetAttr(cacheKey, attribute, defaultValue) :param cacheKey: An xlSlim cached object handle. :type cacheKey: str :param attribute: The name of the attribute to retrieve. :type attribute: str :return: 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. :rtype: str or object GetItem ------- The :func:`GetItem()` function efficiently gets items from cached Python dictionaries. .. py:function:: GetItem(cacheKey, dictKey, defaultValue) :param cacheKey: An xlSlim cached dictionary handle. :type cacheKey: str :param dictKey: The key of the item to retrieve. :type dictKey: str :return: 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. :rtype: str or object Link a Python Function to Key Combinations ------------------------------------------ .. _OnKey: https://learn.microsoft.com/en-us/office/vba/api/excel.application.onkey 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. .. py:function:: LinkPyFunctionToKeys(keyCombination, functionName) :param keyCombination: The combination of keys, for example ^+P is Ctrl+Shift+P. :type keyCombination: str :param functionName: 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 :func:`RegisterPyModule` or :func:`CreatePyModule`. :type functionName: str :return: A confirmation that the combination of keys have been set or cleared. :rtype: str Schedule a Python Function -------------------------- .. _OnTime: https://learn.microsoft.com/en-us/office/vba/api/excel.application.ontime 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. .. py:function:: SchedulePyFunction(earliestTime, functionName, latestTime, schedule) :param earliestTime: When to run the function. :type earliestTime: datetime :param functionName: The name of the function to run. The function cannot take any arguments. The function must have already been registered with :func:`RegisterPyModule` or :func:`CreatePyModule`. :type functionName: str :param latestTime: Optionally set latest time at which the function can be run. :type latestTime: datetime :param cancelSchedule: Optionally set to TRUE to cancel a scheduled run. :type cancelSchedule: bool :type functionName: str :return: A confirmation that the function has been scheduled or cleared. :rtype: str Add a Python Event Handler to process Excel events -------------------------------------------------- .. _VBA object and event model: https://learn.microsoft.com/en-us/office/vba/api/overview/excel/object-model 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. .. py:function:: AddPyEventHandler(evtHandlerClass, wbName, sheetName) :param evtHandlerClass: The xlSlim cache key of an event handler object. Note that the object itself is not used, only the class data. :type evtHandlerClass: str :param wbName: Optionally apply the event handler to this workbook. By default the event handler is applied at the application level. :type wbName: str :param sheetName: Optionally apply the event handler to this sheet. By default the event handler is applied at the application level. :type sheetName: str Update the Right-Click Context Menu ----------------------------------- .. _page: https://bert-toolkit.com/imagemso-list.html The :func:`UpdateContextMenu` function can be used add items to the right-click context menu and link these items to functions. .. py:function:: UpdateContextMenu(Name, MenuItems, Image=None) Updates the Excel right-click context menu. :param Name: The top level name for the menu. :type Name: str :param MenuItems: 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 :func:`RegisterPyModule` or :func:`CreatePyModule`. :type MenuItems: Excel range :param Image: Optional image to use with the Name. :type Image: str or None See :doc:`/user/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 ----------------------------------- .. _page: https://bert-toolkit.com/imagemso-list.html The :func:`UpdateRibbonMenu` function can be used add items to the ribbon menu and link these items to functions. .. py:function:: UpdateRibbonMenu(Name, MenuItems, Image=None) Updates the Excel ribbon menu. :param Name: The top level name for the menu. :type Name: str :param MenuItems: 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 :func:`RegisterPyModule` or :func:`CreatePyModule`. :type MenuItems: Excel range :param Image: Optional image to use with the Name. :type Image: str or None See :doc:`/user/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 :func:`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. .. py:function:: ActivateInteractiveMode(ExecutablePath=None, ExtraModuleSearchLocations=None, ExtraDllSearchLocations=None) Activates Interactive Python mode. :param ExecutablePath: Optional location of the Python environment to use. :type ExecutablePath: str or None :param ExtraModuleSearchLocations: Extra locations to be added to the Python module search path (sys.path). :type ExtraModuleSearchLocations: str or None :param ExtraDllSearchLocations: Extra locations to be added to the Windows DLL search path (PATH). :type ExtraDllSearchLocations: str or None See :doc:`environments` for details of the `ExecutablePath` parameter. Deactivating Interactive Mode ----------------------------- The :func:`DeactivateInteractiveMode` function disables interactive Python mode. Keyboard shortcuts are reset to their defaults. .. py:function:: DeactivateInteractiveMode() Deactivates Interactive Python mode.