Getting Started with Macros

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

If these instructions do not help then try Visual Basic for Applications 101 (Excel) «
http://support.microsoft.com/support/excel/content/vba101/default.asp

Your personal.xls file 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.

Excel has builtin functions like IF, AND, OR, SUM which you use in your worksheet. 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 the are not built in to Excel.

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.

Macros and UDF can be stored in your workbook or in another workbook. Personal.xls 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 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 this) 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. Let's record a macro. Hopefully your are using XL97 or later because there would be some minor modification otherwise. (would be simpler)

Starting on your menu bar: Tools, Macro, Record Macro
supply a name, in this case accept suggestion of Macro1
anything you do now in Excel will be recorded in the macro.
Select Column B and using the Ctrl key Select D and F
Click on interior color icon looks like a paint bucket, or use Format, Cell, Patterns and choose a light color.
Click on the stop button on the macro dialog box that is over your sheet, or Tools, Macro, Stop Recording

Instead of recording own macro, install a macro from a newsgroup

Put the subroutine in a module by first hitting Alt+F11, which brings up the VBE (Visual Basic Editor).
Use Menu to View--> Project Explorer.
Select your VBAProject (YourFileName.xls).
Use Menu to insert a "Module". Copy the code into the module.
Return to Excel to run your macro. (see below)

Now run the macro that you just created or installed

Alt+F8 (program function key 8)
select Macro1, then hit the [Run] button

If the macro does not run

Macros will not run if the security setting is set to High, Check under Tools, Macro, security, set to Medium. If the security setting is set a High and you try to run macros in the workbook you will see the following message:
The macros in the project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros.
Syntax errors will show up in Red and the macro will not run. This would be a frequent occurence for macros copied from newsgroups where a statement got split between two lines and needs to be rejoined. Fix any syntax error.
In the VBE (Visual Basic Editor, Alt+F11) the Run, Break, and Reset buttons must be active (not grayed out). If not active fix your macro errors and the press the Reset button on the toolbar. The buttons look like VCR buttons Play, Pause, Stop.

Now do the above similarly but this time when you choose a name

accept the suggested Macro1, but go down to the bottom and
choose Personal.xls instead of all workbooks.
Choose different columns and different light color. Stop the macro.

Now test the macro you created in personal.xls

Alt+F8, at the bottom choose Personal.xls then [Run]
Alt+F8, then [Run]
Alt+F8, choose the Macro1 that does not say personal.xls, and the press or hit [Delete]
Alt+F8, type Macro1 into the box, then [Run] note since you do not have a macro1 in your current workbook, it will be run from your personal.xls workbook. Your personal.xls workbook should be hidden so it can be used by other workbooks without specifying where the macro resides. see menu, Hide/Unhide

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, 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 for that suggest you take a look at a my Formulas 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.

More on Getting Started

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

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.

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.

Visual Basic for Applications 101, « starts of with Recording Your First Macro, Running the Recorded Macro, Assigning Macros to Controls, Recording Macros Using Relative Cell Referencing, Avoiding Pitfalls When You Record a Macro, and Where to Store Macros. Use the regular help note the tabs: Contents, Answer Wizard, and Index. Ignore the topics on Microsoft "Office Assistant" one the worst abominations ever produced by Microsoft.
Formula, information on creating using macros and formulas. Contains the User Defined Function, GetFormula which, I think, you will get a lot of use from.
Slow Response, How to improve your response time with more efficient coding.

This page was introduced on June 5, 2001.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]