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

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.

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.