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 (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.
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
Common to XL95, XL97, XL98, XL2000, and up
Summary of ways to invoke a macro (#invokemacros) «
Alt+F8 --> Select Macro -- Run
To invoke a User Defined Function (#invokefunctions)
Same as you invoke builtin Excel functions, as a formula
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).
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
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 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
usage note: 3rd parameter has null default value, 4th & 5th have default values
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.
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 signatureDon'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:
- Q151490 XL: How to Create a Global User-Defined Function
- Q141288 XL: How to Use a Custom Function in Another Workbook
- Q108425 XL: How to Create Add-in Application Name and Description
- Q156942 XL97: How to Create an Add-in File in Microsoft Excel 97
- Managing Macros with the Visual Basic Editor By Paul Cornell, Microsoft Office Help Team
- List of webpage for AddIns, Tom Ogilvy, 2004-02-24, programming, and another list on 2005-08-26.
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
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved