Man-Computer-Laptop-Coffee-Cafe-Concept-000079551979_Large-1.jpg

SAP BPC & EPM
Thought Leadership

VBA Object Declarations in the EPM Plug-In for Analysis for Office

Posted by
Dennis Frey
Dennis Frey
on Fri, Nov 03, 2017 @ 09:11 AM

One of the key features for the SAP BPC EPM Add-in for Excel has been the ability to write VBA to extend and customize the capabilities of the Add-in. The new EPM Plug-in for Analysis for Office (AO), which replaces the EPM Add-in, offers similar and in most cases compatible VBA support as the original EPM Add-in. However, there are some important distinctions between the two which this article covers.

Featured Blog Photo-8.png

The new EPM Plug-in for Analysis for Office (AO) requires a different method of declaring the specific API class used to access the library of VBA methods that SAP BPC supports.  The latest version as of this writing, AO 2.4 SP1, made some but not all the old FPMXL Client methods compatible with the EPM Plug-in.  To be safe, it’s recommended to start coding using the new standards for AO, as there’s no clear indication of whether all methods will be made compatible using the old object declaration standards.

To illustrate, here is a direct comparison of the differences between the two declaration methods.

EPM Add-In (old declaration)

Dim epm As Object
Set epm = CreateObject("FPMXLClient.EPMAddInAutomation")

EPM Plug-In (new declaration)

Dim epm As Object
Dim api As Object
Set api = Application.COMAddIns("SapExcelAddIn").Object
Set epm = api.GetPlugin("com.sap.epm.FPMXLClient")

The rest of the code would look exactly the way it did before. Here are some examples:

epm.RefreshActiveSheet
        ⋮
epm.SaveAndRefreshWorksheetData
        ⋮
epm.SetContextMember ActiveConnection, DimensionName, MemberName

An issue with the new Plug-In code is that it’s not backwards compatible with the EPM Add-In.  In other words, the VBA will error out when running the new declaration in the EPM Add-In. One method of addressing this is to first try to declare the object using the new standard of declaration. If the code is running in the Analysis EPM Plug-In, the object will be set normally.  If running in the EPM Add-In, the object will not be set to nothing.  A simple check if the object was set can determine which version of EPM is running.

Here is an example of code that will run correctly in both the EPM Add-In and AO EPM Plug-In.

On Error Resume Next
Dim epm As Object
Dim api As Object
Set api = Application.COMAddIns("SapExcelAddIn").Object
If Not api Is Nothing Then
        Set epm = api.GetPlugin("com.sap.epm.FPMXLClient")
Else
        Set epm = CreateObject("FPMXLClient.EPMAddInAutomation")
End If

Any SAP customers currently developing VBA in the EPM Add-In should do so with the knowledge that the next upgrade may likely be to Analysis for Office.  By being proactive and including the simple changes highlighted above, much effort can be saved in migrating templates over to the new tool.

Learn to improve your experience with the EPM Add-in with our free, on-demand EPM Add-in Tips & Tricks and Reporting & Analysis Best Practices Webinar!

Something else to note:  when using the EPM Plug-in with the 64-bit version of Excel, there are certain class methods that are no longer working.  Any method in the EPMAddInDMAutomation (note the DM) and the EPMAddInOOAutomation classes of the FPMXLClient library does not work.  The only working methods are in the EPMAddInAutomation class.  The issue was verified by SAP, but it’s unknown if a fix is being developed for those classes.

Here are the methods in the EPMAddInDMAutomation class, which will not run in the 64-bit version of the Analysis Plug-in.

4-3.png


* SAP will be supporting one Excel Add-in going forward, called the Analysis for Office Add-in that will have two plug-ins. The Analysis Plug-in (recommended for SAP BPC Embedded models) and the EPM Plug-in (recommended for SAP BPC Standard models.)

Are you interested in learning more about the EPM Add-in and everything SAP BPC? Join us for the 2018 EPM Leadership Summit in Las Vegas for a week of hands-on learning, networking with other SAP BPC users and a full lineup of expert speakers.

register now

 

Related Articles:

6 Commonly Asked Questions About the EPM Add-in Excel Client

EPM vs Analysis Office

Analysis for Office and the BPC Plug-in – everything you need to know!

Troubleshooting Techniques for SAP BPC Reports Created w/ the EPM Add-In

Author Bio:

Untitled design-4.pngDennis Frey has nearly 10 years of experience in financial systems and joined Column5 Consulting to focus on SAP BPC in 2013. He specializes in designing and developing BPC solutions for revenue, sales, and financial resource planning.  Dennis currently works as a Product Manager on the Darwin EPM team.

 

Topics: Analysis Office, API, EPM Add-in, EPM

Subscribe

Recent Posts