Creating an addin from an Excel macro.

Conclusion

This article has shown a little bit of what it takes to convert a self built macro to a utility fit for distribution to others. Of course there are many more things to account for when one designs an add-in. Things like changing (and setting back!) Excel-application settings and designing a proper user interface haven't been addressed at al.

Nevertheless, the subjects discussed in this article will certainly get you started to create a professional looking add-in from a simple macro.

Books

To those of you who need more in-depth information, I would very much like to recommend these books:

Professional Excel development (update: Pro Excel dev II has been released)
Authors: Stephen Bullen, Rob Bovey and John Green
Published by Addison-Wesley, February 2005.
ISBN: 0321262506

Or:

Excel 2003 Power Programming with VBA (There is a 2007 edition as well)
Author: John Walkenbach
Publisher: Wiley
ISBN: 978-0-7645-4072-1

If you're more of a beginner, try the Dummies series:

Excel 2007 VBA Programming for Dummies. (there are separate editions for the older Excel versions)
Author: John Walkenbach
Revision author: Jan Karel Pieterse
Publisher: Wiley.
ISBN: 978-0-470-04674-6

Comments

Showing last 8 comments of 56 in total (Show All Comments):

Comment by: Jan Karel Pieterse (2/1/2011 5:22:03 AM)

Hi Chris,

I expect the best place is somewhere near where the copying of the add-in is done in the code:

'Copy addin to install path
FileCopy msCurrentAddInPath & Application.PathSeparator & gsFilename, gsPath & gsFilename

Application.TemplatesPath seems to be the default location Excel is expecting templates to be, but I have never looked into this property and how it works exactly.

Comment by: Chris (2/1/2011 12:56:35 PM)

Thank you, Jan. Application.TemplatesPath & "file.xltm" did the trick.

Comment by: Priyanka (2/6/2011 10:51:13 PM)

Hi Jan,

Thanks for sharing such a great article.
It helped a lot.

But I have a problem, my add-in uses a UserForm but I am not able to show that userform when I click the add-in tab that i have created using your example from
http://www.jkp-ads.com/Articles/DistributeMacro03.asp.

I am able to create a tab in Ribbon area but when I click my add-in it gives error saying : "Wrong number of arguments or invalid property assignment".

I am a newbie in excel vba. Please suggest what shoul i do to show my User Form when I click on my add-in tab.

Thanks

Comment by: Jan Karel Pieterse (2/6/2011 11:00:31 PM)

Hi Priyanka,

Exactly on which line of code do you get that error?
Does the form show if you try to run the subroutine directly (Alt+F8)?

Comment by: Priyanka (2/6/2011 11:22:53 PM)

Hi Jan,

Actually when I run my add-in from Developer side , i.e, where my VBA project is shown, it works fine. but when I click the tab that is created on Ribbon on Excel Home , it shows the message. May be there is something wrong with my code.
But could not find what to do and where?

Thanks

Comment by: Jan Karel Pieterse (2/7/2011 12:53:25 AM)

Hi Priyanka,

If you get the error, click the debug button. Post the routine in which the highlighted line of code is located. Indicate which line was highlighted please.

Comment by: Priyanka (2/7/2011 2:36:09 AM)

Hi,
Thanks for the reply and suggestions.

No line of code gets highlighted. When I debug it through VBA project it works fine. But when i click on add-in button it gives message. no debugging issue occurs there.

Is there a way to define the startup routine in an add-in, i guess my add-in is not getting that routine or something like that.

Comment by: Jan Karel Pieterse (2/7/2011 2:49:18 AM)

I guess I did not make myself clear.

WHat I want you to do is click the addin button. Then you get the error message. Then I would like you to click the debug button on the error message window.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.