#1
|
|||
|
|||
How to make an add-in/global macro
Hi, a colleague was complaining about there not being a copy formulae (without changing the references) function in excel, he used to have a macro that did it but it was lost.
I'm reasonably competent at macros so I said it would be no problem for me to make one that did what he wanted. It wasn't, I have a macro that copies and pastes how I want it - I even added some buttons to the ribbon so it should be easy for him to use. However, I can only use these with the worksheet I wrote the module on. I read that I need to add the worksheet as an addin to excel. I went to addins and went through it. Now I have two problems: 1) Whenever I open Excel, eg through the start menu, it automatically opens the "CopyPasteFormula.xlsm" that I added as an addin! This person is going to get really confused with that and forget to go file-new and destroy the addin sheet... This also means Half the time I click the button to run the macro it it comes up with "this file is already open etc etc" because its opened it before 2) The macros don't work properly anymore... I added the buttons, the macros appear in the "macro" section but don't operate as expected. I get an error copying something with no clipboard, as I would expect - but they don't actually copy and paste (they do If I do this in the "CopyPasteFormula.xlsm" sheet that I added as the addin). I tried adding some breakpoints and message boxes, neither worked. It might be related to the issue that I don't have access to the Application Data/Microsoft/Addins folder on my PC, so I selected "do not copy" when adding the addon. |
#2
|
|||
|
|||
I think I've worked out why Its not working.
It calls ActiveSheet, which is referring to the "CopyPasteFormula.xlsm" sheet, not the one where you are pressing the copy button... I need to somehow work out how to find out sheet that was active before Still don't know why it opens it as an addon every time though :| |
#3
|
|||
|
|||
It actually opens the addon sheet when I use the macro!
This can't be what addons should behave like surely? All I want is a macro that can be accessed across the whole of Excel! |
#4
|
||||
|
||||
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. |
#5
|
|||
|
|||
Quote:
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 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> 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 Last edited by hanvyj; 12-14-2012 at 04:23 AM. |
Tags |
addin, macro |
Thread Tools | |
Display Modes | |
|
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 |