How to use UDF from Add-in in VBA

[Andibevan]

Hi All,

I have a UDF that is contained in an add-in I have created myself
(MyAddin.xla)

I have been trying to utilise the UDF as follows:-

Var_TXT = application.worksheetfunction.myextract(Var_Source_Fold,1,"B","\")

But keep getting this error "Object doesn't support this property or method"

Any pointers?

Ta

Andi

 

[Bob Phillips]

Try this

Application.Run "addin_name.xla!myextract", Var_Source_Fold,1,"B","\"

or set a reference to your addin project and call directly.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

It is true that from the UI perspective a UDF appears to be part of
XL's functions. However, it is not really so. It is part of the XLA
that contains it. Adapt the idea at
How to use a class (object) from outside of the VBA project in which it
is declared
http://support.microsoft.com/default.aspx?scid=kb;en-us;555159

specifically, see how the function New_clsEmployee is accessed
externally. If the rest of the stuff about classes and objects is
confusing, just ignore it.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Only a subset of built in Excel functions are methods of the
WorksheetFunction object.

Var_TXT = application.Run( "AddinName.XLA!myextract",
Var_Source_Fold,1,"B","\")

 

[Andibevan]

Thanks All - much appreciated

Only a subset of built in Excel functions are methods of the
WorksheetFunction object.

Var_TXT = application.Run( "AddinName.XLA!myextract",
Var_Source_Fold,1,"B","\")

 

[Andibevan]

I have tried your solution and am getting a runtime error 1004 saying it
can't find the add-in - Do I need to specify the path and add-in name or
just the add-in file name?

Ta

Andi

 

[Tom Ogilvy]

Do I need to specify the path and add-in name

Not if the addin is loaded.

If it isn't you would.

 

[Andibevan]

In my case it was loaded but still threw the error.

 

[Tom Ogilvy]

All I can do is demonstrate that it works. I will use the analysis
toolpak-VBA function Dec2Bin in the immediate window as an example:


? Application.Run( "ATPVBAEN.XLA!DEC2BIN",123)
1111011

 

[Andibevan]

Thanks Tom - I will have more of a play - as you said - I can always set a
reference as a workaround for the moment.

Ta

Andi

 

[Bob Phillips]

Try the other suggestion Andi, set a reference and see if that works.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andibevan]

Tried that and it works fine - I find the problem with setting references
like that is that it limits the portability of the spreadsheet but it may be
that I am missing a trick.

Thanks

Andi

 

[Bob Phillips]

It shouldn't, as long as the target machine has the add-in installed. The
reference will move with the spreadsheet.

--

HTH

RP
(remove nothere from the email address if mailing direct)