How to make an add-in/global macro

Colin Legg · #1 12-13-2012, 10:59 AM

Hi,
When you first start with VBA add-ins they can be extremely confusing - there's a steep learning curve involved.

(1)

When you install the add-in, this tells Excel that it should open the add-in. The add-in will automatically be opened when it is installed or when you launch Excel. This is expected behaviour. For Excel to run VBA code, the workbook that contains that VBA code must be open. The same applies to VBA add-ins. This however, should not be an issue for your end-users. If the ThisWorkbook.AddIn property of the add-in is set to true then all of the worksheets of the add-in will be hidden from the Excel UI. The fact that the add-in workbook is open will only be apparent in the VBA IDE. For clarity that the file is an add-in, you could save the add-in as an xlam file instead of an xlsm file.
The toolbar/menu customisation should be done dynamically by the add-in when it is opened. You can do this with VBA code in the Workbook_Open() or Workbook_AddinInstall() event handlers. It shouldn't be done in advance. When the add-in is unloaded, it should clean up after itself and remove the toolbar/menu customisations. This is done from the Workbook_BeforeClose() or Workbook_AddinUninstall() event handlers. This way, the toolbar/menu controls relating to your add-in will only be displayed when the add-in is installed, making it a user-friendly experience. Your profile says that you are using Excel 2003, but your add-in seems to have an Excel 2007+ format so I can't be more specific on this. Are you actually customising the ribbon with RibbonX?

(2)
I can't tell you what is wrong with your code unless you post the code (or attach the add-in) on the thread. I'd suggest you address the points I raised in (1) above and then let's see where we're at.
Once you've got your add-in working there are further considerations regarding deployment and updating. This is just a heads-up that once your add-in is working perfectly, We need to go through these deployment and updating issues before you distribute the add-in to your colleague(s).

Incidentally, you can also use Personal.xls / Personal.xlsm to have macros available across all of your Excel. But this would be personal to you.

hanvyj · #2 12-14-2012, 03:23 AM

Quote:

Originally Posted by Colin Legg

Hi,
When you first start with VBA add-ins they can be extremely confusing - there's a steep learning curve involved.

(1)
When you install the add-in, this tells Excel that it should open the add-in. The add-in will automatically be opened when it is installed or when you launch Excel. This is expected behaviour. For Excel to run VBA code, the workbook that contains that VBA code must be open. The same applies to VBA add-ins. This however, should not be an issue for your end-users. If the ThisWorkbook.AddIn property of the add-in is set to true then all of the worksheets of the add-in will be hidden from the Excel UI. The fact that the add-in workbook is open will only be apparent in the VBA IDE. For clarity that the file is an add-in, you could save the add-in as an xlam file instead of an xlsm file.
The toolbar/menu customisation should be done dynamically by the add-in when it is opened. You can do this with VBA code in the Workbook_Open() or Workbook_AddinInstall() event handlers. It shouldn't be done in advance. When the add-in is unloaded, it should clean up after itself and remove the toolbar/menu customisations. This is done from the Workbook_BeforeClose() or Workbook_AddinUninstall() event handlers. This way, the toolbar/menu controls relating to your add-in will only be displayed when the add-in is installed, making it a user-friendly experience. Your profile says that you are using Excel 2003, but your add-in seems to have an Excel 2007+ format so I can't be more specific on this. Are you actually customising the ribbon with RibbonX?

(2)
I can't tell you what is wrong with your code unless you post the code (or attach the add-in) on the thread. I'd suggest you address the points I raised in (1) above and then let's see where we're at.
Once you've got your add-in working there are further considerations regarding deployment and updating. This is just a heads-up that once your add-in is working perfectly, We need to go through these deployment and updating issues before you distribute the add-in to your colleague(s).

Incidentally, you can also use Personal.xls / Personal.xlsm to have macros available across all of your Excel. But this would be personal to you.

Thanks a lot for the detailed reply!

Setting Workbook.IsAddin to true and saving it as an addin file makes it behave how I wanted, hiding the workbook from view. I do have excel 2010 now, the office updated it since I last posted! I will try and change my profile.

I just had a look at RibbonX, that sounds like a right pain! I will have to investigate that a little further.

My macro doesn't work because it uses "ActiveSheet" which is the macro sheet, not the sheet the user is on when they press the copy/paste buttons. I resolved it using "ActiveWorkbook.ActiveSheet":

Code:

Dim copiedCells As Range

Sub CopyFormulae()
    Set copiedCells = Application.Selection
End Sub

Sub PasteFormulae()
    Dim s As Range
    Set s = Selection
    
    'ActiveWorkbook.ActiveSheet
    For columnPaste = 1 To copiedCells.Columns.Count
        For rowPaste = 1 To copiedCells.Rows.Count
            Dim c As Range
            Set c = ActiveWorkbook.ActiveSheet.Cells(s.Row + rowPaste - 1, s.Column + columnPaste - 1)
            c.Formula = copiedCells.Cells(rowPaste, columnPaste).Formula
        Next rowPaste
    Next columnPaste
    
End Sub

Sub ClearCopiedData()
    Set copiedCells = Nothing
End Sub

Regarding RibbonX, I have written an XML definition of my buttons:

Code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabHome">
        <group id="GroupFormulae" label="Formulae" 
        	insertAfterMso="GroupClipboard" >
          <button id="ButtonFormulaeCopy" label="Copy"
          	size="Small" image="HappyFace"
	  	onAction="ButtonFormulaeCopyPressed"/>
	  <button id="ButtonFormulaePaste" label="Paste"
          	size="Small" image="HappyFace"
		onAction="ButtonFormulaePastePressed"/>
          <button id="ButtonFormulaeClear" label="Clear"
          	size="Small" image="HappyFace"
		onAction="ButtonFormulaeClearPressed"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

and the even handling:

Code:

Sub Workbook_AddinInstall()
    
End Sub

Sub Workbook_AddinUninstall()
    
End Sub

Public Sub ButtonFormulaeCopyPressed(control As IRibbonControl)
    MsgBox "test1"
End Sub

Public Sub ButtonFormulaePastePressed(control As IRibbonControl)
    MsgBox "test2"
End Sub

But I'm having trouble on how to get excel to load/unload the new xml file, Can I only do this through the 3rd party tools available?

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need macro to make list of "defined terms"	glnz	Word VBA	8	09-07-2022 02:09 PM
Are changes to the ribbon global?	TechEd	Word	0	05-26-2011 12:23 AM
Global template	ryalls3857	Word	1	12-27-2010 05:45 PM
Can I automate or make a macro for this?	mkoenig	Word VBA	1	01-31-2010 02:47 AM
how to make global changes in existing doc?	hamster	Word	0	03-18-2009 08:39 AM