Creating an addin from an Excel macro

Pages in this article

  1. Preparations
  2. Book or add-in
  3. Menus
  4. Toolbars
  5. Limit access
  6. Protect code
  7. Initialise
  8. Save Settings
  9. UI Languages
  10. Setup
  11. 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 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 (25-2-2012 15:59:47) deeplink to this comment

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 (19-4-2012 11:44:18) deeplink to this comment

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 (20-4-2012 02:51:45) deeplink to this comment

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 (28-11-2014 20:56:46) deeplink to this comment

Thanks, this is a very helpful starter


Comment by: Marty Helfer (15-4-2016 15:54:00) deeplink to this comment

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 (15-4-2016 16:09:06) deeplink to this comment

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:
https://jkp-ads.com/Articles/UpdateAnAddin.asp


Comment by: Marty Helfer (15-4-2016 20:57:03) deeplink to this comment

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 (18-4-2016 06:12:22) deeplink to this comment

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.




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