Creating an addin from an Excel macro

Pages in this article

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 (27-4-2006 09:08:57) deeplink to this comment

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

Comment by: Jan Karel Pieterse (27-4-2006 10:17:20) deeplink to this comment

Hi Erum,

Thanks!

Regards,

Jan Karel Pieterse

Comment by: Darren (23-5-2006 17:01:37) deeplink to this comment

Great article. Well put together and easy to read.

Comment by: Ivan Da Bologna Italy (2-6-2006 09:32:55) deeplink to this comment

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

Ivan

Comment by: McQ (23-8-2006 19:46:49) deeplink to this comment

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

Comment by: Claude1966 (8-11-2006 09:17:15) deeplink to this comment

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 (8-11-2006 12:22:43) deeplink to this comment

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 (3-1-2007 02:58:33) deeplink to this comment

This is very helpfull for me.

That is very easy method.

Thank you

Comment by: Lars Herminius (5-1-2007 04:52:06) deeplink to this comment

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 (5-1-2007 06:30:04) deeplink to this comment

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 (24-1-2007 14:49:46) deeplink to this comment

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 (24-1-2007 22:47:02) deeplink to this comment

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 (31-1-2007 20:48:14) deeplink to this comment

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 (1-2-2007 02:26:53) deeplink to this comment

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 (1-2-2007 03:38:12) deeplink to this comment

Thanks, Marc and Indu!

Comment by: Zoppuy Ippy (1-3-2007 11:53:23) deeplink to this comment

Alliluja, or smth like that

Comment by: Vyacheslav Maliuhin (2-3-2007 05:58:34) deeplink to this comment

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 (2-3-2007 07:34:12) deeplink to this comment

Hi Vyacheslav,

Thanks!!

Comment by: Mohammed Haneef (14-4-2007 02:43:27) deeplink to this comment

Thanks alot!

Comment by: bjork (20-5-2007 01:18:27) deeplink to this comment

Wonderful site!

Comment by: Ansher. M (31-5-2007 06:41:52) deeplink to this comment

Excellent site.

Comment by: Sandra (19-7-2007 01:54:20) deeplink to this comment

Wish you luck!
google team.

Comment by: Mohan (7-8-2007 13:00:28) deeplink to this comment

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 08:48:24) deeplink to this comment

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 14:16:26) deeplink to this comment

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 (6-5-2008 00:48:02) deeplink to this comment

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 (14-7-2008 22:27:57) deeplink to this comment

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

Comment by: Yusuf Ahmed (2-6-2009 04:14:31) deeplink to this comment

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 (2-6-2009 04:47:00) deeplink to this comment

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 (2-6-2009 23:38:19) deeplink to this comment

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 (16-6-2009 12:33:09) deeplink to this comment

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 (17-6-2009 01:43:23) deeplink to this comment

Hi Johnnie,

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

Comment by: Ron Pero (13-4-2010 18:13:22) deeplink to this comment

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 (14-4-2010 00:15:39) deeplink to this comment

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 (13-7-2010 04:41:17) deeplink to this comment

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 (13-7-2010 05:03:35) deeplink to this comment

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 (13-7-2010 05:28:01) deeplink to this comment

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 (13-7-2010 06:45:37) deeplink to this comment

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 (13-7-2010 07:37:24) deeplink to this comment

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 (26-7-2010 23:50:50) deeplink to this comment

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 (16-8-2010 03:16:33) deeplink to this comment

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 (5-10-2010 01:45:30) deeplink to this comment

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 (5-10-2010 01:58:08) deeplink to this comment

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 (5-10-2010 06:32:57) deeplink to this comment

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 (31-1-2011 22:20:28) deeplink to this comment

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 (31-1-2011 22:21:24) deeplink to this comment

Hi Matt,

Which Excel version are you experiencing this problem with?

Comment by: Chris (1-2-2011 00:40:10) deeplink to this comment

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 (1-2-2011 05:22:03) deeplink to this comment

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 (1-2-2011 12:56:35) deeplink to this comment

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

Comment by: Priyanka (6-2-2011 22:51:13) deeplink to this comment

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
https://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 (6-2-2011 23:00:31) deeplink to this comment

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 (6-2-2011 23:22:53) deeplink to this comment

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 (7-2-2011 00:53:25) deeplink to this comment

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 (7-2-2011 02:36:09) deeplink to this comment

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 (7-2-2011 02:49:18) deeplink to this comment

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