You can automate repetitive tasks in Excel by using ^Rmacros^r.
|
A macro is a series of commands and functions stored on a VBA (Visual Basic for Applications) sheet called a ^Rmodule^r.
|
You can run a macro whenever you need to carry out a task performed by that macro.
|
Before you record a macro, you should plan the steps and commands you want the macro to perform.
|
This is because any mistakes and corrections you make while recording a macro will be stored as steps in the macro.
|
To begin recording a macro, you choose ^UMacro - Record New Macro^u from the ^UTools^u menu.
|
This opens the Record Macro dialog box.
|
You can type a name for the macro at the first field (or you can accept the name suggested by Excel).
|
The first character of a macro name must be a letter. Other characters can be letters, numbers, or underscores. Spaces are not allowed in a macro name.
|
You have the option of creating a shortcut key for the macro by typing a letter at the appropriate field.
|
You can use an uppercase letter as a shortcut key by pressing ^RShift^r and entering a letter at the Shortcut key field.
The shortcut key will override any default Excel shortcut keys while the workbook that contains the macro is open.
|
Excel inserts a default description for the macro, which you can change if you wish to.
|
A drop-down list allows you to choose where to store the macro.
|
All workbooks have access to macros stored in your Personal Macro Workbook.
|
Your Personal Macro Workbook (PERSONAL.XLS) is stored in the Startup folder (\XLSTART) of Excel. It is opened each time Excel is opened.
If you are working on a network, your Personal Macro Workbook probably won't be available to others. In that case you should make sure that all required macros are located in each workbook.
|
You now click ^UOK^u, or press ^UEnter^u, to begin recording your macro.
|
The word "Recording" at the bottom of your screen indicates that any steps you now make will be recorded in the macro.
|
By default, absolute references are recorded for any cells you select while recording the macro.
|
However, if you click the ^URelative^u ^UReference^u button on the Stop Recording toolbar, relative references will be recorded for any cells you select while recording the macro.
|
When you run a macro recorded with absolute references, it will be executed in exactly the same cells as were recorded. However, macros recorded with relative references are portable throughout the workbook.
|
The ^URelative Reference^u button is now depressed, and you can carry out the steps you wish to record.
|
Say, for example, you want to create a macro to insert the name and address of Centurion Technologies Inc., relative to the active cell.
|
First you choose the font size for the company's name.
|
Next you click both the ^UBold^u and ^UItalic^u buttons on the Formatting toolbar.
|
Now you can type ^RCenturion Technologies Inc.^r, then press ^UEnter^u to select cell A2 before typing the company's address.
|
Once you have completed the steps you require, you click the ^UStop Recording^u button to finish recording the macro.
|
The macro has now been recorded and is stored in your Personal Macro Workbook.
|
Notice that the word "Recording" is no longer visible at the bottom of your screen.
@@
Suppose you want to try running your new macro on Sheet2 in your open workbook.
|
Since you recorded the macro named Centurion with relative references, you can choose the active cell to be a cell other than cell A1.
|
If you had recorded the macro with absolute references, you would only be able to run the macro correctly from cell A1 - the active cell when you began recording the macro - on the current sheet.
|
To run a macro that you have created you choose ^UTools - Macro - Macros^u, or press ^UAlt+F8^u.
|
The Macro dialog box lists the names of the macros available in All Open Workbooks, This Workbook, or PERSONAL.XLS - in this case All Open Workbooks is chosen.
|
The ^UOptions^u button allows you to change the description of the selected macro, or the shortcut key assigned to it.
|
You can remove a macro from the list by selecting it and clicking the ^UDelete^u button.
|
Your Personal Macro Workbook is normally hidden. So if you try to delete a macro from PERSONAL.XLS, you may be prompted to unhide the workbook before you can do so.
You can display any hidden workbook using the ^UUnhide^u option in the ^UWindow^u menu.
|
To run the macro you click the ^URun^u button, or press ^UEnter^u.
|
The macro has now been executed (in this case relative to the chosen active cell, B3).
@@
@@
@@
You can edit, or write, macros attached to Excel workbooks using the Visual Basic (VB) Editor.
|
So you should be familiar with the VB Editor in order to do this.
|
For more information see the CBT Systems suite of Visual Basic 4.0 courses.
|
Suppose you record a macro called Months in the current workbook.
|
You do this by typing ^RJanuary^r in cell B3, and then filling right to cell D3.
|
You then select cell E3 before clicking the ^UStop Recording^u button.
|
Suppose you now wish to edit the macro so that it inserts labels for the months April, May, and June in cells B3 to D3, before selecting E3.
|
To edit a macro you choose ^UTools -^u ^UMacro - Macros^u, or press ^UAlt+F8^u.
|
You click ^UEdit^u to make changes to the macro selected from the list.
|
The ^UStep Into^u button in the Macro dialog box provides a debugging facility for the selected macro.
|
This opens the Visual Basic Editor, which shows the VB code for the macro named Months.
|
You can use ^UAlt+F11^u to toggle between Excel and the VB Editor.
This is equivalent to
ò choosing ^UTools - Macro - Visual Basic Editor^u
from Excel
ò choosing ^UView - Microsoft Excel^u from
Microsoft Visual Basic
|
You can now replace the word "January" with the word "April".
|
You can run the macro from the VB module by clicking the ^URun Sub/UserForm^u button.
|
This is the same as choosing ^URun Sub/UserForm^u from the ^URun^u menu, or pressing ^UF5^u.
|
If you choose to run a macro from the VB module, you should first ensure that the active cell is positioned appropriately in the Excel workbook.
|
To close the VB Editor and return to your Excel workbook, you choose ^UFile - Close and Return to Microsoft^u ^UExcel^u.
