Using XLGL in a VBA macro

Home > XLGL Manual > Advanced Topics > Using XLGL in a VBA macro

Using XLGL in a VBA macro

Thursday, August 17, 2023

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"
```

Application.Run "XLGL.Freeze", False

Freeze the active worksheet
```
Application.Run "XLGL.FreezeWorksheet"
```

Application.Run "XLGL.FreezeWorksheet", False

Freeze the selected cell range
```
Application.Run "XLGL.FreezeRange"
```

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"
```

Was this article helpful?

Thank you for your feedback

Articles in this section

Using Multiple Sage 50 Companies on a Report
Using XLGL in a VBA macro
Automating XLGL Tasks on a Schedule

Can't find what you're looking for?

You can Contact us so we can address your question.