Custom Function Call

[Guest]

I have a library XLA file where I keep custom functions I've written and use in
multiple spreadsheets. The XLA file resides in my XLStart subdirectory and all
works as I would expect it to. I can call the functions from any spreadsheet cell.

My current problem arises in that I'd like to call one of the functions from
within a VBA macro. Somehow I'm unable to figure that out. If I try to call it
normally:

X = MyFunction(Y)

I get a compile error "Sub or Function not defined". So then I tried:

X = Application.WorksheetFunction.MyFunction(Y)

and get an error "Object doesn't support this property or method". Various
other attempts to point VBA to find the custom function all fail.

What's the key? Thanks.

Bill

 

[Norman Jones]

Hi Bill,

Try using Application.Run.

Something like:

X = Application.Run("MyAddIn.xla!.MyFunction",Y)

 

[Guest]

Hi Bill,

Try using Application.Run.

Something like:

X = Application.Run("MyAddIn.xla!.MyFunction",Y)

Ok, I tried that and it gets closer. Now Excel97 complains during compile that
it can't find the file. So then I put in the fully qualified name with the
whole subdirectory tree. With that the macro compiles properly, but then I get:

Run-time error '1004':

The file could not be accessed

The error statement also gives various possible causes for the error, none of
which are relevant in this case. It sounds to me like XP not wanting to open
two copies of the file or something so I copied the XLA file to another location
and renamed it to eliminate such XP level conflicts. But I still get the same
error.

Any ideas? Thanks...

Bill

 

[Dave Peterson]

You have your addin open?

Maybe it was just a typo:

X = Application.Run("MyAddIn.xla!.MyFunction",Y)
has an extra dot in it:
X = Application.Run("MyAddIn.xla!MyFunction",Y)

If that doesn't work, you should post the line you used.

Alternatively, you could add a reference to your workbook's project that
requires that addin.

Tools|references
point at the addin's project

Then you can use the function in that addin just like it was a built-in
function.

ps. It's always good to give each project a unique name.

Inside the VBE:
hit ctrl-r (to see the project explorer)
select your addin's project's top level
hit F4 to view the properties
change the (name) from VBAProject to something more meaningful.

(don't forget to save that addin.)

 

[Guest]

You have your addin open?

Maybe it was just a typo:

X = Application.Run("MyAddIn.xla!.MyFunction",Y)
has an extra dot in it:
X = Application.Run("MyAddIn.xla!MyFunction",Y)

If that doesn't work, you should post the line you used.

Alternatively, you could add a reference to your workbook's project that
requires that addin.

Tools|references
point at the addin's project

Then you can use the function in that addin just like it was a built-in
function.

ps. It's always good to give each project a unique name.

Inside the VBE:
hit ctrl-r (to see the project explorer)
select your addin's project's top level
hit F4 to view the properties
change the (name) from VBAProject to something more meaningful.

(don't forget to save that addin.)

-------------------

Your observation about the extraneous dot resolved that problem for me -- thank
you. Now I can go back to directly accessing the XLA file residing in my
xlStart folder and all is well. The add-in is permanently open in my system
since I use functions from it liberally.

Thanks Dave...

Bill

 

[Norman Jones]

Hi Bill,

Your observation about the extraneous dot resolved that problem for me --

Yes, that was my typo! Apologies and thanks also to Dave for spotting it.