The Excel Add-In reporting tool of EPM 10 combines the features of Excel with EPM functionality. A feature within Excel that is sometimes useful to meet more advanced reporting needs is macros programmed using Visual Basic (VBA). This can achieve features that are not achievable through EPM 10 functionality.
VBA is a standard Excel feature. Its use in reports and input schedules is made possible by the fact that the Excel Add-In includes a large number of commands that run EPM 10 functionality. An example is that the code “EPMObj.RefreshActiveSheet” refreshes the active worksheet.
The most common way to run such VBA is to attach macros to buttons, created with standard Excel functionality. The use of macro buttons can improve the usability of reports because they are a user friendly feature and can avoid the need to train users to use the EPM toolbar in Excel.
A selection of functionality that can be achieved through VBA is listed below:
- Refreshing worksheets in a specific order. This may be necessary if data from a report in one worksheet is required to feed into another.
- Displaying a message box. An example would be to warn the user of the risk of overwriting data and providing a Yes / No message box before performing a save.
- Use of VBA “If” statements to determine what steps are taken. An example is checking the validity of user selections before performing a report refresh.
- Checking the validity of data in an input schedule before it is saved. VBA can be written to perform checks on the data in the schedule and then perform further actions such as displaying message boxes. An example is that if a set of figures input need to balance to zero, a check can be performed before the save.
- Hiding of rows or columns in a way that cannot be achieved using standard suppression functionality. A way to achieve this is to enter a local member with a formula that identifies which rows or columns should be hidden and then add VBA after the refresh to search through the local member results and hide the necessary rows or columns.
- Setting the print range in an expanding report by using VBA last row and column syntax after the refresh.
- Running data manager packages.
The general rule that VBA should only be used if it is necessary, due to requirements not being achievable through regular Excel functionality, applies just as strongly to the use of VBA with the EPM Add-In. One consideration is that using VBA in reports and input schedules results in additional complexity and can make them more difficult to maintain in future.
Please feel free to add any observations or questions as comments below.