We help IT Professionals succeed at work.

Distribution of a macro

rtod2
rtod2 asked
on
I want to start distributing a macro for Excel 2010 in a manner that a user can install with little or no fuss. Once installed, I need them to see a big button in the ribbon to click on. Ideally, I would not want them to be able to see the underlying code without some significant effort. I need some thoughts on how best to accomplish this?
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
I would recommend distributing an add-in.  You can make visible to the user only the macros you'd have them execute (so they will only see public macros from the developer's ribbon, for example) or functions to use in the workbook.

In VBE Explorer (ALT-F11) you can LOCK the project with a password.  Short of developing a compiled add-in, or obtaining hardware keys and going through that method, I believe the password locking approach to be the best, especially if we define significant effort as having to BREAK a password with some shareware program.

If this is what you're looking for, the attached is an add-in template that you can modify.  When done/tested, you can lock the VBA Project, and save as .XLA or .XLAM then distribute.

Please review/test attached.

Dave
Add-in-Template.xls
My thought would be to create the macro as an addin.  It's a fairly simple process to install the new addin, and it would give you the ability to add buttons to the ribbon, in the add-in section.  If you are interested in using this process, let me know and we can get into details of how to add the buttons in.
Most Valuable Expert 2012
Top Expert 2012

Commented:
To make a BIG FAT BUTTON, we can do some XML development and integrate that into the Excel 2007/2010 Ribbon.

Here are some links:  http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/26/customuieditor.aspx
http://www.rondebruin.nl/ribbon.htm#Sample

I'll build you one using the Add-In Template (now .XLM) as a base.

Author

Commented:
OK, I think the Add-In solution is probably the best. I am a little concerned about the user being prompted for a password if they typed Alt-F11.  That just makes them "want" to look for a crack.  How could we make it where they had the option of entering a password but the where and how to enter it were hidden. Then that might work?

Author

Commented:
That way Alt-F11 either has no impact or shows an empty macro list. Then the password thing could perhaps be brought up by knowing the correct key sequence to get it.
You can also add a button in without editing the XML ribbon code.  It's not as flexible as manipulating the ribbon, but it's MUCH easier to do.  It will appear in the add-ins tab of the ribbon, and you won't be able to put it anywhere else.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok - this one will give you the BIG BUTTON on the HOME tab.  I've left the Add-in Menu, but you can comment out the CreateMenus() call in Open Workbook.  The macro to run is myMacroToRun() which is a stub that can call whatever macro you want, or you can code it there.

Play with it.

Save it as a .XLAM and load as an add-in, etc.  When you're fine with it, password protect your VBA project and you're good to go.

I used the links I posted above for the basics.  Also, I downloaded the icon galleries to get the icon I used - you just need the name after that and it will load it with the XLAM/XLM file.

Here's a link to the icons gallery download:  http://www.microsoft.com/download/en/confirmation.aspx?id=11675.

Steps:

1.  Start with the add-in template, save as a new copy, somewhere.
2.  Open the Custom UI editor and load some XML code.  You could just open the .XLM I'm uploading and see the code there, you can modify it (without the Excel file open!) there, change the icon with new icon name, add more buttons, groups, etc., then save.
3.  Load the updated XLM file and test, and potentially not load the Excel 2003 commandbar menus, via CreateMenu() call on Open_Workbook event.
4.  Save as .XLAM after locking the VBA project.

See attached test add-in.  VBA PROJECT PASSWORD = "password"
Also, original .XLSM Excel file.

Here's the XML Code for the BIG BUTTON GROUP and BUTTON:
 
<!-- This is example :  Add custom group in built-in tab   -->


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

  <!-- Add Custom group to the Home tab in the ribbon-->
  <!-- The example add three buttons to the group and a menu button with 5 options-->

  <ribbon>
    <tabs>

      <tab idMso="TabHome" >
        <group id="customGroup1" label="Big Button Group" insertAfterMso="GroupEditingExcel" >
          <button id="customButton1" label="Big Button" size="large" onAction="myMacroToRun" imageMso="CondolatoryEvent" />
        </group>
      </tab>

    </tabs>
  </ribbon>

</customUI>

Open in new window


Finally, if you make changes to the XML - e.g., adding more macros to call off more buttons, you need to generate the callback functions within the Custom UI, or you'll get an error when you try to run the macro.

Enjoy!

Dave
Add-in-Template.xlam
Add-in-Template.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
@Cbrine -  that was my first posting, using the Excel 2003 style menus.  Now, I think, rtod has both options.

Cheers,

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
>> OK, I think the Add-In solution is probably the best. I am a little concerned about the user being prompted for a password if they typed Alt-F11.  That just makes them "want" to look for a crack.  How could we make it where they had the option of entering a password but the where and how to enter it were hidden. Then that might work?

>>That way Alt-F11 either has no impact or shows an empty macro list. Then the password thing could perhaps be brought up by knowing the correct key sequence to get it.

Unfortunately, this is only accomplished (top of mind) by disabling the control sequence and modifying the base Excel Ribbon menus - both Excel Options (re: add Developer's ribbon is a popular item), etc.

Or you could just disable Alt-F11.  I think if you want to go down this path, its a new scope for a new question, as there are several approaches to discuss and you have your distributable add-in approach with a big button, as originally requested.

Cheers,

Dave
Dave,
  Didn't look at your addin...sorry about that.

Author

Commented:
I am totally confused.  To avoid confusion, let's avoid the big button.

Question Modification:
What are the easiest steps to take to get an 'obvious button' tied to a macro and protect the macro in some way?

Author

Commented:
Would need it to be a one-click install for a user.
Most Valuable Expert 2012
Top Expert 2012
Commented:
My first post, make your changes for your macro, then password protect the project and send to the user.  The menu for the "button" is in the Add-Ins tab.

If you liked the second, the button is already implemented.  Just use that workbook.

Regardless of approach...

If you want the add-in to install itself, there are a couple approaches (and I'll check on the code for that), but first let's see if you like either post.  You need to try something out and give feedback for me to positively proceed.  That way, my next post will be razor sharp :)

Dave

Author

Commented:
Hmmm,
The file starts out as a CSV file.  I have to actually save the file first as an Excel file and then add it to the macros for that sheet to run it.

What I would like to do is give someone a program to install that would create an Excel button that would live in Excel for them.  This way, whether they have a CSV or not, it won't matter and the macro will still run. Obviously once it is run, they have to save the Excel file but not before.

Code not yet finished but is not necessary to post either.  The macro is named TOSReport and this is version 1 of it.  I will change it over time.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok.  Is there anything you're expecting from me at this time?

Dave

Author

Commented:
Yes.  I'm unclear on how to accomplish that objective.
Most Valuable Expert 2012
Top Expert 2012

Commented:
I'm not sure there's a difference, but let's explore...  This may be the first time you've worked with your own add-in and I had some trepedation the first few times as well, and it wasn't that long ago!

Here's my mental model on the problem/resolution:

You create an add-in that has this wonderful macro and you distribute to those who need it.  You've been given two very good example templates for add-ins - either the Add-In menu or a Ribbon button that require no additional modification than for you to call your "real" macro from.

I'm assuming the CSV file starts with you or "the system" and somehow, sometime, it is sent to a user who saves it in a known location.  The macro you will write that is part of the add-in will (as a result of user acting with the BUTTON/Menu Item) prompt the user for the CSV file and do whatever it is you want done with it.

The user is informed to use that add-in they have installed to do work with the CSV file.

Make sense?

Dave