Creating and using add-ins

[Dale Fye]

I just finished writing some VBA code that will let me automatically refresh
the series of a chart (to account for more or less rows in the data set). I
want to use this code in several XLS spreadsheets, but only want to maintain
one copy.

I've saved this file as an XLA, added it to the list of add-ins for my
spreadsheet that has the charts I want to reformat. I then created a macro
that calls a function FormatChart( ) in the add-in. But when I run the
macro, I get a "sub of function not defined" error. It's as if the
application doesn't know to look at the add-in file for this function.

Any ideas?

 

[Dave Peterson]

You have a couple of options.

#1. Use application.run

dim myAddinName as string
myaddinname = "someaddinname.xla"
application.run "'" & myaddinname & "'!FormatChart"
'or to pass parms
Application.Run "'" & myaddinname & "'!FormatChart", A, B
'or to pass parms and return a value:

res = Application.Run("'" & myaddinname & "'!FormatChart", A, B)

#2. In the Calling workbook's project, you can set a reference to the addin.

First, rename the project's name (don't use the default--VBAProject). You'll
want it to be unique. Then save your addin with that new project name.

Then in the workbook doing the calling:
tools|references
and check that addin's project.

Now you can use those functions--just like they're built into excel's VBA.