How to trigger a VBA Macro?

[Former Member]

Hi,

I intend to use a Macro to format my workbook. I have created a macro by name "Format" now I wrote the name in the "Exits" tab of "Workbook Settings" and clicked "Attach Macro" and clicked "OK". Then I saved the workbook. Now when I reopen the workbook and refresh the workbook query there is no effect of the Macro.

Please suggest how I can make this macro work.

The code I have is:

__________________________________________________________

Sub Format(<b>Technical Name of Query</b>, )

'

' Format Macro

' Macro recorded 2/5/2007 by Registered User

'

'

Columns("F:F").ColumnWidth = 7

End Sub

__________________________________________________________

Thanks

Kumar

[Former Member]

Hi,

I'm trying to execute a macro in the same way as described in this thread. I have followed all steps but my macro doesn't run.

More exactly, using BEx Analyzer BI AddOn 7.X (based on 6.40), Support Package 16, Revision 1059 the button Add or Delete Macros appears and after pressing it, the macro is correctly saved but doesn't run.

Any suggestion will be well appreciated.

Thanks in advance for your kind support.

Regards,

Giovanni

[Former Member]

This seems like such a simple process, yet I am clearly missing something as I cannot get this to work.

I have been given a workbook, to which I need to add a macro that will perform some calculations and update values in the displayed table.

I have tried the following:

- Click on the Workbook Settings button, and in the Exits tab, I can specify a macro to call on refresh (myCallBack).

- Click on the OK button to save the changes

- Next step is to Tools > Macro > Macros ... enter myCallBack, and click on the create button ... my macro is now created

- Add the required signature to the new macro, so now it is Sub myCallBack(ParamArray varname()) ... this routine is placed in Module 2 of the VBA project (QBFWxxxxxxx.xls)

- Add a simple VBA piece of code into the macro, like a msgbox, to see how and when the routine is called

- Save the code by clicking on the save icon

If I refresh the workbook by clicking on teh refresh icon, then I get my msgbox appearing.

So ... seems to be working ... however, if I close the workbook and open it again, all the above is no longer valid ... the setting to call my own subroutine from the exits tab is gone, my custom function is gone, so no message appears.

I am willing to forego the custom routine requirements and just put my code in the standard callback routine, but that code also disappears each time I close the workbook.

Is there something wrong with the way I am saving things perhaps ... the workbook exists, I choose Save >> Save Workbook from the toolbar. I have also tried using the Excel save option, still no saving my code.

Please, I am desperate to get this working, so would really appreciate some input.

Cheers,

Andrew

Edited by: Andrew Lewis on Mar 25, 2008 11:09 PM

[Former Member]

if you got this to work please share findings..... I have similar issue and also with format of signature requested.

[Former Member]

Kumar,

The 'attach macros' button only adds the bexapi.dll into the references of the workbook. This means that you are then able to access the bex api for 7.0 and integrate it into your macros (e.g. BEx.RaiseButtonClick for creating your own button to execute multiple bex buttons or BEx.Dataproviders to manipulate dps via VBA). See for a general blog on VBA 3.5 vs 7.0.

If you have tried adding the macro into the 'Invoke macro on refresh' field then you will probably find that in the error / warning messages you have the following message: 'incorrect signature'. If that is the case then add (ParamArray varname()) after your sub name and this will sort out that issue.

As for the most effective way to format the workbook then the most effective way is to apply the themes you require to a default workbook (see workbook setting > Themes) and then apply that default workbook in 'Global Settings'. As a hint, when creating the default workbook please avoid the standard query template which increases the size of the workbook significantly because of the the added graph.

Hope this helps. Good luck!

Rgds, Keith

[Former Member]

from your note....

If you have tried adding the macro into the 'Invoke macro on refresh' field then you will probably find that in the error / warning messages you have the following message: 'incorrect signature'. If that is the case then add (ParamArray varname()) after your sub name and this will sort out that issue.

I do not undertsand what to add. If macro name is "afterrefresh" then Please provide example code for (ParamArray varname())

[Former Member]

it should be

sub afterrefresh(ParamArray varname())

end sub

I tried it and it works in my case

Assign points if it helps