Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-13-2012, 09:04 AM
hanvyj hanvyj is offline How to make an add-in/global macro Windows XP How to make an add-in/global macro Office 2003
Advanced Beginner
How to make an add-in/global macro
 
Join Date: Feb 2012
Posts: 33
hanvyj is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 12-13-2012, 09:12 AM
hanvyj hanvyj is offline How to make an add-in/global macro Windows XP How to make an add-in/global macro Office 2003
Advanced Beginner
How to make an add-in/global macro
 
Join Date: Feb 2012
Posts: 33
hanvyj is on a distinguished road
Default

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 :|
Reply With Quote
  #3  
Old 12-13-2012, 09:13 AM
hanvyj hanvyj is offline How to make an add-in/global macro Windows XP How to make an add-in/global macro Office 2003
Advanced Beginner
How to make an add-in/global macro
 
Join Date: Feb 2012
Posts: 33
hanvyj is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 12-13-2012, 10:59 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline How to make an add-in/global macro Windows 7 32bit How to make an add-in/global macro Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #5  
Old 12-14-2012, 03:23 AM
hanvyj hanvyj is offline How to make an add-in/global macro Windows XP How to make an add-in/global macro Office 2003
Advanced Beginner
How to make an add-in/global macro
 
Join Date: Feb 2012
Posts: 33
hanvyj is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
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?

Last edited by hanvyj; 12-14-2012 at 04:23 AM.
Reply With Quote
Reply

Tags
addin, macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make an add-in/global macro 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
How to make an add-in/global macro Global template ryalls3857 Word 1 12-27-2010 05:45 PM
How to make an add-in/global macro 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft