Using VBA macros is an excellent way to create a more user-friendly UI for reports using Analysis for Office. You can customize workbooks to simplify various planning tasks with the click of a button. In this blogpost, we’ll go through steps you can take to create macros in your own Analysis for Office workbooks.
To create a macro, go to the “Developer” tab. Click “insert” and select the button icon. Draw the size of the button and a new window will pop up. Select “New” in the new window.


Now we will need to create the VBA code. This is where we will be able to specify the actual task the button will carry out.
- Starts with calling and naming the sub:
Sub Name_Of_Sub()
- Next, define a variable. This variable will be used when calling the specific command.
Dim Variable_Name As Long
- The next line of code will set the variable that was defined. Below are the some popular that can help streamline your planning process:
- Set Prompts – This command will open a window that allows users to set their prompts. Instead of users having to set multiple filters within the report, users will be able to use the prompts window to filter the data available in this data source so that it pulls the exact data they need from the start.
Variable_Name = Application.Run(“SAPExecuteCommand”, “ShowPrompts”, “ALL”)
- Recalculate Data without Saving – This command serves as a preview of how the changes to data will be reflected in SAP. Using this command, users can recalculate the data in the query without saving the data to SAP BW. For example, a user may want to increase sales by 10% for their specific brands. They could easily use this macro to see how the increase would affect the company wide total without having to save the data to SAP BW.
Variable_Name = Application.Run(“SAPExecuteCommand”, “PlanDataTransfer”)
- Reset Data – This command will reset the planned data in the workbook and revert to the last saved data. This is useful if a user has used the macro to recalculate data without saving to SAP BW and wants to start over. The user can easily click this button to revert back to the last saved data in the data source.
Variable_Name = Application.Run(“SAPExecuteCommand”, “PlanDataReset”)
- Execute Planning Function/Sequence – Planning functions or sequences can be used to carry out more complex functions that are created in SAP. A popular option is to copy last forecast data. Using a macro for this feature makes it easy for the data in SAP to be copied and saved directly into the data source all in the back end. The benefit of this option is that it significantly cuts down on the manual process of copying the data. In addition, multiple planning functions can be combined into a planning sequence. For example, the macro could trigger a planning sequence that will copy the last forecast data and do a simple initial calculation for the new initial forecast.
Variable_Name = Application.Run(“SAPExecutePlanningFunction”, “PF_1”)
Variable_Name = Application.Run(“SAPExecutePlanningSequence”, “PS_1”)
- Pass Variables Values in Planning Function/Sequence – Some planning functions will recalculate the data using a formula. For example, you may need to multiply the values by a certain factor. With Analysis for Office you are able to set a cell in Excel as the factor value. With this option, end-users can enter any value as the factor and forecast with the utmost flexibility. To do this, click on your planning sequence in the Design Panel of Analysis for Office. At the bottom of the panel, click the “Variables of Planning Sequence”. Here, you can set the variable value as a set cell in the Excel or set it to a variable.
- Save Data – This command will save entered data. Users can use this button after they have done their forecasting and need to save their data back into the SAP BW. Having a button in the workbook for this functionality makes the workbook more user-friendly.
Variable_Name = Application.Run(“SAPExecuteCommand”, “PlanDataSave”)
- Be sure to end the sub with:
End Sub
These are just a handful of the plethora of ways you could use macros in Analysis for Office. Macros create a user-friendly interface and can take care of the more tedious tasks at the click of a button. For instance, you can create multiple macros that guide business users through the required steps to complete their forecast. Complicated calculations can be handled in the back end. Using macros is a great way to streamline planning tasks for your user community.