Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Create Addins > Conclusion
Deze pagina in het Nederlands

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 63 in total (Show All Comments):

 


Comment by: DDuck (2/25/2012 3:59:47 PM)

Wish you had put all the tutorial pages in one instead of making 11 separate pages. Hope all the clicks are worth it.

 


Comment by: Ludo (4/19/2012 11:44:18 AM)

Hi Jan Karel,

'Of course there are many more things to account for when one designs an add-in.' is more than true!

Some things aren't mentioned in this article, example:
1) it's impossible to add worksheets in the Add-In at run time, unless you set the IsAddin property temporary to FALSE, add the worksheet(s) and back to TRUE after the worksheets are added.
2) You can't use the .Select property.
3) Refering to your add-in project or a worksheet(s) within your add-in need to be approached by ThisWorkbook.Sheets("Sheet 1"), and / or use SET
Example

Dim wbMyWorkbook as Workbook
Dim shMyWorksheet as Worksheet

Set wbMyWorkbook = Thisworkbook
Set shMyWorksheet = wbMyWorkbook.Sheets("Sheet Name")

With shMyWorksheet
Place your code here

End With
Set shMyWorksheet = Nothing
Set wbMyWorkbook = Nothing


I struggled more than a week to create a chart in a add-in, and found out that i couln't ADD a chart in the add-in at runtime, but that this needed to be already in place (and the needed series too) to get a chart with variable data.
Sometimes, as long as the IsAddin property = FALSE, the code runs smootly, but once you change this property to TRUE, the code crash. Not always clear to me why this behaves like this, but it happens.

Hope this message is usefull.

Regards,
Ludo

 


Comment by: Jan Karel Pieterse (4/20/2012 2:51:45 AM)

Hi Ludo,

Thanks for your thoughts.
One slight comment: there is no need to set an object variable to ThisWorkbook, as you can use ThisWorkbook directly. So this line of code:

Set shMyWorksheet = wbMyWorkbook.Sheets("Sheet Name")


Can be replaced with:

Set shMyWorksheet = ThisWorkbook.Sheets("Sheet Name")


and the variable shMyWorksheet can be omitted from the sub entirely.

 


Comment by: KineticFlow (11/28/2014 8:56:46 PM)

Thanks, this is a very helpful starter

 


Comment by: Marty Helfer (4/15/2016 3:54:00 PM)

Good Morning, I have multiple macros(5)that multiple(15-20)users need access to. I've been going into each PC and copying the macros into each users PERSONAL.xls. When business requirements change and a macro needs updating, I need to revisit each user. Will an Add-in allow me to save it on a network where the users can access it? Ultimately, if a change needs to be made, I'd like to make it in one spot and have it immediately available to the users. Is an Add-in what I'm looking for? Thank you very much. Marty

 


Comment by: Jan Karel Pieterse (4/15/2016 4:09:06 PM)

Hi Marty,

Yes an add-in would help you in that situation. If you make sure you place the add-in on a fileshare everyone has access to and if you remember to mark the add-in file read-only (right-click, properties in widnows explorer), then you can simply replace the addin file. People would have to restart Excel to get the new version though.

I show a more sophisticated method here:
http://www.jkp-ads.com/Articles/UpdateAnAddin.asp

 


Comment by: Marty Helfer (4/15/2016 8:57:03 PM)

Good Afternoon, I feel like I'm making headway! Just as "proof of concept" I've created a macro in a workbook all by itself. I saved it on the network as an add-in. I went back into Excel. Selected File, Options, Add-ins, <Manage: Excel Add-ins> Go. Select my two add-ins, click OK, restart Excel and I don't see them in "Data - Data Analysis". Am I missing something? Please let me know your thoughts...Marty

 


Comment by: Jan Karel Pieterse (4/18/2016 6:12:22 AM)

Hi Marty,

If this is Excel 2013/2016, you may have to repeat the install process of the add-ins. Make sure ALL instances of Excel are closed prior to doing this.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].