Problem accessing Sub routines in Addin modules

[JonWestcot]

Hi all:

I'm having a lot of trouble accessing Sub routines in an Addin from a
worksheet. For some reason, I can access the any Functions just fine like so:

=AddinFunctionName()

But, I cannot access similar Subs with what I thought was the correct syntax:

AddinSubName

When I try the latter, I get a compiler error.

ANY help will be greatly appreciated!

Thanks!

Jon

 

[jon.westcot]

Hi Peter:

Thanks for the info. Could you explain a bit more about creating a
reference to the Addin via a Tools reference? Is this different than
installing and enabling the Addin? The Excel 2007 Help system isn't exactly
a user-friendly platform.

Thanks again!

Jon

 

[Peter T]

UDF's in an addin can be used in cell formulas as you describe.

However, if you want code in your project to call a procedure in an addin
there are two ways

1. Set a reference in your project to the addin project via Tools references

2. result = Application.Run("myAddin.xla!myFunc", arg1)
or call Application.Run("myAddin.xla!mySub", arg1)

Regards,
Peter T

 

[Peter T]

In the VBE, under Tools, click References -

Find the project name of your addin, select and tick. Hopefully the addin's
project name has been renamed from the default "VBAProject" to something
unique (if not and it's your addin I suggest you do that).

After setting the reference you can refer to anything in the addin as if it
were in your own. Ensure there are no similarly named procedures in
respective projects.

Note there are issues to consider if your workbook is to be distributed to
other users who do not have the addin in the exact same path.

Setting this reference is not related whether the addin is installed into
the addin manager. If the addin is not already loaded, as it normally would
be if installed, it will load automatically as soon as it's required
(subject of course to it being in the same location as it was when the
reference was added).

Regards,
Peter T