Skip to Content

Hi

Since our bread and butter for the BPC is based on Excel, it seems safe to say that we should be taking advantage of the inherit excel functionality to improve BPC performance and user interaction.

I am writing this document to explain the some of the basic VBA code that can use used to assist us while building reports and forms.

To start off, so that we are all on the same page – to open the VBA code window – you have two ways (at least that is what I am aware off). The first way is using the Excel menu > Developer tab > view code and the other way (my favorite) is using hot-key “ALT+F11”.

To add the developer tab to your excel menu, please follow the below steps.

Step 1:
Right click on the menu and click on “Customize the ribbon”.

Adding Developer Tab.jpg

Step 2:

Check the box next to “Developer” and click on “Ok”.

Adding Developer Tab 1.jpg

You can do a lot more in the developer tab then just going to the “View Code” window, but I am not going to into that in this document.

Now in the VBA code window, you will notice that there are several windows / panes open. The two panes that we are interested are (1) Project Explorer and (2) the code window. If you do not see these panes / windows open, you can add them by going to “VIEW > Project Explorer (shortcut is CTRL+R)” and “VIEW > Code (shortcut is F7)”.

VBA Window.jpg

The left window is the “project explorer” and the right window is the “code window”. The left drop-downs in the code window will be used to select the object and the right drop-down will be used to select the event / trigger for that object.

For our purposes, we are going to be adding a Module to the our workbook. You can do that by right clicking on the “VBAProject (Book1)” and navigating through Insert > Module.

VBA Window 1.jpg

The various functions that I have used over the past years have been listed below with their explanations

Public Function AFTER_WORKBOOK_OPEN()

End Function

This is the first code that will be called after the workbook has been opened. You can insert any code that you need to be executed just after the workbook is opened. Please note that if you had set the “Refresh Data in the whole file when opening it” in the sheet options, that will take happen before this code can be executed.

Public Function BEFORE_SAVE() As Boolean

    If variable then

          BEFORE_SAVE = True

    else

          Msgbox “Please use button to save”

          BEFORE_SAVE = False

    End if

End Function


This code will be called every time, EPM attends to send data back to BPC for saving. You can insert code here if you need to perform any validation before the data is sent or you can restrict the user from clicking on the “Save Data” in the EPM tab – similar to the example. I will also show the functions that I used to call the save and refresh at the bottom of this documentation.


Public Function BEFORE_REFRESH() as Boolean


    If variable then

          BEFORE_REFRESH = True

    Else

          Msgbox “Please use button”

          BEFORE_REFRESH = False

    End if


End Function


This code will be called every time, EPM attends to refresh the report or form. You can insert code here if you need to perform any action before EPM refreshes the sheet or you can restrict the user from clicking on the “Refresh” in the EPM tab – similar to the example. I will also show the functions that I used to call the save and refresh at the bottom of this documentation.

Public Function AFTER_REFRESH() as Boolean

    AFTER_REFRESH = True

End Function

This code is called after the refresh has occurred. You can insert the code to perform any action or call to another function within the function definition.

As promised, the two functions that I have created to save and refresh my workbook are as follows

Public Sub MySave()

    blnMySave = True

    EPM.SaveAndRefreshWorksheetData

      blnMySave = False

    ‘MsgBox “Saved”

End Sub



Public Sub MyRefresh()

    blnMyRef = True

    EPM.Refresh

    blnMyRef = False

    ‘MsgBox “Refreshed”

End Sub

Please note that I have defined 1 object (EPM) at the top of the module.

Dim EPM As New FPMXLClient.EPMAddInAutomation


I hope this document was helpful.


Thanks,


Ranjeet

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply