562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to record macros in Excel Visual Basic
Part four of a six-part series of blogs
If you're not sure what the Excel Visual Basic for Applications language is (or how it can help you) this blog attempts to explain! Learn what VBA is, and how you can record macros and then replay them again and again to automate work in Excel.
Once you've started recording a macro, Excel (or the Bob/Bobette inside your computer) will transcribe everything you do into Visual Basic, until you stop recording. For this reason, you should proceed carefully!
For our example, here's what you should do.
Remembering that you are recording, you should follow these steps exactly to make your recorded macro do what you want:
First, click on the first cell to be copied (C4 on the Input Form worksheet), and copy it:
Right-click on each cell to be copied, and choose to copy it using the short-cut menu (as here) or any other way
Now go to the top of the list of names in the Results sheet:
Select the top "person" in the list of names
You now want to select the first non-blank cell in the list. To do this, you want to record the instructions:
Go down to the bottom of the block; then
Go down one cell further
rather than the instructions:
Go to cell B8 (which will always take you back to the same cell, whenever you run the macro)
You therefore need to switch from absolute to relative recording.
To change the way Excel records macros:
Choose the Developer tab on the ribbon (if you can't see this, see below).
Choose to use relative references.
If you can't see the Developer tab, see below
In Excel 2003, you can accomplish the same thing by clicking on the tool shown (this only appears once you have started recording).
If you can't see the Developer tab on your ribbon, you need to enable it. If you're using Excel 2007, first choose to show Excel options:
You can now show the Developer tab in the ribbon:
Tick the box shown to display the Developer tab on your Excel ribbon
Switching between absolute and relative recording is one of the few things for which Excel will not record commands.
You can now paste in the name you've copied:
Go to the top of the column of names
Press CTRL + the down arrow to go to the bottom of the block, then press the down arrow again to go to the first blank cell
If you patiently repeat the steps above, you will eventually build up a row of copied data:
The results of copying and pasting - the right data, but with the wrong formatting
You can now format the cells using the standard formatting tools so that:
the first cell is in a larger font;
the other cells are vertically aligned centrally; and
the whole row has an outline border.
Here's what the final result could look like:
The final result, correctly formatted
You can now stop recording!
To stop recording, click on the tool shown below (in Excel 2003 this tool is on its own toolbar, which appears when you start recording).
Click on the button shown to stop recording
You should now have a macro which copies a contestant's score to the results sheet. It's time now to tidy it up!
You could omit this last stage, but it's likely that you made several mistakes in the recording process which you'll need to rectify, and it can't do any harm to check what you've done!
|Parts of this blog|
25 Aytoun Street