Conclusion

JKP Application Development Services.

Microsoft Office Application Development

Conclusion

Up • Preparations • Book or Addin • Menu's • Toolbars • Limit access • Protect code • Initialise • Save Settings • UI Languages • Setup • Conclusion

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Creating an addin from an Excel macro.

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

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 this book:

Professional Excel development
Authors: Stephen Bullen, Rob Bovey and John Green
Published by Addison-Wesley, February 2005.
ISBN: 0321262506

Or:

Excel 2003 Power Programming with VBA (2007 edition soon to be published)
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. (to be published beginning of 2007, 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

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me (Maybe I'll add a feedback page with the feedback I get).

Rate this article:

(Rated: 372 times, average rating: 6.8)

Comments

Showing last 15 comments of 27 in total (Show All Comments):

Comment by: Jan Karel Pieterse (1/24/2007 10:47:02 PM)

Hi Jim,

You can still use commandbars, but everything your code did to them appears in Excel 2007's Add-ins tab. Also for the menu items to be available to all workbooks, you have to make it an addin.

Comment by: indu (1/31/2007 8:48:14 PM)

Hi,

The article covers almost all the topics.I spent so much time to find the information in other sites.No site helped me as this.

Great Work!

Comment by: Marc Messens (2/1/2007 2:26:53 AM)

Everything you ever wanted to know but were afraid to ask about .....

It explains more than what I was searching fore - perfect !!

Comment by: Jan Karel Pieterse (2/1/2007 3:38:12 AM)

Thanks, Marc and Indu!

Comment by: Zoppuy Ippy (3/1/2007 11:53:23 AM)

Alliluja, or smth like that

Comment by: Vyacheslav Maliuhin (3/2/2007 5:58:34 AM)

Thank you very much for your tutorials! Your tutorials are clear and perfect! :)

I am a programmer-hobbyist for 10 years now... Yesterday I had to write a VBA function for Excel. I had absolutely NO experience in VBA and Excel Add-ins. I also had NO practice in BASIC since high school... But your perfect tutorial "Creating An Add-in From An Excel Macro" saved many hours of my life :) Thank you!

Comment by: Jan Karel Pieterse (3/2/2007 7:34:12 AM)

Hi Vyacheslav,

Thanks!!

Comment by: Mohammed Haneef (4/14/2007 2:43:27 AM)

Thanks alot!

Comment by: bjork (5/20/2007 1:18:27 AM)

Wonderful site!

Comment by: Ansher. M (5/31/2007 6:41:52 AM)

Excellent site.

Comment by: Sandra (7/19/2007 1:54:20 AM)

Wish you luck!

google team.

Comment by: Mohan (8/7/2007 1:00:28 PM)

Thanks for the crisp intro and subsequent details. It helped clear up a lot of things after stumbling through other sites. I have a question though. My macro does not automatically show up in the workbook. i.e. when I use the macros button it shows up empty. However, if I type in the name of the procedure, it automatically enables the Run button. This seems to contradict the fact that by default all macros (i.e. procedures and functions)in an add-in will appear when we choose Alt-F8. BTW, I am using Excel 2003 - SP1. Thanks again..

Comment by: Jan Karel Pieterse (8/8/2007 8:48:24 AM)

Hi Mohan,

Thanks! To your question: For Addins, no subs show in the alt-F8 list indeed, I should update the text to reflect that. Any sub that is declared "public sub" or just without public can be run if you know its name (as you discovered).

Comment by: Tim (5/5/2008 2:16:26 PM)

Thanks for the excellent article!

I used the setup code you described on the last page and found that it installs the addin in the C:\Program Files\Microsoft Office\OFFICE11 directory as opposed to the user's addin directory.

If you make the following tweaks to the code, it will install in the user's addin directory instead.

Thanks for the great code! Saved me so much time!

change application.librarypath to application.userlibrarypath (note - you need to take out the extra backslash added on this line:

AddInLibPath = Application.LibraryPath & "\" & sFilename

add the following lines before the

Else

vReply = ... lines

Dim xlApp As Excel.Application

Set xlApp = New Excel.Application line

Set xlwbBook = xlApp.Workbooks.Open(AddInLibPath)

Workbooks.Open (AddInLibPath)

xlApp.AddIns.Add(AddInLibPath, True).Installed = True

Comment by: Jan Karel Pieterse (5/6/2008 12:48:02 AM)

Odd. All Path properties Excel exposes do not have the path separator at the end as far as I know, but you are the second one to report it does contain the path separator at the end and hence there is a duplicate \. I need to investigate this!

Add a comment too!!!

<<Previous

Subscribe in a reader