Getting started with Excel macros and user defined functions, and your personal.xls (personal.xlsm in Excel 2007) workbook

Getting Started with Macros and User Defined Functions

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

Creating your first macro (#create)

Macros beginning with Worksheet_ or Workbook_ are Event macros and are covered on the Event macros page.

 
If these instructions do not help then try Visual Basic for Applications 101 (Excel) « 
Microsoft Excel for Windows -- Visual Basic for Applications 101 [archived copy]

What is a Macro:  A stored series of commands and functions that can be run to perform a specific task or tasks.  In Excel a macro could be a complete program.  Macros may be run from a toolbar button, or menu, from a shape in the workbook, or from another macro.

What is an Event Macro:  Event macros are triggered by an event such as opening a double clicking a cell, right click, opening a workbook, or a activating a worksheet.  (Event macros)

What is a Function:  Excel has builtin functions like IF, AND, OR, SUM which you use in your worksheet.  Functions can invoke other functions to return a value.

What is a User Defined Function:  You can make up your own functions or use functions made up by someone else, either or both would be User Defined Functions (UDF), because they are not built into Excel (not built-in).

Your personal.xls file (personal.xlsm in Excel 2007) is where you store your own macros and User Defined Functions (UDF), that you want to be available from any workbook.  For macros you only want available in a single workbook you would install macros in that workbook.

Recording a macro:  If you want to program something, or issue a series of commands more or less simulating what you do one at a time at the keyboard you can create a macro.  The usual way to start into macros is to record a macro.  Macros in Excel are written in VBA (Visual Basic for Applications).  Macros are sometimes made into Addins or Addins may be programmed in another language such as C++, but we'll skip over addins.

Recording a macro in Excel creates a series of actions in VBA, to duplicate what you enter -- it does not record individual keystrokes.

Macros and UDF can be stored in your workbook or in another workbook.  Personal.xls (personal.xlsm in Excel 2007) is just another workbook, but it is commonly used and described for the macros that you want available to more than one workbook for your own use.  The personal.xls (personal.xlsm in Excel 2007) is normally started when you bring up Excel.  When you start up Excel all files are opened that are in your XLSTART directory, so you don't want other workbooks or files in that directory.  (remember that) This also applies to your Alternate Startup Directory under Tools, options, general which for most should have no file designated. You can find your XLSTART directory using FIND it would be in the same directory as Excel is invoked from.  You won't have one at this point, and expect that the following will create one for you.

To make sure that there is a DIM statement for each variable and that the usage conforms to the definitions it is imperative that you use Option Explicit which in included at the top of modules in Excel 2000 by default.  (VBE Tools, Editor, [x] Require Variable Definition).  Be sure to have this option on to help keep you out of serious trouble.  There are additional hints on my Proper page mentioned later on.

Modification of instructions for Excel 2007 (#xl2007)(#tools)

Tools menu becomes Developer Tab in Excel 2007, and is not shown by default.  To display the Developer Tab, click on the Office 2007 button in upper left corner, then on options, and select Developer.  You can use the same shortcut keys in Excel 2007 as you used in previous versions of Excel.

Tools menu, Options becomes Office Button in upper left corner of Excel 2007, then click on options (at bottom), and then guess at which item on the left to use, for instance what was Tools (menu), Options (submenu), View (tab) becomes Office(button), Advanced (button), then Display heading instead of View.

Modification of Excel 2007 that I had in prior versions.

  Tools, Options, Custom lists -- Office 2007 button, Options, Custom Lists
-- S,M,L,XL,XXL
-- A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z

Additions of toolbar buttons, and Menus will have to be figured out later, also will have to check out Change the ribbon in Excel 2007 by Ron de Bruin.

Security Level set to Medium to run macros (#security)

Macros will not run if the security setting is set to High in Excel, Check under Tools, Macro, security, set to Medium.

Record a Macro (#record)

If you don't know if you have a personal.xls (personal.xlsm in Excel 2007) or not you can let Excel record a macro in your personal.xls (personal.xlsm in Excel 2007) and if you don't have one then Excel will create a personal.xls (personal.xlsm in Excel 2007) workbook for you in the correct directory, which is in the XLSTART directory.  The XLSTART directory is in the same directory as you are running excel.exe from.

Let's record a macro.  Hopefully you are using XL97 or later because there would be some minor modification otherwise.  (would be simpler)

Use someone else's macro (#havemacro)

Instead of recording your own macro, install a macro from a newsgroup, or other source such as a web page.

Bring up the Visual Basic Editor (VBE) standard with normal layout of windows:  Project window (left), Code window (right), Immediate window at the bottom.  [Picture Alt+F11 -- Project view"]

Within the Project Explorer select your project, which is your workbook name.  You will install the provided macro in your workbook, or if you want the macro to always be accessible you would install in your personal.xls (personal.xlsm in Excel 2007) workbook.

If you do not see modules in your project (workbook) which you've selected, then invoke the Insert (menu), and then module [picture], the first one created in a project would be module1.  You can put several macros in a module or create a new module for some addition macros.  You can rename a module with help of F4.

Select the module with a double click on the module name (i.e. module1)

Something to keep in mind when you use a macro is that you can not undo use of macro with Ctrl+Z.

Running the Macro you just Installed (#run)

Now run the macro that you just created or installed from the Excel Window.
The F8 -- macro list does not show macros that have arguments, nor will you see User Defined Functions.  For instance the macro "InsertRowsAndFillFormulas" described in "Insert a Row using a Macro to maintain formulas" is called by another macro, "InsertRowsAndFillFormulas_caller", that does not have any arguments.  [See code] [Pictures Alt+F8 -- Macro List, Alt+F11 -- Project view"].  Alternate methods of invoking a macro from a button or a menu can be seen in Toolbars, Custom Buttons and Menus which is NOT readily available in Excel 2007. 

If the macro does not run (#willnotrun)

Installing a Macro into your Personal.xls   (#install)

This topic may be somewhat redundant as it has pretty much been covered in previous topics, but was written specifically concerning the personal.xls (personal.xlsm in Excel 2007) project workbook.

Now do the what you may have already done in record a macro, but this time when you choose a name


Now test the macro you created in personal.xls (personal.xlsm in Excel 2007), restore the coloring you changed first (format, cells, pattern, automatic color).

Now that you have a macro, switch over to the Visual Basic Editor(VBE) Alt+F11  you can use Alt+F11 to get back, but you may find it easier to just select the workbook with the cursor later.  Anyway in the VBE look for your macro under personal.xls (personal.xlsm in Excel 2007), modules -- if you do not see the listing use  Ctrl+R,  when you click on module1 it will bring up a code window as would F7(view, code) and you will see your Macro1 there.  To help distribute your code so that you would have all of your code open at one time Excel will start a new module when you record your first macro for the day.  You probably want to put related code together that will be open at the same time.

Now it's your turn, suggest you create a user defined function, to go into your personal.xls (personal.xlsm in Excel 2007) for that suggest you take a look at a my Install Macros and User Defined Functions page.

BTW, some of this is actually in HELP, that's where I found it when I was told I should use a macro.  But then that was Excel 95, which was probably a bit easier to get started in.

User Defined Functions (#UDF)

User Defined Functions are installed into the same files as Macros.   Macros begin with SUB, and User Defined Functions begin with FUNCTION.  Another distinction is that you will not find UDF in the macro list (Alt+F8), but you will find them in the Function Wizard list from the address bar button [fx], or from the insert menu (then [fx] function...).
 

Unlike macros you will need to include the project library (i.e. personal.xls!) if not in the same workbook unless you set up references or make if part of an addin.  Otherwise, you must include personal.xls (personal.xlsm in Excel 2007)when invoking the function:
  =personal.xls!GetFormula(D4) or =personal.xlsm!GetFormula(D4) in Excel 2007)

Favorite User Defined Functions (#favorites)

I think you will find the UDFs on my page Show FORMULA of another cell in Excel very useful.  Particularly GetFormula to show the formula or constant used in another cell and a comparable function GetFormat to show the format used in another cell.

Usage Example for a User Defined Function:
  =GetFormula(D4)
  =personal.xls!GetFormula(D4)   or   =personal.xlsm!GetFormula(D4) in Excel 2007

This page also includes information on calling a macro from another macro, and examples of cell formatting.  These are my favorites because GetFormula was the first UDF that I wrote, and I received help from the newsgroups.  The page is fully documented.

Favorite Macros

I think you will find the macros on my page Proper, and other Text changes -- Use of SpecialCells very useful, but more important the page contains useful information (documentation) on what you should have in a macro for efficiency and should look for in macros offered to you as complete.  Another page of my favorite macros is my page on Reformatting with the Join macro being the favorite on the page, other macro on the page that help create test data are among my favorites, another favorite page is Build Table of Contents which has several macros for working with hyperlinks.  On of the most used macros appears to the TrimAll macro on the join.htm page kind of a necessity when you copy and paste from HTML and your data contains   (non-breaking space characters).

Usage Examples on that page:
For the macros on that page you make a selection of cells and then run the macro, and the macro only applies to the selected cells.

If you look at what I have in my toolbars, you can probably get a better idea of some of my favorites. toolbars.htm and how my toolbar menus are structured in bar hopper.

More on Getting Started (#help)

The HELP in Excel is different form the HELP in the VBE. You invoke the HELP you need from where you use it. Hope that makes sense.  Would suggest you read HELP in the contents area first few sections anyway because that is where the orientation information is found.

Your Turn

Now it's really your turn.

Additional Comments:

Running Efficiently   (#content)

Even though you can have any number of macros in a module you want to put macros and functions together that are likely to be needed at the same session together, as once the module is opened it will use more memory.  Default module names are module1 to modulen  and when you record a macro they are somewhat separated by putting each days recordings into a separate module.  You can rename a module with F4 (view, properties).

In the Visual Basic Editor you should close the modules before closing your VBE session, because when Excel is reopened modules that were open will be reopened. 

Writing Efficient Code   (#coding)

Whether you write your own code or use someone else's, you want to make sure that you are using sound and efficient coding.  Some tips on writing macros can be found in Proper, and other Text changes -- Use of SpecialCells, and in Slow Response and Memory Problems.

If you are providing macro examples, please indent your code so that it is readable.  If you are posting code to a newsgroup try to break lines yourself, rather than expecting someone to fix coding based on syntax errors.

Finding macros to do what you want (#archives)

Finding a macro that someone else wrote saves a lot of time, and if you are just starting tends to avoid making serious mistakes.  Suggest including the word "SUB" when searching the Excel newsgroups.  to help in your search for results as opposed to questions.  Some people do not post the full macro but so many questions have already been answered over and over again that missing a few isn't likely to impair your search.

Searching Excel newsgroups -- Google Advanced Group search:
  http://groups.google.com/advanced_group_search?q=group:*Excel*
  be sure to look at the entire thread on anything that looks promising.  [xlnews.htm],

Searching the web with Google Advanced Web Search:
  http://www.google.com/advanced_search?lr=lang_en
When searching the web include both the word SUB (for macros)  and Excel. [xlnews.htm]
You might want to remove junk websites using Customize Google in Firefox to eliminate search hits that really don't have web type content by excluding those that contain newsgroup postings.  A less reliable way is to exclude some words associated with newsgroup postings:  -HTH and  -"posted by" in your Google web search (note the negative signs in front to exclude items).

Creating a Module Name with a specific name, or Rename an existing Module (#modulename)

An example of a module of macros that you might want to install would be my Formula macros described in Show FORMULA or FORMAT of another cell, all of the code for which is found in my code folder as /code/formula.txt
  1. In VBA (Alt+F11) First create a new Module {Insert, New Module} (warning choose 'Module' not 'Class Module') -- suggest you do this in your personal library rather than your project library.
  2. Rename the "Module nnn" created by selecting Module nnn, if not already selected, then get to get to View, Properties using F4 {View, Properties Window} and change the module name to "McRitchie_Formula".
  3. Paste in the code for this new module from /code/formula.txt

Asking questions -- newsgroups (#questions)

Questions concerning Excel VBA Macros are best asked in the microsoft.public.excel.programming newsgroup after first searching newsgroup archives as described in the previous topic to see if you can find an answer or at least to get additional information that might help you and others with solving your problem with a better phrased question.  Then you should be in pretty good shape to post to the programming newsgroup.

You should at least know how to install a macro before posting a programming question.  Hopefully, this page helped with that, if you didn't already know.

Questions not involving with programming should be asked in a different newsgroup see list of Excel newsgroups.

Terminology   (#terminology)

cell.EntireRow.Delete
  cell is the object
  EntireRow is a property of that object
  Delete is a method that operates on the object — (verb, command, action)

Problems   (#problems)

Macros will not run if the Excel security setting is set to High, in Excel check under Tools, Macro, security, set to Medium.  Do not set to low as you would not get the warning that macros exist (macros can expose you viruses).

Many problems involve not hiding the personal macro workbook before attempting to run macros.  Your personal macro workbook should be in your XLSTART directory and if you let Excel create it for you, it would have been created in the correct place.  In Windows Vista the XLSTART directory is in a completely different location than earlier versions of Windows.

From Excel try Alt+F8, look for your macro in the list, if not there you did not install the macro in the correct place or the workbook that you installed it in (personal.xls /personal.xlsm)) is not open.  An exception to this are private macros which have to be called by name and are not seen in the list.  Any macro with parameters is by definition private, and the parameters even if optional can only be used when called from another macro.

If your macro is in the list, * try invoking the macro from there with the Run button on right that might show up a problem such as being in Break mode due to a previous macro failure.  If you are in break mode, fix the error and press the square Rest button in the VBE (Visual Basic Editor). 

If your assigned shortcut key does nothing, it could be because you are in break mode, and you would see that if you tried to invoke from Alt+F8 macro list.  From the macro list, press the options button, you should see your assigned shortcut there.

On the Excel Window menu, make sure that your personal.xls (or personal.xlsm) workbook is hidden.  The reason for hiding is so that you don't have to specify the workbook name with the macro.  With a shortcut key assigned it really wouldn't matter because the workbook name was included when you created the shortcut.  If you unhide your personal macros, don't forget to hide it again.

Those with Excel XP (2002)may run into a situation where Excel thinks a workbook is bad and quarantines it.  Look in HELP (F1) for “Disabled items” (Dave Peterson, programming, 2005-04-28)

If the macro does not run see topic under Running the Macro you Just Installed.

Loss of the Stop Recording toolbar:  * You should use the black button to Stop recording a macro.  Clicking on the Exit button will turn off the Recording toolbar so you will no longer have a choice between turning “Relative Reference” on or off because the only place you can change is on the Recording toolbar.  To restore the toolbar:  Start recording a macro, then View, toolbars, toolbars (tab), (check) Stop Recording.    The relative button allows you to switch at any time between the two while recoding a macro example of the difference in coding:
    ActiveCell.Offset(-5, 5).Range("A1").Select
    Range("G31").Select
 
Macro won't run, summary of things to check (#wontrun)

  1. Security is set to high in Excel (Change it to Medium)
  2. Macros are disabled by choice when workbook was opened
  3. Another macro is already running and you are in break mode
  4. Edit Mode in Excel (menus would be grayed out, hit Enter)
  5. Dialog box open in Excel or in the VBE (complete the dialog)
  6. Workbook is disabled because Excel thinks it is bad (Excel 2002)
  7. Sheet tab is being renamed
  8. Workbook is protected
  9. Chart sheet is active from F11 (macro menu is actually missing)
  10. Menu or customize is active (Alt or / has been pressed)
  11. The task pane is active (actually I can't duplicate problem at moment)
  12. also see grayed out.
  13. Caps Lock On will interfere with shortcut keys assigned to a macro, preventing invocation of the macro, or of the correct macro. (not listed above)

User Defined Function does not work   (#problems_udf)

Most of the above that apply to macros also apply to user defined functions as well, but you will not see User Defined Functions listed in the macro list (Alt+F8), look for them instead in functions list [fx] and choose "user defined" on the left side.

Check that you have calculation on automatic, that you are not in formula view, and that you that you did not enter your formula into a text formatted cell.  If you see #NAME! then your function is not found possibly misnamed.

Startup Problems (#startup)

Excel 2007

Help with and transitioning between Office Versions