98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article VBA articles
VBA: It's Easy To Record Macros In Excel 2007
Wed 29th December 2010
The reason that our voices sound so different on recordings from what our own perceptions are is that when we speak we hear the sounds and feel the vibrations inside our heads as well as hearing what comes our of our mouths. A recording instrument only picks up what comes out of our mouths and so we often sound more high-pitched and quite unfamiliar. You can see how this works by performing this simple experiment. Stretch a rubber band between your fingers and twang it. Now hold one end between your teeth and the other in your fingers and twang it. Just as our voices sound deeper to our own ears, the sound of the latter twang is far fuller than the first one. Anyway, now that the science lesson is over, back onto the article.
The reason I chose the camcorder introduction is to establish that we are all familiar with the concept of recording. Right across the board from a Hollywood director shouting 'Action' to me as a youth illicitly recording songs from the radio onto cassette tape, the process is exactly the same; you press a button to start recording and you press another button to stop recording.
So if you are familiar with that concept then you are already well on your way to being able to record a macro using VBA, as the following simple demonstration shows.
Let us assume that you compile an Excel worksheet every evening that displays the day's racing results. It would save you a lot of time if you were to create a simple macro that would automatically insert the numbers 1, 2 and 3 into a column by means of a keyboard shortcut. This is a simple task that is no more difficult than recording on a camcorder or a voice recorder.
To do this you must first access the Developer tab. This is not shown on the ribbon and so to access it click on the Office button and select Options. In the dialog box that appears check the box marked Show Developer tab in the Ribbon and click OK. Your new Developer tab will now appear alongside the others on the ribbon and this is your gateway to creating VBA applications. From the Code segment, click the Record Macro button.
First of all you must give the new macro a name. I would suggest RacingResults as this allows me to make the point that there can be no spaces in the name of a macro, and it must begin with a letter. You need to create a keyboard shortcut to your new macro so in the Shortcut key box enter a letter, say, R for example. Pressing Ctrl+R will now activate your macro. You should store the macro in This Workbook and you could give it a description of Daily Racing Results. When you have done all of this you are ready to click OK - which will start the recording and you will notice that the Record Macro button has changed into a Stop Recording button.
In cell A1 type 1, in A2, 2 and in A3,3, then click the Stop Recording button and it's job done. In order to test the macro delete what you have just typed and, with cell A1 selected, press your shortcut keys of Ctrl+R. If all has gone well the macro will be activated and appear at a single stroke.
So if we assume that this first macro relates to the first, second and third in the 2.15 at Lingfield, then we skip a cell and select A5 for our next race, the 2.45. Simply go to A5 and press your shortcut key and...
Oh dear the macro didn't work but don't worry, there is a perfectly simple explanation. The reason for this apparent failure, which wasn't a failure at all really, but the macro behaving exactly as you instructed it, lies in another button in the Code segment, Use Relative References.
The macro you recorded was hard-coded so that it would only work within the cells in which it was originally recorded, in this case cells A1 to A3. To allow the same macro to run from other cells on the worksheet, you must record a new one.
As before select Record Macro but this time be sure to select the Use Relative References button as well. Now name your new macro, perhaps RacingResultsRelative, and assign a shortcut key to it that is different from your original selection. Store in This Workbook as before and give a description if you so wish. Click OK to start recording and type in your 123 again. Click Stop Recording.
Again, to test the macro, delete what you have just typed and select A1. Press your new shortcut keys and the macro should activate. This is all just as it was in your original attempt and now comes the moment of truth. Select A5 and press your shortcut keys. This time the macro should have activated and the numbers should be sitting pretty on the sheet.
This is a very basic demonstration of how to record macros in Excel but I hope that it shows just how easy it is to do. It is possible to record far more complicated macros but even this is just a tiny part of the huge VBA resource. If this demonstration has made you keen for more then why not embark on a dedicated training course?
Author is a freelance copywriter. For more information on microsoft excel courses london, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-1332-vba-its-easy-record-macros.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsAllergan
BEX Analyst Marijke Rohlfs Power BI Modelling, Visualisation and Publishing Excellent trainer with broad knowledge and teaching skills. Only drawback was the projector which was at times a bit blurry and it was difficult to follow the formulas British Council
Resourcing Lead Matt Dallat Excel Intermediate I felt the course was the right level and well paced and took into account the differing levels of those in the roome. A patient trainer and very knowledgeable. 1000heads
Analyst Sam Marchant Excel Advanced - Formulas & Functions Jens is a great teacher, patient and passionate Would recommend course to colleagues The timings of the day work well, keeps people refreshed and engaged |
PUBLICATION GUIDELINES