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.

 

[William_G]

Still trying to understand all the details of your reply:

...AddIn should not be with XLSM extension

As I said, that was my mistake, the add-in is installed with the .xlam 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.

The "Open_xxxx" Subs are Subs, not Functions -- I do not know if it makes any difference. The Subs which are not found are declared as Public in the code in the add-in.xlam. Are you saying that they should be available to any Workbook in the Project simply by calling "Open_xxxx", that is without needing to use "Run AddIn.xlam!Open_xxxx" instead of "Open_xxxx" in code in a Worksheet? If that is what you mean then I think that may be the problem that I need to fix, because (as far as I can tell) the Public Subs are not found by the code which runs when the user clicks on an image in the Worksheet - in my OP I show the error message which the user sees after clicking on the buttons - and yet the Worksheet was created for the user by one of the buttons on the Ribbon which is created by the add-in itself! (*)

Sure, Run can be used to call code of any workbooks, but VBA project should be rewritten for that.

Yes, in that case it seems likely that there would be many other issues than just being able to call three or four Public Subs.

(*)
Please note that I am unfamiliar with Excel terms such as Worksheet, Workbook, Project, Add-in etc. I am still struggling to understand how they all relate to each other and I could easily be using the terms incorrectly when I try to explain what I am seeing. If you can point me to a document which starts at the beginning and which explains the structures of the objects, the relationships between them, their purposes, capabilities and limitations I should be most grateful.

 

[William_G]

Update on the situation:

I added 'Module1' to Book1, then added a bunch of Subs like so:

Sub A_B()

 

[William_G]

[I'll try again. I *think* I hit the 'TAB' key.
After I completed an edit of my previous post, vBulletin said per the Administrator's rules I'm not allowed to do that.
Admin: please feel free to delete my previous post and this entire aside.]

Update on the situation:

Today, with my friend, I added 'Module1' to Book1, and then we attempted to file his tax return.

Numerous clicks on images were required.

More often than not, the response to the click was a message saying that a Sub could not be found.

Every time this happened I added to 'Module1' a Sub like this:

Sub ABC()
Run "AddIn.xlam!ABC"
End Sub

where ABC was obviously the name of the Sub that could not be found.

Much to my surprise, after I had added about ten of these Subs, we had successfully filed my friend's tax return.

Thanks once again one and all for your help with this issue.

One last question:

The tools available from the 'Developer' tab in Excel (at least from the point of view of a long-time Emacs user) leave a lot to be desired. It's a little bit like trying to view the pages of a ream of fan-fold paper through the slot in a letter box. What do people use when they're developing this stuff?

 

[DataBlake]

Numerous clicks on images were required.

More often than not, the response to the click was a message saying that a Sub could not be found.

are these images in the VBAproject excel objects?
you can view the code on the images if they are there. It should show what its trying to do when you click an image

Sub ABC()
Run "AddIn.xlam!ABC"
End Sub

where ABC was obviously the name of the Sub that could not be found.

if it works it works, but if the image has more code than just running one addin code then something somewhere is messed up
its hard to tell without physically seeing it

What do people use when they're developing this stuff?

VBA stuff? We just type it i guess. I imagine some use other programs like notepad++ or visual studio to write their VBA, but the debug tool in the developer screen is actually quite useful so i just write code there. As far as things past VBA i'm unsure myself.

 

[ZVI]

...Much to my surprise, after I had added about ten of these Subs, we had successfully filed my friend's tax return.

Thanks once again one and all for your help with this issue.

One last question:

The tools available from the 'Developer' tab in Excel (at least from the point of view of a long-time Emacs user) leave a lot to be desired. It's a little bit like trying to view the pages of a ream of fan-fold paper through the slot in a letter box. What do people use when they're developing this stuff?

Good job! Nice to know you have made it out!

Your method is better than copying code from the AddIn into module of Book1 - code is stored in one place without doubling.

Macro is assigned to an image by right clicking on that image and choosing the “Assign Macro...”

Major controls in Developer tab of a Ribbon are went from an old Visual Basic command bar of Excel 2003, according to the principle of least surprise

 

[William_G]

are these images in the VBAproject excel objects?
you can view the code on the images if they are there. It should show what its trying to do when you click an image

I believe that the images are Excel Objects but I don't know how to be sure. I don't really know what defines an Excel Object.

The outline of the structure, at least as much of it as I can describe, is as follows:

After the AddIn is installed into Excel, in the row of Tabs which includes the 'Developer' Tab, the AddIn creates a new Tab immediately to the left of the 'Developer' Tab.
When you click the new Tab, a Ribbon appears immediately below this row of Tabs.
There are several Buttons on this Ribbon, and the instructions (*) tell you to work along the Buttons on the Ribbon from left to right in sequence.
The first three Buttons pop up boxes into which various personal details need to be entered; along the way the Addin requests a 'token' from the Tax Authority.
So far this all works OK. I do not know if the boxes seen so far are themselves spreadsheets, but I think not.
The fourth Button opens what I'm sure is a spreadsheet -- you can see a rectangular cursor with a heavy black outline if you click on random blank parts of it.
This spreadsheet contains areas for numbers which need to be filled in and a number of Buttons.
AFAICT none of these Buttons works, when you click them all you get is error messages.
A 'Book1' is mentioned in the error messages (e.g. "Cannot find Book1!ABC").
These non-working Buttons are those which I've made work by adding Subs in my new Module.
I created 'Module1' using the 'Visual Basic' Button on the 'Developer' Tab, which opens a window showing the AddIn and this 'Book1'.
I guess 'Book1' was created by the Addin.
I added a Module to 'Book1', Excel decided to call it 'Module1'.
I've tried obvious things like right-clicking on the Buttons to see their Properties but nothing happens when I do that.
For use by someone who isn't expected to do anything other than use the Buttons to enter data it would make sense to disable viewing (and altering!) of the Button Properties.
As I said this is my first experience of Excel. I wonder if there's something I don't know about configuring Buttons which would let me see their Properties.

(*)There is a separate PDF document with step-by-step instructions. They are fairly thorough, although the authorship is a little bit variable.

if it works it works, but if the image has more code than just running one addin code then something somewhere is messed up
its hard to tell without physically seeing it

As you might expect my friend has made an agreement with the supplier not to give the code to anyone else so I'm afraid I can't give it to you.

For sure something is messed up - I'd really hoped to find out what that is, but I haven't. If this isn't, say, a legacy issue which only appears in older versions of Excel then I can't see how anyone could ever have successfully used this Addin. My friend's a big bloke with a short temper so I don't want to suggest that he spends money upgrading Excel just to pander to the Tax Man.

I now expect (and fervently hope) not to see Excel again until the next time my friend has to do another tax return...

 

[DataBlake]

I believe that the images are Excel Objects but I don't know how to be sure. I don't really know what defines an Excel Object.

When you press alt + f11 that will bring up the VBA screen. there should be a tab in the project viewer that says "VBA Project (@@@@@)" where @ = your workbook or addin
when you expand the selection there is "microsoft excel objects". you may be able to find the image codes that are broken here? i understand that you cant post the code as its paid for and contractual to your friend. I'm just saying anything i say is a stab in the dark because i CAN'T see it.

As you might expect my friend has made an agreement with the supplier not to give the code to anyone else so I'm afraid I can't give it to you.

Plan B needs to be that the supplier fix the excel file for you / handle your issue since you paid.

 

[William_G]

Interesting that I'm now getting spam to the domain that I used to register here...