Custom Functions in modules
Posted by Mark on October 10, 1999 9:28 PM
Hi,
I've created a couple of custom functions, and put them in my custom addin (mymacros.xla). They are in standard modules.
It seems the functions can be used if they're in the modules of *that particular workbook*, but if I only put them in the addin file I can't use them with whatever the current workbook is...
Does anyone know how to get the bugger up and running?!
Cheers,
Mark
Posted by Ivan Moala on October 11, 1999 1:18 AM
Mark,
Put your mymacros.xla file in the Xlstartup directory.
If excel has been setup in the default locations
then it is; C:\Program Files\Microsoft Office\Office\XLSTART or
what ever drive loaction you have Office in.
This loaction is deemed to be a safe place for
excel files and will not give the usuall macro
startup screen.
Any valid xl files in this Dir will automatically
load up when you open excel.
Ivan
Posted by Mark on October 12, 1999 1:25 AM
Thanks for replying Ivan!
There's no problem with actually loading the addin: it loads fine when I start Excel. I can run my custom macros from it fine, but for some reason the custom functions give a #name! error. If I copy the functions to a workbook module rather than the addin module, it works fine. And at one point I had another simple function working successfully. There must be some simple thing I'm overlooking, which happened to be ok with the function that worked. I'm out of ideas!
Cheers!
Posted by Ivan Moala on October 12, 1999 2:45 AM
Mark,
I'm not sure but, does your function use defined named ranges?
This error suggests that the function doesn't exist, or is not
referenced properly (loaded in). Using a name that does not exist, or misspelling the intended name or function can cause #NAME? to appear.
Is your Fuction Private (ie. Private Function etc.) or Public (default)
You could try accessing the function via the;
VBA Tools / Reference
and load it in here.
In your VBA select "Tools" then reference.
In the "References - VBAProject" dialog
click on Browse.....
In the dropdown list of "Files of Type:" select "*.xls,*.xla" and
select you file addin.
Other then that I don't really know, perhaps someone else
can help.
good luck
Ivan
Posted by Chris on October 12, 1999 11:33 AM
Mark,
I can only confirm what Ivan has already stated. A function in any open workbook should be able to be referenced.
Just out of curiosity, try putting the name of the workbook in front of the function name. For example: =mymacros.xla!myfunction()
You could also check the text to be sure you are calling it the same thing in both places. Sometimes a zero can be confused with a capital o, or an lower case l with a one.
Beyond that, there is nothing more I can offer.
Chris
