Add-In macro execution problem

[Shaken]

Excel 2003, XP and Vista

I've been releasing VBA code through Add-Ins for years... Then last week any
new macros added can not be found in the Addin: Err Msg: The macro
'Addinname.xla!HelloWorld' cannot be found.

The code executes inside of VB editor but can not be referenced outside of
the editor???

Simple test: 1.New workbook,VB editor - Create HelloWorld, Assign macro to a
Menu button, Create Add-in via SaveAs, Try to execute via the button.

Any ideas would be greatly appreciated.

 

[Jim Cone]

Some ideas, solutions ??...

1. Maybe it is a Vista problem.
2. Maybe you put the code in a class module instead of a standard module.
3. Add the module name to the path.
4. Change the sub from Private to Public
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Shaken"
wrote in message
Excel 2003, XP and Vista
I've been releasing VBA code through Add-Ins for years... Then last week any
new macros added can not be found in the Addin: Err Msg: The macro
'Addinname.xla!HelloWorld' cannot be found.
The code executes inside of VB editor but can not be referenced outside of
the editor???
Simple test: 1.New workbook,VB editor - Create HelloWorld, Assign macro to a
Menu button, Create Add-in via SaveAs, Try to execute via the button.
Any ideas would be greatly appreciated.

 

[Shaken]

Tx for the reply, Regarding suggestions
-Same for XP and Vista
-Module is a "standard" module not class module
-I've tried Priviate and Public declarations of the Sub

Regarding the adding the module name to the path... I'm not sure how to do
this or if it is practical. I have around 100 macros I need to build into an
Add-In...

The 64 dollar question: What changed to make this not work???

 

[Dave Peterson]

I bet that the menu item points a different workbook (or a workbook with the
same name, but in a different location).

Tools|Customize (just to see the dialog)
rightclick on that icon and see what macro (and where it lives) is assigned to
the button.

You may want to consider building that toolbar each time that the addin opens:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

If you want to learn about modifying the ribbon in xl2007, you can start at Ron
de Bruin's site:
http://www.rondebruin.nl
or
http://www.rondebruin.nl/ribbon.htm

 

[Shaken]

I think your onto something Dave, I use John Walkenbach's menumaker code to
assign my macros to menu bar. As a test, I downloaded the current version
(menumakr.xls) and added a "HelloWorld" macro to the worksheet as well as
assigned it to the first menu item. After creating the menu and executing
the code I get the following err msg:

The macro 'menumakr.xls!HelloWorld' cannot be found.

This leads me to believe it has nothing to do with the xla but rather my
environment.

Question: Is there a PATH that Excel searches for macros that might be
messed up?

 

[Shaken]

Dave- I got something to work but I don't know why it works the way it does...
I noticed that the macro name is listed as
'menumakr.xls!HelloWorld.HelloWorld' from the Tools->Macro->Macros... menu.
When I replace the name 'HelloWorld' with 'HelloWorld.HelloWorld' in the cell
it works!
Do you understand why the extra '.HelloWorld' is necessary? It wasn't a few
weeks ago...? I still think there is something fishy with my environment.

 

[Shaken]

Ok, I think I figured something out that is relavent... I use the following
call to name my procedures:

ThisWorkbook.VBProject.VBComponents("Module2").Name = "HelloWorld"

If I don't rename the code - leave it as "Module2" it is found. If I rename
it then it is not found.

Does that make sense to you?

 

[Dave Peterson]

I'm not sure it's a case of making sense. But I would never give a module the
same name as a sub (or function)--or even a defined name or a builtin
name/function.

It can confuse excel (as you've seen).

You could have used: ModHelloWorld
to make it look nice.