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 add-ins > 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

All comments about this page:


Comment by: Erum Hyder (4/27/2006 9:08:57 AM)

The article is great! It explains everything in great detail and yet makes it so simple to understand.

 


Comment by: Jan Karel Pieterse (4/27/2006 10:17:20 AM)

Hi Erum,

Thanks!

Regards,

Jan Karel Pieterse

 


Comment by: Darren (5/23/2006 5:01:37 PM)

Great article. Well put together and easy to read.

 


Comment by: Ivan Da Bologna Italy (6/2/2006 9:32:55 AM)

I appreciate your well-remarked work.
I will use add-ins from now, thanks a lot

Ivan

 


Comment by: McQ (8/23/2006 7:46:49 PM)

Gave me a great head start. I really appreciate the example code. Fantastic contribution. Thanks for sharing...

 


Comment by: Claude1966 (11/8/2006 9:17:15 AM)

Great article, clearly written too. I have one question, does this apply to user defined functions as well. That is can I use a function of the following form:
"Function UDF (input as variant) as double" in any spreadsheet (if I create it in an addin)?

 


Comment by: Jan Karel Pieterse (11/8/2006 12:22:43 PM)

Hi Claude,

Thanks!

To answer your question:
Yes, as long as you use the Public keyword:

<font face=Courier New>Public Function MyUDF(Arg1 A Variant, Arg2 As Varian) As Variant<font face=Courier New></font><font face="Trebuchet MS">

 


Comment by: Amit jaggi (1/3/2007 2:58:33 AM)

This is very helpfull for me.

That is very easy method.

Thank you

 


Comment by: Lars Herminius (1/5/2007 4:52:06 AM)

Very useful and well written. I have a similar question to the one above: is it possible to have module code (subroutines, functions) in an add-in that can then be called from a module within a separate file? I can't get this to work...

 


Comment by: Jan Karel Pieterse (1/5/2007 6:30:04 AM)

Hi Lars,

Thanks fo the kudos!

To your question: There are various ways to do this.

1. Create a reference to the addin

For this, you should change the VBAProject name of the addin (Tools, VBAProject properties). After doing that, save the addin. Then select your workbooks VBA project and choose Tools, references. Find the addin's project and check the box. Now you can refer to routines in that project directly (depending on the scope you've given the subs: private or Public).

2. Using Application.Run
Check VBA Help for the exact syntax.

 


Comment by: Jim Cumming (1/24/2007 2:49:46 PM)

Great article, cleared up a number of things for me.

A big question - what's the best way to make macros available to others in Excel 2007, now that menus and toolbars have gone the way of the dodo?

 


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!

 


Comment by: Ajay K Tripathi (7/14/2008 10:27:57 PM)

Siply Excellent. I got what I was trying from many weeks.
Thanks a lot.

 


Comment by: Yusuf Ahmed (6/2/2009 4:14:31 AM)

I have several add-ins. Some of my users may need to install two or more of the add-ins. I am having trouble getting the second add-in to go in the tab and group created by the first add-in. Is it possible to add to the same tab and group on the ribbon at different times.

 


Comment by: Jan Karel Pieterse (6/2/2009 4:47:00 AM)

Hi Yusuf,

This is certainly possible, but requires special syntax. This is excellently described in this book: RibbonX by Ken Puls et al. See my books page for a link.

 


Comment by: Yusuf Ahmed (6/2/2009 11:38:19 PM)

Thank You for the information. I found this tutorial very helpful. It got me going on the right track and now I have new questions for which you have provided a reference. Thanks again. This is really helpful.

 


Comment by: Johnnie Lockett (6/16/2009 12:33:09 PM)

This is not only helpful for the subject matter involved it is also a tutorial on how to properly write tutorials! Over several hours of searching the web, I was able to find only bits and pieces of what you have managed to teach all in one place.

Also, a pet peeve of mine involves the lack of care usually evident on web pages when it comes to literacy, articulation, and spelling. To many, this may be a minor consideration, but I tend to have more confidence in the information being taught when the author is as assiduous as you are about the presentation.

Thank you. Thank you. Thank you.

 


Comment by: Jan Karel Pieterse (6/17/2009 1:43:23 AM)

Hi Johnnie,

Wow, <blush>. Thanks for the compliment, much appreciated!

 


Comment by: Ron Pero (4/13/2010 6:13:22 PM)

Hello,
I have created 3 very simple macros that clean up contact data exported out of a contact database program. I saved the macros as an Excel add-in in order to distribute them to other people who use that program. I tested the add-in by loading it on another PC and everything worked fine except that I had to change the security level to low in order for Excel to accept the Add-In and not disable the macros. It looks like I can somehow be listed as a trusted source which would allow the add-in to run at medium level but I'm not sure how to list myself as a trusted source or to be able to get my macro signed. This looks like an expensive process considering that this is just for 3 very small macros. Can you tell me how to list myself as a trusted source and if there is any low cost way to add a digital signature? Your help would be greatly appreciated. Thank you.

Ron Pero

 


Comment by: Jan Karel Pieterse (4/14/2010 12:15:39 AM)

Hi Ron,

If you install the add-in, it will automatically be trusted, provided Excel is set to trust addins (a security settings added with Excel 2007), there is no need for code signing there.
Installing an addin can be done from the add-ins dialog, using the browse button, or -if the file is placed in the usser's addin library folder- by checking the add-in in that dialog.
For 2003 it is Tools, Add-ins.
For 2007 it is (IIRC) Office button, Options for Excel, Add-ins, select Excel add-ins and press Go button.

 


Comment by: Yuri (7/13/2010 4:41:17 AM)

Hallo, Jan.
Your site is great for VBA developers, but I haven't found a solution for sharing AddIn for several users that attach an AddIn to work with it simultaneously. For example, 10 users need to run an Application for Excel that uses the same AddIn. Please, provide your advice for the following issues.
1. Can two or more users open an AddIn file concurrently (e.g., over a LAN) and use VBA code from it programmatically (say, via the "Run" operator). For example, they could open the AddIn in the WorkBook_Open procedure of their workbooks that need this AddIn for work.
2. If the answer is "Yes," what options should the AddIn been prepared with (and what are the limitations)? I need to ensure that using VBA from AddIn concurrently won't harm the Application. Should I use any block/release techniques? Should I allocate anywhere, for each user, a separate data-storing area (or file?) to keep there values of public variables that change while the Application is Running?
Thanks in advance.
Yuri.

 


Comment by: Jan Karel Pieterse (7/13/2010 5:03:35 AM)

Hi Yuri,

As long as you're not modifying anything in the add-in ITSELF (and saving it), there is no limit to the number of users that can open the addin at the same time. In fact, I would set the add-ins readonly propery to true (right-click file, properties, check box) so you can easily replace the add-in should you have an updated version.

 


Comment by: Yuri (7/13/2010 5:28:01 AM)

Jan, thanks for the answer.
And what about using the same AddIn concurrently by ONE user who opens in the same Excel session TWO different workbooks that require the same AddIn? It's just a problem of using shared memory, I suppose. For example, that AddIn has declarations of public variables. If the first workbook instance changes the variable, the second instance could malfunction after getting the variable value, couldn't it? May be, I haven't got sufficient info on sharing memory by Excel session instancies, could you pls, give me an appropriate link?

 


Comment by: Jan Karel Pieterse (7/13/2010 6:45:37 AM)

Hi Yuri,

It isn't all that complex.
All variables are local to the Excel session. So if both workbooks are opened in their OWN Excel session, then each has its own variable values.

If they are opened in the SAME Excel session, the global/module level variables have the same value for each open workbook in that session. So you will have to cater for a situation like that yourself.

 


Comment by: Yuri (7/13/2010 7:37:24 AM)

Jan, thank you. Now it's all clear.
In case of working with workbooks in the same session, I am going to introduce individual instances of global variables (intended for the corresponding workbooks). To store these, I will use a collection of objects defined in the AddIn. An object will have two properties: Name and Value. Keys to the objects will be based on names of workbooks that use the AddIn.
Best regards, Yuri.

 


Comment by: Yuri (7/26/2010 11:50:50 PM)

Hallo, Jan.
I am trying to have my application run in separate Excel session (each workbook should be loaded in its own Excel session to avoid problems caused by using global variables when several workbooks use the same AddIn in the same excel session).

I added the following code to Workbook1:


'-----
Private Sub Workbook_Open()

Dim xl As Excel.Application
Dim curbook As Workbook
Dim wbpath As String

'EN:Ensure the worbook is opened in separate Excel session
Set curbook = ActiveWorkbook
wbpath = curbook.FullName
If Application.Workbooks.Count > 1 Then
    On Error Resume Next
    curbook.ChangeFileAccess Mode:=xlReadOnly 'ThisWorkbook
    On Error GoTo 0
    Set xl = New Excel.Application
    xl.Workbooks.Open (wbpath)
    xl.Visible = True
    curbook.Close SaveChanges:=False
    GoTo e_open
End If

MsgBox "Message 1"

'. . . some code

MsgBox "Message 2"

e_open:
End Sub
'----


Then I created copy of Workbook1 and named it Workbook2.

When I load Workbook1 (from Windows Explorer), everything is OK = I continuously receive both messages. Then I load Workbook2 from Windows Explorer, without closing Workbook1.

I successfully receive Message 1, then Excel "hangs" until I manually select its tab in the Windows taskbar (this is the problem). Only then I see box of Message 2.

I am not sure that my Application would behave correctly in the similar situations. How do I resolve this problem and are there general directions for such situations?
May be, I incorrectly open another Excel session?

Thanks in advance.

 


Comment by: Jan Karel Pieterse (8/16/2010 3:16:33 AM)

Hi Yuri,

I would use a different aproach than starting a new instance of Excel each time, that will avoid the problems you are seeing in the first place.

I would add a class module to your addin that keeps score of which workbooks are open and what the values of the variables are for each of them.

 


Comment by: Matt (10/5/2010 1:45:30 AM)

Hi Jan - thanks for the great layout; v easy to understand. Do you cover how to add the help file for each UDF? I can't seem to find anything on that so I have many functions but the help is no available to users - I'm sure its simple but where do I add the path for each help blurb? Thanks!

 


Comment by: Matt (10/5/2010 1:58:08 AM)

ps - I know that via macros you can add a description for each function but i want to be able to let the user click on the <help on this function> link whcih shd link at a text file right?

 


Comment by: Jan Karel Pieterse (10/5/2010 6:32:57 AM)

Hi Matt,

Have a look at Application.MacroOptions, it allows you to specify a help topic Id and a helpfile for a given function written in VBA.

 


Comment by: Matt Carter (1/31/2011 10:20:28 PM)

I have some code like this (this is one example but there are hundreds of UDFs in my Add-In:


CatStr = "OvaSEA\Cross Section Functions"

Application.MacroOptions macro:="AutoProp", _
    Description:="Cross section properties based on perimeter definition", _
    Category:=CatStr, HelpContextID:=101, HelpFile:=psHELPFILE


It used to work fine that I could run the code, save the .xla file
and then the MacroOptions data was still there next time I open the
file. Now the Category and Description is being saved but I am losing
the HelpFile data. If I try to run the above code using Workbook Open
from the ThisWorkbook object in xla I get an error "Cannot edit a
macro on a hidden workbook"

Any idea of how to get around this?

 


Comment by: Jan Karel Pieterse (1/31/2011 10:21:24 PM)

Hi Matt,

Which Excel version are you experiencing this problem with?

 


Comment by: Chris (2/1/2011 12:40:10 AM)

Jan,

Thank you for your Setup Utility. It has been a great help deploying a project I've been working on.

I would like to modify this to also place a file in the template folder. Looking through the code there seem to be several places it can be changed, but I'm not sure the best way to do this. Do you have any suggestions?

Thanks,
Chris

 


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.

 


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].