Add-in aggravation

[Richie(UK)]

Hi all,

OK, a brief outline of the scenario. I'm creating a small add-in containing a collection of various routines. The routines are accessed via a menu that is added to the Worksheet Menu Bar (using a sheet-driven routine from the VBA Programmers Reference by Mssrs Bullen & Bovey et al).

During the development phase of creating the add-in the workbook was actually maintained as a workbook, rather than an add-in, so that I could easily modify the menu details on the relevant sheet.

Now to the problem. Since saving the workbook as an add-in one of the routines (and only one as far as I can tell) has refused to work. I receive the "Wrong number of arguments or invalid property assignment." error. If I copy the relevant code to a new workbook it runs fine, if I change the add-in back to a workbook it runs fine, but as an add-in ...

What's really weird is that even if I comment-out all of the code in the routine and just add a simple messagebox line I still get the error!

Anybody ever come across this sort of situation before?

More importantly, does anybody know how to resolve it?

I regularly frequest two Excel forums and this query has also been posted here:
http://www.ozgrid.com/forum/viewthread.php?tid=9263&page=1#pid43771

Before responding, please check for any replies on that thread - I have no wish to waste anybody's time going over ground that has already been covered.

 

[bydesign]

Richie

I had a similar problem

When you make a workbook an addin (i.e. workbook property "IsAddin = true") it is of course still a workbook, but it is hidden. Therefore it can't be refered to as "Activeworkbook" in your code.

Check your references to the addin and make sure you use the name of the workbook i.e. Workbooks("MyAddin").Sheets("MySheet").Range("A1")

This is what happened to me... hopefully it will help lead you to your problem.

 

[Richie(UK)]

Hi Scott,

Unfortunately (fortunately ), I'm not referring to the add-in as the ActiveWorkbook. The only references to the ActiveWorkbook are when I want the routines in the add-in to act on it (the ActiveWorkbook).

Thanks for the input though.

 

[TheBuGz]

Thank u Richie Bro for pasting the link now i will be using that Forum too the one u just pasted. Thanx a lot for Sharing Knowledge.

Share The Knowledge and u will Learn More (I hate ppl who ask Money For Sharing such Knowledge)

 

[Ivan F Moala]

Hi Richie

It appears you have developed this in Excel 97 ?
Have you by any chance then recompiled this in Excel 2000
and made it into an Addin?

You say @ ozgrid that it is called via custom menu ....
What code are you using for this and are you sure this is called properly .....

 

[Richie(UK)]

Hi Ivan,

Yes, the workbook was developed in Excel97. No, I haven't recompiled in Excel2000 (or any other version for that matter). I have, however, used Mr Bullen's CodeCleaner on it, if you think that may be relevant.

Yes, it is called from a custom menu. The menu routine is one of those sheet-driven affairs using code from the Programmers Reference by Bullen et al. The same routine is being used for all of the other routines in the add-in and runs with no problems at all for those.