Macros not run due to security setting -> No Workbook_SAP_Initialize -> No Call Back Registration "AfterRedisplay"

[Former Member]

Hello SAP BO Analysis/VBA Gurus,

I migrated some BEX 3.5 with macro code in SAPBEXonRefresh to BO Analysis.

I was able to run the macro code by using the Call Back Method "After Redisplay" - registering the call back in ThisWorkbook.Workbook_SAP_Initialize

(as explained in docu and with the help of SCN members).

In testing directly after migrating workbooks all worked fine .... but now I find out that the macros are not run at all!

Why?  Per Windows group policy all Excel Macros are set to "Disabled with notification".

This means then whenever I open a BO Analysis workbook I get the "Macros have been disabled" bar message.

I surely can press  "enable" but this doesn't help me as the Workbook_SAP_Initialize (which registers the call backs) should have run already/but did not. Hence all the macro code attached to "AfterRedisplay" gets not triggered.

Anyone a bright idea how to overcome this?

Thanks for all replies in advance,

Axel

PS: The issue was not occurring in testing as the VBA project/file was still open even you close the workbook -> Excel VBA behavior. When the BO workbook was open again it asked on "download/ before open" if macros should be enabled -> so Workbook_SAP_Initialize did run. Different behavior

[former_member245941 · Accepted solution]

Hello Alex,

let me give you two brief options on the issue. I will try to come back with a more in depth answer later, though. (If no one beats me to it...)

a) the Workbook_SAP_Initialize Macro in your Workbooks can be executed manually or by an event. Additionally it can be run at a later point in time. It will still register the callbacks.

(e. g. Try to run the Workbook_SAP_Initialize macro via "Alt+F8" from your workbook, after enabling Macros in the popup dialog and work it out from there.)

b) I won't suggest a change to an "all macros allowed" group policy. But perhaps you would like to pitch the responsible IT crew the curveball of "signed vba code with code signing certificates and an 'allow signed macros' group policy".

Regards,

Werner

[former_member245941 · Accepted solution]

werner.mittrup wrote:

[...]I will try to come back with a more in depth answer later [...]

a) the Workbook_SAP_Initialize Macro in your Workbooks can be executed manually or by an event. Additionally it can be run at a later point in time. It will still register the callbacks. [...]

According to your description of the issue, you should "register" your callback tied to the workbook_open event. This event will trigger after the user choses to enable macros in the workbook.

If your Workbook_SAP_Initialize sub does nothing except registering callbacks, as per the help documentation, add the following sub to "this_workbook" (Same place as the Workbook_SAP_Initialize macro):

Private Sub Workbook_Open()
Dim addin as ComAddIn
For Each addin in Application.COMAddIns
If adddin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then
If addin.Connect Then
Call Workbook_SAP_Initialize
End If
Exit For
End If
Next addin
End Sub

The sub checks for the Analysis COMAddIn and its state before executing the Woorkbook_SAP_Initialize sub.

In an environment, where the user does not need to enable macros via the popup, this code will just lead to the execution of the Initialize sub twice. Once from the Analysis Com AddIn itself, and a second time from the Workbook_open event. As long as there is no additional code or functionality in your Initialize Sub, that should be harmless.

Opening the workbook in plain Excel, without the Analysis Com AddIn enabled, the sub won't do anything, except looking through the list of the COMAddIns.

Regards,

Werner

[Former Member · Accepted solution]

Hi Werner, I am working under Axel on this project.  My VBA is a little rusty, and the AO methods and stuff like 'crosstab' I am not real familiar with using.

I tried to put a breakpoint on the first procedure (in 'ThisWorkbook' module) and save the WB.  Then re-open the WB to prove if macro executes any of the VBA.   But saving does not save the BPs.

With a migrated BEX workbook currently open, looking at analysis tab, should the 'Refresh All' not kick off at least the VBA code in 'ThisWorkbook' module? 

This is not currently working for me.. However it does refresh the WB and filter criteria. 

I did try the above workbook open procedure that you provided. 

When I reopen the workbook, after selecting criteria at selection screen and clicking 'ok' , I don't believe any VBA runs.

The security warning button for enable macros is up again, because I reopened the WB of course.  And the problem here is I cannot add a breakpoint to confirm if code is being executed until after selection criteria is executed..   If I open the VBA, the security warning disappears.  If I click on 'enable content' (macros) I get a runtime error (which makes me think your code is trying to kick off some VBA right at the point of enabling macros)..    The VBA compiles with no errors, so not sure what is happening now.  This is after adding your Private Sub Workbook_Open()  to the 'ThisWorkbook'

I would actually like to see the code run using the refresh all button.  This way I can test with BPs and also compare to the open event method.  What are your thoughts on this?

Any help on this is greatly appreciated.

Paul

[former_member245941 · Accepted solution]

Hello Paul,

I just noticed there is a typing error in the example Please replace the "adddin.progID" with "addin.progID" and try again.

Regards,

Werner

[Former Member · Accepted solution]

Hi Werner,

thanks a million! You are a true BO VBA guru!

You proposed code is exactly what we have been looking for.

Thanks again and best regards,

Axel

[Former Member]

Hi ,

You can change the behaviour in excel if you go to options trust centre -->trust centre settings -->macro settings and then set to enable macros

[Former Member]

Unfortunately this is not an option as all security settings are disabled by windows group policy.