Cannot run macro from add-in.

[William_G]

First post to this forum. Familiar enough with computer stuff, but first time user of Excel. I'm doing this to help a friend.

Excel 2010 on Windows 7 (Ultimate) running in a VirtualBox VM on Debian Linux Stretch

My friend downloaded a free Excel add-in (in an 'xlam' file) which is to file some numbers with the UK tax authorities. There's no support.

The add-in has an installation routine which seems to work fine, and after it runs it saves a workbook 'Book1.xlsm'. When opened in Excel, this file displays a tab showing the name of the add-in, which seems to indicate that the installation has been at least partially successful.

Clicking this tab displays a ribbon with some buttons which have to be clicked in sequence, to set up some constant data and fetch an authorization token from the tax man. This involves the tax man making an automated telephone call to the number that they have for my friend, to give him a six-digit number that has to be entered into the tax man's Website to verify that it's really my friend asking for the token. That all seems to work fine too. When that's all done, the add-in renders a form, so that you can input the data to be sent to The Man.

Here's where it goes belly-up.

There are two dates and a few numbers to be sent. The add-in provides GUI routines to choose the dates (and the numbers) which are supposed to be run by clicking images on the form which it's rendered. The images appear to be calling the correct sub names, but on clicking them the following error message appears:

Cannot run the macro 'Book1.xlsm!Open_xxxxxxx', The macro may not be available in this workbook or all macros may be disabled.

The xxxxxxx is different for each image/icon but the subs are indeed present in the code in the add-in.

Using 'Developer'->'Visual Basic' I see in the Project pane both the add-in and the workbook. Under the workbook hierarchy of VBAProject (Book1.xlsm) I can see in 'Microsoft Excel Objects' three (what I take to be) spreadsheets: 'Sheet 1', 'Sheet 2', 'Sheet 3', which look like the three spreadsheets that I can see named in tabs at the bottom of the 'normal user' Excel screen, and something called 'ThisWorkbook' which I don't recognize. The only thing that I can find in 'ThisWorkbook' is this code:

Private Sub Workbook_Open
End Sub

I suspect that I've done something silly but I've been searching for days and I haven't found quite what I'm looking for. This thread is close:

https://www.mrexcel.com/forum/excel-questions/557506-cant-run-macro-add-ribbon.html

but unless I'm misunderstanding something it's not quite the explanation. I've seen claims that the buttons and the macros might somehow get disconnected, but that doesn't seem to be the case here. I tested the theory by creating within VBAProject (Book1.xlsm) a module which contains a dummy sub with the same name as one of the not-found subs in the add-in. When I click on an image on the form it runs my dummy sub. I've tried and failed to get the dummy sub to call the real one by explicitly calling Addin_Name!Sub_Name but being a complete novice at all this I'm having syntax an probably other issues doing it that way.

Do these symptoms remind anyone of novice mistakes that people make when installing add-ins?

If you've read this far, thank you.

 

[DataBlake]

firstly in the developer ribbon click "Macro Security"
make sure "enable all macros" and the "Trust access to the VBA project object model" is checked

 

[William_G]

firstly in the developer ribbon click "Macro Security"
make sure "enable all macros" and the "Trust access to the VBA project object model" is checked

Thanks very much for your reply.

The "Enable all macros" radio button is selected and the "Trust access to the VBA project object model" checkbox is checked.

 

[William_G]

Bump.

Cannot run the macro 'Book1.xlsm!Open_xxxxxxx', The macro may not be available in this workbook or all macros may be disabled.

The xxxxxxx is different for each image/icon but the subs are indeed present in the code in the add-in.

 

[ZVI]

Hi,

Try using Run, like this:
Run "Book1.xlsm!Open_xxxxxxx"

Regards

 

[DataBlake]

only other thing i can think of is that you accidentally deleted whatever code is giving you the issue.
you may want to download a fresh copy

 

[William_G]

Hi,

Try using Run, like this:
Run "Book1.xlsm!Open_xxxxxxx"

Progress! Thank you very much. In fact what I eventually found I had to do was

Run "AddIn_name.xlsm!Open_xxxxxxx"

but that's definitely a step forward, the date picker (apparently) works as intended now.

I suspect that this is a legacy issue, would I be right in thinking that this kind of behaviour has changed sometime between the releases of Excel 2010 and Excel 2013 (or perhaps later)?

Still some work to do though, so, next question:

How do I find from where these subs are called? I've searched the 'project' using Developer->VisualBasic->Edit->Find for the string "Open_" but all that it finds is the text of the subs themselves. It doesn't show me where they're called from. As I explained in my OP, some of these subs are linked to images and I would like to be able to (1) find them all and (2) change the linkages -- instead of having a bunch of uselesss subs which just say

Sub Open_xxxx
Run "AddIn_Name.xlsm!Open_xxxx"
End Sub

Thanks again for everyone's help.

 

[ZVI]

In fact what I eventually found I had to do was

Run "AddIn_name.xlsm!Open_xxxxxxx"

...
How do I find from where these subs are called? I've searched the 'project' using Developer->VisualBasic->Edit->Find for the string "Open_" but all that it finds is the text of the subs themselves.

There are some points:

1. According to your initial code - Book1.XLSM!Open_xxxxxxx , the code of Open_xxxxxxx should be in Book1.xlsm not in the AddIn. May be something went wrong during installation process, reinstalling AddIn in Windows OS and MS Excel is recommended to analyze its initial behavior.

2. AddIn should not be with XLSM extension, looks like XLA(M) AddIn was saved as macro enabled workbook XLSM which works differently. For example, public User Defined Functions (UDFs) of the installed AddIn are accessible for any workbooks, but UDFs of XLSM are for only that macro enabled workbook. Sure, Run can be used to call code of any workbooks, but VBA project should be rewritten for that.

3. If you can't find code of Open_xxxxxxx subroutine then it's rather under author's security and not allowed for the editing. According to forum rule #6 we can't help in this case.

 

[William_G]

There are some points:
1. According to your initial code - Book1.XLSM!Open_xxxxxxx , the code of Open_xxxxxxx should be in Book1.xlsm not in the AddIn. May be something went wrong during installation process, reinstalling AddIn in Windows OS and MS Excel is recommended to analyze its initial behavior.

I can find very little code in Book1.xlsm. Maybe that's the real problem all along. Can I just copy the modules from the add-in?

2. AddIn should not be with XLSM extension, looks like XLA(M) AddIn was saved as macro enabled workbook XLSM which works differently. For example, public User Defined Functions (UDFs) of the installed AddIn are accessible for any workbooks, but UDFs of XLSM are for only that macro enabled workbook. Sure, Run can be used to call code of any workbooks, but VBA project should be rewritten for that.

Apologies, I typed "xlsm" when I meant to type "xlam". Yes, you are correct, the add-in has the extension "xlam".

3. If you can't find code of Open_xxxxxxx subroutine then it's rather under author's security and not allowed for the editing. According to forum rule #6 we can't help in this case.

I understand, but I can see the code for the subs. What I do not see is what calls the subs when an image is clicked.

Perhaps something is hidden?

Thanks once again.

 

[William_G]

... May be something went wrong during installation process, reinstalling AddIn in Windows OS and MS Excel is recommended to analyze its initial behavior.

I forgot to respond to this part of your message.

Like you I suspect that there may be something going wrong with the installation.

Of course I have reinstalled the add-in many, many times. It does not take a long time. I usually reinstall it after making many code changes to try to debug the problems, so that I always start from the same place.

But I have only ever installed it on this same Windows 7 virtual machine.

Are you suggesting that I should try an installation on Windows installed on bare metal, instead of using a virtual machine?

Thanks again.