Reply To: Is relying on VBA for reporting requirements a sensible idea?

[Deb Silverman, Report Coach]

VBA has become an integral part of EPM solutions, for without we wouldn’t be able to fully harness some truly powerful API’s that come with the tool. Automation has become far more accessible than ever before!

I really like how you handled the validations – we don’t want to waste time or lose our input if something was overlooked. I sometimes use the standard BPC function BEFORE_SAVE() so the data doesn’t get wiped out if there are any events following the save. If i were to use your code it would look something like this for both MS and NW versions (you can copy paste this code and it will work as it is – the object declares go at the top of the module):

Option Explicit

Public api As Object
Public z As Object

Sub SaveAndRefreshData()

On Error Resume Next

If Range(“SAVE_VAL”) = “OK” Then

Set api = Application.COMAddIns(“SapExcelAddIn”).Object

If Not api Is Nothing Then

Set z = api.GetPlugin(“com.sap.epm.FPMXLClient”)

Else

Set z = Application.COMAddIns(“FPMXLClient.Connect”).Object

End If

z.SaveAndRefreshWorksheetData

Else

MsgBox “Save validations are not met, please correct your input”

Exit Sub

End If

‘…. may have more code here??

End Sub