In Visual Basic for Application (VBA), you can call any XLGL worksheet function and a number of useful XLGL macros by using the Application.Run
Excel command.
For example, you can get the balance of account 4010 using the XGL Worksheet Function:
Dim balance as Double
balance = Application.Run("XGL", "4010")
Make sure to supply all parameters in the order specified in the documentation of each function. Empty arguments can be an empty string. For example, to get the total amount of all transactions in account 4010-0300, where 0300 is the department, you would use:
Dim amount as Double
amount = Application.Run("XGLP", "4010", "", "", "0300")
Predefined macros
Use the following commands to achieve actions normally done using the XLGL Ribbon:
- Open the Connection panel (to actually open a connection, use the XConnect worksheet function)
Application.Run "XLGL.Connect"
- Open the report panel
Application.Run "XLGL.Reports"
- Refresh the workbook
Application.Run "XLGL.Refresh"
- Recalculate the workbook
Application.Run "XLGL.Recalculate"
- Freeze the active workbook
Application.Run "XLGL.Freeze"
To prevent a confirmation message being displayed, use
Application.Run "XLGL.Freeze", False
Freeze the active worksheet
Application.Run "XLGL.FreezeWorksheet"
To prevent a confirmation message being displayed, use
Application.Run "XLGL.FreezeWorksheet", False
Freeze the selected cell range
Application.Run "XLGL.FreezeRange"
To prevent a confirmation message being displayed, use
Application.Run "XLGL.FreezeRange", False
Open the drilldown window for the selected range
Application.Run "XLGL.Drilldown"
Hide rows and columns containing only zeros
Application.Run "XLGL.HideZeros"
Hide rows containing only zeros
Application.Run "XLGL.HideZeroRows"
Hide columns containing only zeros
Application.Run "XLGL.HideZeroColumns"
Unhide rows and columns
Application.Run "XLGL.Unhide"
Unhide rows
Application.Run "XLGL.UnhideRows"
Unhide columns
Application.Run "XLGL.UnhideColumns"
Prepare print layout for active worksheet
Application.Run "XLGL.PrintLayout"
Create a copy of the active worksheet
Application.Run "CopyCurrentWorksheet"
Switch Language
Application.Run "XLGL.SwitchLanguage"