.xla Add-In Mechanics and Custom Ribbon Creation

[Sock]

Hi everyone,

I'm at the stage where I've made my code in VBA. I'm ready to pump it out as an add-in to be used in other workbooks and the likes as my coworkers please.

I'm testing this with excel 2007

To start I've put in code similar to this link: http://www.cpearson.com/excel/createaddinsamplecode.aspx in "ThisWorkbook" to load in menu items. (THIS IS TEMPORARY, I hope to have a custom ribbon made in the future, but I have no clue how to go about doing that yet).

I've saved my add-in as a .xla file (although it seems to be called an '.xlam' file. I hope that makes no difference) and I loaded it up in a blank workbook to see if the buttons would work.

I get the heart-crushing error: "Cannot run the macro 'NearFar.xlam!AutoForm'. The macro may not be available in this workbook or all macros may be disabled."

This happens with all the buttons in the "Add-Ins" area. Some are userforms, others are modules. I've made sure macros are enabled in this blank test workbook, as well as even going into the trust center and enabling all macros. I'm still confused as to what is causing this problem. I'm sure I'll have lots of tweaking to ensure this is a versatile add-in and that it can work in any workbook, but I can't even get out of the gate. What are the mechanics of an add-in when you're trying to use it on other workbooks? What might be required? I feel there is something fundamental I've missed in getting this to work.

As my last question, how does one make it such that when an xla add-in is being used in a workbook, a custom ribbon item is created for it? I've seen this as the case for more professional add-ins (that are .xla or at least have .xla functionality).

Thank you so much to everyone.

[Zack Barresse]

Hi there,

It's hard to give you any help as you haven't given a whole lot of information about your project. There's good background info, just little specifics. So I can't really address why your add-in isn't working. Let me give a little information on add-ins, see if that helps clear things up.

Add-ins are workbooks which, when installed, will automatically open when you open Excel. They will stay open until you close the application and they are always hidden. Beyond the XLA/M file format there are COM add-ins, which can be created with a multitude of programming languages, e.g. Visual Basic, C#, etc, using a program such as Visual Studio (there are Express versions available for free).

XLA is the pre-2007 file format for add-ins, while XLAM is the 2007 (and forward) add-in file format. There are some differences between the two. An XLA has the same limitations as any 2003 file format, i.e. only 65536 rows instead of 1M+ on the worksheets. Typically they are used to add some functionality which doesn't exist natively. There have been changes to the ribbon UI since 2007, and two (XML) schemas exist, one for 2007 and one for 2010. Currently there is no new schema for 2013. To take advantage of both ribbon schemas you would have to basically duplicate the code in the new formats. Take a look at Ron de Bruin's page on loading different ribbons in 2007-2013: http://www.rondebruin.nl/win/s2/win002.htm

So basically you don't use an add-in on other workbooks, they're always available once installed for every workbook opened in the application.

Regarding your heart-crushing error, I have no idea why you can't run the macro. I haven't seen any code or file. Hard to say more than that. I would make sure you have associated the workbook and routine in the button/objects OnAction property.

With an XLAM you can use the CommandBars still but it's recommended you create a custom ribbon for your add-in. CommandBar's work and have backwards compatibility but are no longer the preferred method for creating a UI. That being said there is a lot more work to create a custom ribbon, which is done with XML, and unlike XLA's, can't be created solely in Excel with VBA. Fortunately the editor is free, and there are tons of tutorials online for it.

Here is a link to get you started, which should be pretty much everything you need:
http://msdn.microsoft.com/en-us/library/aa338202%28v=office.12%29.aspx

Some other great info
http://www.vbaexpress.com/forum/forumdisplay.php?96-Office-2007-Ribbon-UI
http://www.excelguru.ca/blog/category/the-ribbon/
http://msdn.microsoft.com/en-us/library/vstudio/bb386104.aspx (for Visual Studio created add-ins)
http://www.contextures.com/excelribbonaddcustomtab.html (good beginner's tutorial)
http://www.fontstuff.com/vba/vbatut03.htm (geared towards Excel 2003 but good info)

[Paul_Hossler]

Did you install the addin? File, Options, Add-Ins, Manage Excel Add-in, etc.?

Paul

[Sock]

Did you install the addin? File, Options, Add-Ins, Manage Excel Add-in, etc.?

Paul

Hi Paul,

Yes I did :P The problem came from how I had coded in calling the procedures. Thanks for the response! I know sometimes it's the simplest of things that get us.


Hi there,

It's hard to give you any help as you haven't given a whole lot of information about your project. There's good background info, just little specifics. So I can't really address why your add-in isn't working. Let me give a little information on add-ins, see if that helps clear things up.

Add-ins are workbooks which, when installed, will automatically open when you open Excel. They will stay open until you close the application and they are always hidden. Beyond the XLA/M file format there are COM add-ins, which can be created with a multitude of programming languages, e.g. Visual Basic, C#, etc, using a program such as Visual Studio (there are Express versions available for free).

XLA is the pre-2007 file format for add-ins, while XLAM is the 2007 (and forward) add-in file format. There are some differences between the two. An XLA has the same limitations as any 2003 file format, i.e. only 65536 rows instead of 1M+ on the worksheets. Typically they are used to add some functionality which doesn't exist natively. There have been changes to the ribbon UI since 2007, and two (XML) schemas exist, one for 2007 and one for 2010. Currently there is no new schema for 2013. To take advantage of both ribbon schemas you would have to basically duplicate the code in the new formats. Take a look at Ron de Bruin's page on loading different ribbons in 2007-2013: http://www.rondebruin.nl/win/s2/win002.htm

So basically you don't use an add-in on other workbooks, they're always available once installed for every workbook opened in the application.

Regarding your heart-crushing error, I have no idea why you can't run the macro. I haven't seen any code or file. Hard to say more than that. I would make sure you have associated the workbook and routine in the button/objects OnAction property.

With an XLAM you can use the CommandBars still but it's recommended you create a custom ribbon for your add-in. CommandBar's work and have backwards compatibility but are no longer the preferred method for creating a UI. That being said there is a lot more work to create a custom ribbon, which is done with XML, and unlike XLA's, can't be created solely in Excel with VBA. Fortunately the editor is free, and there are tons of tutorials online for it.

Here is a link to get you started, which should be pretty much everything you need:
http://msdn.microsoft.com/en-us/library/aa338202%28v=office.12%29.aspx

Some other great info
http://www.vbaexpress.com/forum/forumdisplay.php?96-Office-2007-Ribbon-UI
http://www.excelguru.ca/blog/category/the-ribbon/
http://msdn.microsoft.com/en-us/library/vstudio/bb386104.aspx (for Visual Studio created add-ins)
http://www.contextures.com/excelribbonaddcustomtab.html (good beginner's tutorial)
http://www.fontstuff.com/vba/vbatut03.htm (geared towards Excel 2003 but good info)

WOW!! Thank you! Huge wealth of information here. I've been poring through it for a bit and I've been able to solve that pesky error problem. Now I know it works I have some things to work out (which I may post here in a different thread) before I move into creating the custom ribbon. Big big thank you to you once again.

I'll mark this as solved. Great work, team :D