Custom VBA Functions (Events)Locate this document in the navigation structure

You can customize your Visual Basic for Applications (VBA) code by associating the events listed below.

Enter your code in modules.
  • BEFORE_CONTEXTCHANGE

    Use this function to execute a custom operation before changing the context.

  • AFTER_CONTEXTCHANGE

    Use this function to execute a custom operation after changing the context.

  • BEFORE_REFRESH

    Use this function to execute a custom operation before the system refreshes the data.

  • AFTER_REFRESH
    Use this function to execute a custom operation after the system refreshes the data.
    Note

    After each refresh action, a dialog box will appear and display the text "Refresh successful".

    Enter the following lines in a module: 

    Option Explicit
Function AFTER_REFRESH()
  MsgBox "Refresh successful"
  AFTER_REFRESH = True
End Function
  • BEFORE_SAVE

    Use this function to execute a custom operation before the system saves data into the database.

  • AFTER_SAVE

    Use this function to execute a custom operation after the system saves data into the database.

  • BEFORE_EXPAND

    Applies to: Planning and Consolidation connections.

    Use this function to execute a custom operation before the system expands the EVDRE reports.

  • AFTER_EXPAND

    Applies to: Planning and Consolidation connections.

    Use this function to execute a custom operation after the system expands the EVDRE reports.

  • AFTER_WORKBOOK_OPEN

    Use this function to execute a custom operation after opening an Excel workbook.

    The return values to use for all the above events are: true or false.

  • GetMaxTupleByCellRequest The parameter SmartQueryEngineMaxTupleByCellRequest in the FPMXLClient.dll.config file defines - for EPM functions - the maximum number of tuples (member combinations) by query when a refresh is performed. This parameter cannot be modified during a session for a specific workbook.

    For performance reasons, you might want to change the number of tuples during a session for a specific workbook, for example, if your workbook contains a lot of EPM functions such as EPMRetrieveData, EPMSaveData and EPMSaveComment. To change the number of tuples, use the GetMaxTupleByCellRequest event. The number entered will override the SmartQueryEngineMaxTupleByCellRequest number.

    Note

    Before each refresh action, the number entered will be automatically taken into account instea of the default value in the FPMXLClient.dll.config.

    Enter the following lines in a module: 

    Option Explicit
Function GetMaxTupleByCellRequest()
GetMaxTupleByCellRequest = 50
End Function

    In this example, if 200 tuples (member combinations) are retrieved, 4 queries of 50 tuples will happen on a refresh.