|
© Matti Mattila, CPFA, CISA, CIA |
|
|
|
Making a macro in MS Excel resembles much making a macro in IDEA.
First record key strokes and save the macro.
Then, if needed, edit the code and add new code in MS Excel's Visual Basic Editor (VBE).
|
 |
| Picture 1 |
|
|
| Recording
|
|
| - |
Start recording key strokes by selecting: "Tools - Macro - Record New Macro".
|
| - |
Give the new macro a name and a description (picture 1).
It might be useful to give the macro a short cut, too,
for starting run of the macro by pressing certain keys.
|
| - |
Make the key strokes needed in accomplishment of the task or part of it.
|
| - |
To stop recording select: "Tools - Macro - Stop recording".
|
| - |
Save the macro by saving the Excel worksheet
(i.e. selecting "File - Save" or "File - Save as..").
|
| Editing
|
 | |
| Picture 2 |
|
|
| - |
Select: "Tools - Macro" or Alt + F11 when you want to edit the macro.
You are shown list of macros (as in picture 2) or a list of modules and forms (Alt + F11).
Select the one you want to edit.
In picture 2, 'HeaderFooter' being selected, clicking ["Muokkaa" =] "Edit"
would give us possibility to edit it.
|
| - |
Edit the macro (keys strokes = recorded code).
Remove extra lines,
add comments, insert more or less sophisticated dialogs,
and insert code by writing it, or copying it e.g. from Excel VBA examples or from previous macros.
|
| - |
|
A macro is saved in a VBA project (picture 3).
A project can consist of a module or more of them, and user form(s).
A macro can use other modules and user forms [of the project].
When making macros you can make use of Excel's functionality and Visual Basic programming.
|
 |
| Picture 3 |
|
|
|
|