Excel Power Users, Rejoice! VBA Support is here!
Everyone has a language. My first language is French, but I also speak English and understand Spanish. Just like you and I, Excel has a language, the one it can understand. That language is called VBA (Visual Basic for Applications). By using VBA, you can tell Excel what to do, automate a boring report, clean-up and format data, or even program a multi-player Tetris game.
I joined Velixo just this summer and this is my largest contribution to the product so far. Similar to Excel understanding VBA, I have always had an aptitude for understanding the languages of software and those which make our technology better. I’m very excited to be part of Velixo and to help enhance the product with new developments. As I have been the one to implement this new functionality, I am very excited to tell you about all the benefits this will bring to your reports.
While you’ve been able to use VBA from day 1 with Velixo, key features in Velixo such as Smart Refresh, Report Distribution and Writeback were not accessible from VBA. Now with Velixo Reports 4.3, we can officially support these features and more, meaning that you can:
- Generate a dynamic P&L for your 500-store enterprise having one column per store
- Create a macro that dynamically expands any cell to show a detail for all the account/subaccount combinations directly on the current sheet
- Schedule report distribution
- Automatically import multiple GL transactions
- …and much more!
Getting Started
You can use Velixo’s VBA functions in your macros, by adding a reference to Velixo, VBA object. This step needs to be repeated once for every workbook where you want to use the functions.
To do so, first, press Alt+F11 from an opened Excel workbook to show the Microsoft Visual Basic for Applications window.
Click Tools -> References…
Scroll the list and check Velixo Reports. Click OK.
How to use Velixo’s VBA functions
First declare an object As Velixo_Reports.VBA and use CreateObject to instantiate it.
Dim velixoObj As Velixo_Reports.VBA Set velixoObj = CreateObject("Velixo.Reports.Vba")
You are ready to use the VBA functions. You can see the available functions with intellisense:
Putting It All Together
To illustrate the power of VBA, I have created a simple macro-enabled workbook with two sheets. You can download this file here.
In the first one, (P&L Expansion Demo), we are using the GetGLDrilldownSummary() function to retrieve a list of all the accounts/subaccounts referred to by the selected formula, and expand the report:
In the second example, we are automating the GL Writeback feature by refreshing the current sheet and importing it afterwards:
You could apply the same principles to generate multiple journal transactions in a single click.
Additional documentation and examples can be found on support.velixo.com.
Closing Notes
I would like to thank Tim Rodman of Aktion and Morgan Taylor of VPI Technology for their precious time and feedback during the development of this enhancement.
To learn more about VBA support in Velixo, check the Visual Basic for Applications Functions Reference in our support portal. Our Consulting & Services team is also able to help you with your macros and advanced reporting requirements; just e-mail us at [email protected].