worksheetfunction doesn't work in vba

[rnl0085]

hello

i am using an excel function called theoretical(market,instrument,price) in my excel sheets. this is working fine.

however, when i want to use it in a vba code, for example
price = worksheetfunction.theoretical(market,instrument,price)
this always gives 0 values..

theoretical() is not a standard excel function but is used via an addin..does anyone knows why it doesn't work in vba?

 

[RoryA]

If it's not an Excel function then it does not belong to the WorksheetFunction object. You will need to call it from the addin.

 

[rnl0085]

the addinfile.xla is already viewable in the developer..in one the modules of the addin teh function can be found...but how do i call it in the regular file then? addinname.function()?

 

[RoryA]

If you can set a reference to the addin file (tools-references) then yes you can call it like that; otherwise you will need to use application.Run.

 

[rnl0085]

the file isn't in the tools -> reference list..but it is in the project library in the developer on the left..and some other functions that are in the xla file are just being called in the normal excel file as well..however this doesn't seem to work for all

will the application.run make the file much slower compared to calling the function directly?

 

[RoryA]

If the addin is open it will be in the references list, but it will be listed as the project name (by default it's VBAProject - if that hasn't been changed, then you can't use a reference), not the addin name.
It will probably be a little slower but not noticeably so unless you call it a lot.