Install Macros and User Defined Functions

Location: http://www.mvps.org/dmcritchie/excel/install.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

The content of this page was formerly part of my Formula and Format page.

Install a Macro or User Defined Function (#install)

  If you would like to begin with a simple example take a look at this first.
 Getting Started with Macros -- http://www.mvps.org/dmcritchie/excel/getstarted.htm
Macros beginning with Worksheet_ or Workbook_ are Event macros and are covered on the Event macros page.
 
User Defined Functions and Macros are installed in (Normal) Modules in the same manner even though used differently.  The UDF will appear in the Function Wizard [fx] (Shift+F3), and the macros will appear in the Macros list  (Alt+F8, or Tools, macro, macros).

The easiest way to install a macro is to record a macro using the macro recorder.  Regardless of your version of Excel, the macro recorder will install a macro for you.  You can add additional macros and functions to the same module.  Don't put all your macros in one module though since each module that is loaded will add to memory usage.

  • Tools --> macro --> record macro
  • Do some things as a repetitive task such as: Select ALL button; Edit --> GoTo --> Special --> constants --> (select text clear other flags);
  • Tools --> macro --> stop recorder (or use square STOP button)

The Select ALL button can be found at the intersection of the row and column headings.

You can record your macro in your Personal Macro Workbook (in your XLSTART directory) by choice in the Store Macro In: box

Loss of the square Record Stop button with the [x] can be rectified with Right-click on a Toolbar --> Customize --> Toolbars - check the Stop Recording box. Close Customize dialog.  Another way if Excel is still open is to open your latest *.XLB file (use START, Find, *.XLB).

Version specific information

  • [XL95] In XL95 (Excel 7.0.A) You may install the macro in your personal workbook, or on the spreadsheet itself.  To Install the macro to your Module sheet.      Insert --> Macro --> Module       (if you don't have one)

    Also see  HELP --> Answer Wizard --> VBA

    Macros can be viewed in your macro sheets.
     

  • [XL97][XL2000]There are no module sheets in Excel 97 [XL97](ver 8) or Excel 2000 [XL2000](ver 9) - go to Tools -- Macro -- Visual Basic Editor to view macros / VBA modules

    Macros can be viewed using the VBA Editor (ALT+F11) then Project Explorer (Ctrl+R) from view menu.  Under Modules not Class Modules select the module that contains your code.  If you don't know you may have to use ALT+F8 then edit from the Excel spreadsheet.

    A hidden book cannot be edited from the ALT+F8 menu; so here are three alternatives for changing macros in hidden workbook.

    • From the VBE (Alt+F11), get into the Object Browser (F2), select the first item in classes, * <global>, find your macro on the right in members of globals and click on it.
    • Place the name of a macro or function into a worksheet cell and use my GoToSub macro to get to the VBE with the macro or function selected.  (see GoToSub within Build Table of Contents).  For Functions in hidden workbooks you will have to include the workbook name   i.e.  personal.xls!GetFormula
    • Unhide the workbook, Window, unhide then select from the F8 macros menu.  You will want to rehide your personal.xls file afterwards.

Common to XL95, XL97, XL98, XL2000, and up

  • Files found in the XLSTART library will all be opened by Excel at start up, but unless those workbooks are hidden in Excel you will not be able to access your macros transparently as if they were in your open workbook.  personal.xls is the name commonly used for a workbook in the XLSTART library for cross-application usage.  To hide your personal.xls book go to the Window menu and then hide/unhide submenu selecting personal.xls in the list below.  It is not sufficient that another workbook obscure an inactive personal.xls.  The personal.xls book must be both open and hidden.  Unlike macros, User Defined Functions (UDF) are not made accessible by merely hiding the workbook they are in, they require a reference.  i.e. =personal.xls!GetFormula(B3)   or you can add your personal.xls to your references...
     
  • To make your function or subroutine accessible to all of your workbooks, not just the one you added it to. 
    1. Locate your XLSTART directory,
      My XLSTART file is on my h: drive, the default would be c: drive, use the Windows Start --> Find files --> Find Files or Folders --> change c: using dropdown to My Computer, then in the top enter XLSTART and make sure subdirectories is checked.  for instance an example might be:
       c:\Program Files\Microsoft Office2000\Office\XLSTART\personal.xls
      but you should locate where your own XLSTART file exists.
    2. Create your personal.xls excel book in your XLSTART library.
    3. When you bring up another xls file, under Windows menu in Excel, hide your personal.xls file.  Anything in the XLSTART directory will be opened when Excel starts, but the macros in another workbook, including your personal.xls, will only be available if the workbook is hidden, unless you include the name of the open workbook.  You don't want to put other files in your XLSTART folder because you do not want Excel to open a lot of files.  Simply hiding a workbook will not make Functions available see References in next item. want to
      You may unhide your personal.xls file in order to update it, but don't forget to hide it again.  One way around this is to find the name of the macro and library and place that into a cell then use GoToSub to get to the code.
    4. One way to access Formulas from another workbook without specifying the workbookname, i.e. personal.xls!getformula(B12), would be to start in the VBE (alt+F11), select any module of the workbook that needs easy access to the formula then under Tools >  References   place a check mark next to your  personal.xls   -- which will make the entire workbook accessible.

     
  • Creating an addin is another method of making macros available
    Another method of providing macros and functions transparently to the user (invoke them without prefixing the subroutine with the bookname) is to create an add-in, see below.
     
  • Loss of use of UNDO:  Something to keep in mind when you use a macro is that you can not undo use of macro with Ctrl+Z.

Summary of ways to invoke a macro (#invokemacros) «

Alt+F8 --> Select Macro --  Run
I use a toolbar button Macros [Alt+F8]  to invoke this dialog.  ( personal.xls!MacroDialogBox)

Macros can also be run by several other means including:  toolbar buttons or menus, from a shortcut key, or from a Image on the Worksheet (Command Button). with a macro to Run from the active cell

To invoke a User Defined Function (#invokefunctions)

Same as you invoke builtin Excel functions, as a formula
    =GetFormula(A1)   in same workbook
    =personal.xls!GetFormula(A1)   from another workbook
    =GetFormula(A1)   with a reference to your personal.xls as in the VBE Tools, Reference.

User Defined Functions(UDF) will not show on the macro list, but will show up in the Paste Function Wizard [fx] (Shift+F3), near the end in the table on the left, in the "User Defined Functions".

Invoking Macros and Functions in another workbook from Code (#another)

There are two ways to do this [Rob Bovey, misc 2001-03-24].  You can either add a reference to the VBAProject of the workbook containing the macro you want to call and then call it directly, or you can use the Excel Application object's Run method.  In either case the macro must be public (e.g. it can't be preceeded by the Private qualifier and it can't reside in a module that contains the Option Private Module directive). The workbook containing the macro must also be open.  The difference between call and run: RUN is by the application and call call via a variable; whereas, CALL is determined at compile time in this case meaning when the macro starts.  (Run method shown below). 

For a simple subroutine with no arguments:
    Application.Run "Book1.xls!MacroName"
For a subroutine with a String argument and a Long argument:
    Application.Run "Book1.xls!MacroName", "Arg1", 2
For a function with arguments that returns a Boolean value:
    bReturn = Application.Run("Book1.xls!MacroName", "Arg1", 2)

To invoke a macro from same workbook.
Macroname
Call MacroName("Arg1", 2)
Module1.MyProcedure -- there may be more than one MyProcedure
[MyProject.xls].[Module1].Main -- more than one Main in MyProject workbook

Additional examples of calling a macro.

Additional Information on Macros (#more)

MS KB directions to install a macro included with Q142140 XL: How to Add the Workbook Path to the Footer
To insert a Visual Basic module into a workbook in Microsoft Excel versions 5.0 and 7.0, click the Insert menu, point to Macro, and click Module

In Excel 97 and Excel 2000 for Windows, press ALT+F11 to start the Visual Basic Editor; then, click Module on the Insert menu.  Be careful to select Module and NOT Class Module.  Many subroutines do not have explicit DIM statements so either add them or remove the Explicit code supplied at beginning of module.

Hiding macro from Macro Dialog:  Individually mark the SUB as private, or to affect all macros in the module with this statement at top   Option Private Module   You can have your macro call a Function.  A Function called from a macro can do anything that a macro can do unlike functions called from a worksheet.  Functions will not show in the macro dialog box.

Installing a macro specific to a single sheet in XL97 and XL2000
The following directions extracted from a posting by Bill Manton pertain to installing a macro specific to a single sheet.  Open the visual basic editor (Alt+F11).  Locate the project explorer (the upper-left pane by default).  It should have VBA Project (Book1).  Underneath should be Microsoft Excel Objects, and under that the sheets in the workbook and a workbook object.  Double-click on the sheet in which you want the functionality, and paste the code into the main code pane.

An alternative is to double-click on the sheet tab, select View Code, and paste your sheet specific mactro there.

Pasting macro code into a module sheet (XL95), or into VBA Editor (XL97 & XL2000)
You want to make both the Excel and the web source windows as wide as possible.  Pasted lines or any other lines turn RED if there is a syntax or other easily recognizable problem in Excel.  An underscore at the end of a line indicates a continuation.  Splitting a line without the continuation character is the most frequent syntax problem and code lines turn red as soon as continuation or other syntax errors are detected.  Actually the continuation character is really two characters, a space followed by an underscore. 

Working with VBE code
The VBE editor is hard to work with but does provide a useful feature for indenting/undenting code by selecting a block of code then use TAB key or Shift+TAB.  Incompatible with other editors where this would result in deletion of code.

In the project library (Excel 97 and up) the default name for the first standard module added to a workbook is module1 which would be created the first time you record a macro in your current workbook.  -- It is not named class1, it is not named sheet1, nor is it named This Workbook. -- information on these can be found on my Event Macros (event.htm) page.

How to make your function or subroutine accessible to all of your workbooks was previously discussed.

Searching VBE code
Use the Ctrl+F (Find) and check the "project" choice, you can search one project library (VBE code in one workbook) at a time.  Start from the first module in the project.


Visual Basic HELP is available in the VBE (Alt+F1), One such item can be found with the Answer Wizard: Function, then look at the topic "List of Worksheet Functions Available to Visual Basic".

Some useful information in getting started with macros can be found in the Excel HELP.

F1(Help) --> Answer Wizard --> Visual Basic
Look for such topics as "Run a Macro".  You can click on a topic, or proceed through the topics using the ArrownDN key.

Description information for a Function (#fundesc)

Your User Defined Functions (UDF) can be found using the Paste Function Wizard (Shift + F3).  Select "User Defined" which is near the bottom of the left-hand window and your UDF will appear on the right-hand window.

 

Entering a Description for the Function  [fx] Wizard (Shift+F3): in XL2000   (-- #fx --)
(directions to install a macro or function are below)
The parameter cell will show up in the Function Wizard Help without any intervention, so choose your parameter names carefully.  The description can be entered as follows:  Alt+F11 (VBE), F2 (View, Object Browser), Select the module (or <globals>) on left, select the function on right, RClick, properties, fill in description. 

For example the description for GetFormula reads as follows:
Displays the formula used in the referenced cell.  For more information see http://www.mvps.org/dmcritchie/excel/formula.htm#getformula

Creating descriptions for each argument of a function is not available within Excel but Laurent Longre has an add-in to to just that, Excel add-ins, which he has put in at the top of his page in English.

Creating new function categories (Excel 97 and up) in the Function Wizard.  This addin also by Laurent Longre in the English subset of his main site which is in French.  You can also look at a Babelfish translator at AltaVista simply type in http://longre.free.fr/ as the URL and then select Translate site from French to English.

Function names can be seen in the Paste Function Wizard (Shift+F3).  User Defined Functions are near the bottom of the left side window.

Restrictions on Functions:  A Function invoked from a Worksheet cannot modify any other cell in any manner, nor can it modify the format or formula of any cell including itself.  (No such restriction of macro functions -- functions called from a macro).

Optional parameters and default values in a User Defined Functions (UDF)
  'arguments 1-2 are required, arguments 3-5 are optional as string
  Function test_args(parm1 As String, parm2 As String, _
          Optional opt3 As String, Optional opt4 As String = "(4)", _
          Optional opt5 As String = "(5)") As String
     test_args = "-1- " & parm1 & ", -2- " & parm2 & ", -3- " & opt3 _
                & ", -4- " & opt4 & ", -5- " & opt5
  End Function
 

  usage note:  3rd parameter has null default value, 4th & 5th have default values
  =test_args("one","two")
  =test_args("one","two","three",,"five")

Can't get your function to work in XL97 and up
If you installed a function and get  #NAME!  then the function cannot be found.  If you get  #VALUE!  then your operands are incorrect.  The Functions described on this page and most macros are installed in a standard module, but that is not a exactly what appears as the choice on the menu in the Visual Basic Editor (Alt+F11,  Insert,  Module) used in Excel 97 and up.

Excel Add-In .XLA   (#addin)

Before continuing I would like to point out that for personal use your personal.xls file in the XLSTART directory may be all you need.  The trick is that the personal.xls file must be hidden (Window-->Hide) in order to find the macros and functions.  Hidden is not the same as obscured by another Excel sheet.  If you do not hide the file the macros and user defined functions (UDF) are only accessible by specifying the filename in front of the macro -- not very practical for general purpose usage.  All files in your XLSTART directory are opened when Excel starts up.  That said ...

Well I've been creating Macros subroutines and Functions and placing them into my personal.xls file, not realizing that there was more to it then that.  If a function didn't work there I made another copy back into my test file, never realizing that I had two copies of everything until I deliberately deleted copies in my test file (1998/08/09).  The functions no longer worked.  A quick check on what is now Google Usenet Archives to find out if I had lost some connection between the two turned up that I needed an add-in.  So thanks to Chip Pearson's posting (1998/04/23), I have created my first add-in. (see option 2 below in his posting)

1) Store the function in your Personal.XLS worksheet (or some other worksheet) and place this in the XLSTART directory.  Since it will be opened whenever you start Excel, it will be available at all times.  Even in this case, you'll have to precede (or "qualify") the function name with the workbook name: =Personal.XLS!MyFunction()

2) Store the function in your Personal.XLS worksheet (or some other worksheet) and save this worksheet as an Excel Add-In (Save As and change File Type to "Excel Add-In" at the very bottom of the list).  Then load in this add in, from the Tools->Add Ins menu.

According to Norman Harker the better library to place an addin is C:\Program Files\Microsoft Office\Office\Library so that you don't have to go searching for where it is located.  [ref.]

Creating an Excel Add-in -- http://msdn.microsoft.com/library/en-us/modcore/html/deovrCreatingExcelAddin.asp

Creating Add-Ins, Excel Tips, http://exceltips.vitalnews.com/Pages/T0869_Creating_Add-Ins.html

Create Addins, Jan Karel Pieterse, http://www.jkp-ads.com/articles/distributemacro00.htm

Add-in Links, links for addin, digital signature

Don't quite know how to do this yet, but I'm working on it.  More to the point don't like the manner addin's are implemented.

Found the following KB articles and it looks like everything is there.

Use the Excel Answer Wizard and enter:
tell me how to create a user-defined function

Addin workbooks Add-in topics on Ole P's site: What is an Addin, How to install one

Tip 45, Excel 97: Creating Add-Ins John Walkenbach

How to create addins , reply posting of links by Tom Ogilvy, 2000-05-30
Another tip from Tom Ogilvy on add-ins: Creating an Add-In

Digital Signature, Create your own digital certificate


Bookmarklets;  There are a lot of links on this page.  The following BookMarklets (Favelets) will distinguish between internal or external links.  First click on "highlight links",  and then on "int/ext links".  Your browser's Reset button (F5) will reload the original unmodified web page. 
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was split off from Formulas and Functions on January 29, 2004. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved