none
How do I correctly add a global macro to Excel RRS feed

  • Question

  • I am trying in our installer to add a macro that is available to every worksheet. What we presently do in our wix installer is (both 32-bit & 64-bit):

    msOfficeApp.Workbooks.Add            
    msOfficeApp.Workbooks.Open (Session.Property("ExcelMacroLoc") & "AutoTagExcelMacro.xla")
    msOfficeApp.Workbooks("AutoTagExcelMacro.xla").RunAutoMacros 1
    ...
    <Property Id="ExcelMacroLoc" Value='C:\' />
    <CustomAction Id="AssignExcelMacroLoc" Return="check" Execute="firstSequence" Property ='ExcelMacroLoc' Value='[INSTALLDIR]'>
    </CustomAction>

    This works sometimes, but not always. Oftentimes we get some or all of the below. What is the best way to tell Excel where this VBA script is and that it's ok to use in any worksheet?

    Also asked on StackOverflow (I'll post any answer from there here).


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Saturday, February 1, 2020 10:37 PM

All replies

  • To:  David
    re:  Excel add-ins

    Installing an Add-In...
    http://peltiertech.com/installing-an-add-in-in-excel-2007/
    -or-
    Add or remove add-ins
    https://support.office.com/en-us/article/Add-or-remove-add-ins-0af570c4-5cf3-4fa9-9b88-403625a0b460
    '---


    Excel programs (now free) at MediaFire...
    The Custom_Functions add-in has 20+ new Excel functions including the X_vLookUp function.
    Download (no ads) from...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents



    Saturday, February 1, 2020 11:48 PM
  • Hi NLtL;

    I think what you're talking about is different, an AddIn built around a worksheet. What we have is a VBA script that is a function/macro. Or am I misunderstanding?

    ??? - thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Sunday, February 2, 2020 12:18 PM
  • To:  David

    I don't understand what your installer is trying to do.
    Your images reflect the use of .xla file type, which is an Excel Add-in file (up thru xl2003).
    The current (xl2007 and later) file type is .xlam
    (an Add-in is a hidden Excel workbook)

    A public function, available to all workbooks...
    Must be located in an Add-in installed on the computer that uses the function.
    -or-
    Located in a standard module in each workbook that needs it.

    Functions placed in a worksheet module are ignored as worksheet modules are private.

    Sunday, February 2, 2020 1:44 PM
  • Hi NLtL;

    I think step #1 is rename it to .xlam (we've had this since Excel 2000). And do we need to change anything in the module, or just rename the extension?

    We want to install this so any worksheet can use it. Based on what you said above, I think that means "Must be located in an Add-in installed on the computer that uses the function."

    Where/how do we do that?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing


    • Edited by DavidThi808 Sunday, February 2, 2020 2:01 PM
    Sunday, February 2, 2020 1:59 PM
  • To:  David
    re:  "how do we do that"

    If you are using only functions to be called from the worksheet:  "=Sludge()"  then no problem.
    If you have Sub routines that need to be run; that requires a button on the  Ribbon/Quick Access Toolbar.
    That has been made extremely difficult, since the Ribbon came into use, as there is no VBA code to handle that.

    You haven't explained your situation.  Are you selling an application or trying to provide features to your corporate users or something else?  In a corporate environment, you coluld create the Add-in and post it on the network along with instructions on downloading/installing.
    In any case, that is about as far as I can go with any help.
    '---

    For what it is worth, my free Custom_Functions Add-in is available at MediaFire.
    It has 20 or so functions called from the worksheet, similar to how built-in Excel functions are used...
      =ANYPART( vText, vDivider, vPiece, [vGetPosition]) as an example.  It might be helpful in seeing what can be done.
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Sunday, February 2, 2020 3:17 PM
  • It's the first case. We have a macro named AutoTag (AutoTagExcelMacro.xla) that we install on our customers computers. And it presently works fine, it's just those prompts are required to get it working.

    We sell a commercial product (on about 40,000 systems) and so this is used in a large number of companies on a large number of varied systems with different security policies.

    It's purpose is as follows. If you have cell content as follows:

    AutoTag("<wr:out select="a long long select statement" nickname="First Name" attrib2="a bunch more long text" attrib3="and even more ..."/>")

    Then when you are not on that cell, the cell will display "First Name". That's all it does. No need to explicitly call it, no ribbon command to fire it off. And our Excel AddIn (totally separate from the VBA) writes the cell contents with the AutoTag("...") surrounding it. So it's written in the cell for users that way.

    Does this explain it well enough? (Writing an Excel COM AddIn I know very well. But an Excel VBA script - I've got very little knowledge.)

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Sunday, February 2, 2020 4:42 PM
  • David,

    Sorry, can't help.  Good luck with it.

    NLtL

    Sunday, February 2, 2020 5:39 PM