Function documentationVBA Macros Locate this document in the navigation structure

 

You can use VBA macros to define custom functions in Interface for Excel. You should be familiar with building VBA macros to use these functions.

The following syntax is included within the routine to call a specific command:

Application.Run "mnu_{command}"

In addition, you can add a button to an Excel worksheet with an assigned macro name starting with MNU_. This enables a user to select a button for a single specified command.

The following is an example for refreshing a workbook: MNU_eTOOLS_REFRESH

The solution provides the following VBA functions to execute commands before and after MNU commands:

  • BEFORE_CHANGECVW

    You use the BEFORE_CHANGECVW function to execute a custom operation before changing the current view.

    Option Explicit

    Function BEFORE_CHANGECVW(Argument As String)

    'Process content

    MsgBox Argument

    BEFORE_CHANGECVW = True

    End Function

  • AFTER_CHANGECVW

    You use the AFTER_CHANGECVW function to execute a custom operation after changing the current view.

    Function AFTER_CHANGECVW(Argument As String)

    MsgBox Argument

    AFTER_CHANGECVW = True

    End Function

  • BEFORE_REFRESH

    You use the BEFORE_REFRESH function to execute a custom operation after the user changes the Current View.

    Function BEFORE_REFRESH(Argument As String)

    MsgBox Argument

    BEFORE_REFRESH = True

    End Function

  • AFTER_REFRESH

    You use the AFTER_REFRESH function to execute a custom operation after Interface for Excel refreshes data.

    Function AFTER_REFRESH(Argument As String)

    MsgBox Argument

    AFTER_REFRESH = True

    End Function

  • BEFORE_SEND

    You use the BEFORE_SEND function to execute a custom operation before Interface for Excel sends data.

    Function BEFORE_SEND(Argument As String)

    MsgBox Argument

    BEFORE_SEND = True

    End Function

  • AFTER_SEND

    You use the AFTER_SEND function to execute a custom operation after Interface for Excel sends data.

    Function AFTER_SEND(Argument As String)

    MsgBox Argument

    AFTER_SEND = True

    End Function

  • BEFORE_EXPAND

    Besides being used in online reporting, this event can be used to execute custom code at the beginning of a batch process, such as book publishing or offline distribution.

    Function BEFORE_EXPAND(Argument As String)

    MsgBox Argument

    BEFORE_EXPAND = True

    End Function

  • AFTER_EXPAND

    Besides being used in online reporting, this event can be used to execute custom code at the end of a batch process, such as book publishing or offline distribution.

    Function AFTER_EXPAND(Argument As String)

    MsgBox Argument

    AFTER_EXPAND = True

    End Function

VBA Macros Examples

Option Explicit

Private Declare Sub Sleep Lib kernel32 (ByVal dwMilliseconds As Long)

Function AFTER_REFRESH(Argument As String)

MsgBox "After Refresh Event Triggered"

MsgBox Argument

AFTER_REFRESH = True

End Function

Function AFTER_EXPAND(Argument As String)

MsgBox "After Expand Event Triggered"

MsgBox Argument

AFTER_EXPAND = True

End Function

Function AFTER_SEND(Argument As String)

MsgBox "After Send Event Triggered"

MsgBox Argument

AFTER_SEND = True

End Function

Sub CheckSendComplete()

Application.Run "MNU_eSUBMIT_REFRESH"

Dim oExcel As Object

'wait for the return code from the server to know the first send completed

Set oExcel = Application.Run("GetEvents")

Do

DoEvents

Sleep 100

Loop While oExcel.SendResult_Status = False And oExcel.SendResult_Submitted <> -1

MsgBox "Send completed on server"

End Sub