Install Macros and User Defined Functions

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

• 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.
   

• There are no module sheets in Excel 97 (ver 8) or Excel 2000 (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   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 (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.