Thread: Macro Distribution

Been going through Google and searches here for 3 hours now, and trying different things with no luck. But not a big Excel person (access mostly).
So I have a working VB macro in Excel 2007.
I need to distribute this (well 2 macros and a function actually) to some people that do not know how to install anything.
So I was getting the best way is via an Add-In.
But when I create the add-in file, and place it in my add-in folder, it shows up and I can check it, and it is listed as an active add-in.
How ever I see no way to run the macros.
Nothing is shown on my Add Ins tab, nothing is listed via the Macros list, I am not seeing anything.
So any suggestions as to what I am doing wrong?

Welcome to the forum.

First, in the VBE, select the add-in project and do Tools > VBA Project Properties. On the General tab, change the name to something recognizable, like projFoFa.

Then, select the project in your workbook, and do Tools > References, and set a reference to projFoFa.

Hi Fo_Fa

You're not doing anything wrong. As shg pointed out, you'll need to set a Reference to your Add-In. However, having done so, still

...nothing is listed via the Macros list, I am not seeing anything.

This will not change.

One of the anomalies of an Add_In is that the procedures DO NOT APPEAR on your macro list. However, they're there if the Reference is set.

You can access these procedures directly (if they're not Private) in your code with the Call construct such as Call DeleteDups where DeleteDups is a procedure within your Add_In. Using an Add_In takes practice and patience...having learned the anomalies, distribution becomes a breeze. You only need to give the file to the user and tell them where to put it. Better yet, I use TeamViewer and do it myself. Great, free tool (free for non-commercial use).

Add-Ins are a wonderful tool...only one workbook to maintain. Good luck.

Originally Posted by shg

First, in the VBE, select the add-in project and do Tools > VBA Project Properties. On the General tab, change the name to something recognizable, like projFoFa.

Then, select the project in your workbook, and do Tools > References, and set a reference to projFoFa.

Sorry, that is completely bogus information. I was posting in a similar thread in another forum for someone who wanted to access macros in another open workbook that was NOT an add-in.

You don't need to set a reference to an add-in. (You would want to set one to Personal.xls, which is not an add-in, but a hidden workbook.)

As John says, you won't see the macros in an add-in if you do Alt+F8, but you can enter the name (the Run button will enable when it's recognized), and you can assign a shortcut.

So now that I've totally confused things (sorry!), what is it you want to do that you can't?

If you want to write code that invokes procedures in an (installed) add-in or another (open) workbook, you can do this without a reference:

Application.Run "RangeToClipboard.xla!ShowRangeToClipboardForm

If you create a reference, you can do this:

ShowRangeToClipboardForm

Hi Fo_Fa

Shg beat me to the last comment regarding addressing the Add-In DIRECTLY if you've set a reference. One more point about setting the reference (Tools --> References)...you may need to Browse for your new Add-In to find it. I've had to do such on many occasions.

If you've been successful, you'll see the References as part of your project (see attached).

I am assuming it would appear in the ADD-IN tab then?

It would appear in Excel Options > Add-Ins, yes.

So I envision they add this Add-In,
and magically they have a button that will run the macros.

No magic, just work.

Adding a toolbar, or a button to a toolbar, in Excel 2003- was easy. Doing it in Excel 2007 is completely different, and I've never done it (lots of other people here have, though).

If you have only a few macros, you could assign shortcuts to them. Your assigned shortcuts would become live when they installed the add-in.

Do Alt+F8 to display the macro dialog. Select a macro from the list, or type the name of one in the box, and press Options. Follow your nose from there.

Hi Fo_Fa

I'm attaching a couple more pictures from my personal workbook to give you an idea what you'll see (and not see). In the "Screen Shot.jpg" you'll see that I've put a Button "Validate Register". In the "Sample Add_In Macro.jpg" you'll see the macro I assigned to that button.

As you see, the macro "validate_check_register" does not show up as an available macro. I simply typed the name in and it's available BECAUSE I set a reference to the Add-In. You don't see them but they're there. It's a difficult concept...not intuitive. Once you get it to work, it'll fall in to place.

Long story short, you assign the Add-In macro to a button (or call it from a procedure) the same way you would if it were in the active workbook. You just can't see it. Tough concept. Work through it...it works.

Hi Fo_Fa

I'm not familiar with this

put the buttons, already linked to the macros, actually in the Add-In and have them appear (in the add-in tab maybe)

although I'm quite certain others on the Forum could help you through that.

I'm AM familiar with working with Excel .xla and .xlam files. If you describe your process, perhaps I can help. For example, you get this

created automagically by a third party and I have no control

workbook. Do YOU get it or do all

people (96 locations around the globe)

get this "automagically" created workbook. Are YOU in between the workbook and the 96 users?

If you're in between and the "automagically" created workbook is not protected up the kazoo, you can do anything you want with it. You can add buttons to the worksheets, you can add code to the buttons and this can all be done WITH code. All you gotta do is press a button or call a procedure.

This may be simple, it may be complex. I may be able to help, I may not. If you're interested, lay out the process, step by step. If you're REALLY interested, post sample files and describe what you wish to do. (By the way, what is it that these Add-In procedures do that's "Special")?

How to load an addIn

Sub snb()
  Application.AddIns.Add("E:\invoegtoepassingen\snb.xla").Installed = True
End Sub

How to activate the macro 'vergeet' in 'module1' of the addin snb.xla

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Application.Run "vergeet"
End Sub

To add magic to your code:

Private Sub Workbook_Open()
  With application
    .AddIns.Add("E:\invoegtoepassingen\snb.xla").Installed = True
    .Run "vergeet"
  End With
End Sub