Speed-write VBA code by recording a macro

Sometimes the fastest and easiest way to write a tricky bit of VBA code is to have Excel do it for you, by recording a macro, then tweaking the created code.

For example, in our article about VBA’s getElementsByTagName() method, we wrote a subroutine to automate scraping data from a table on a page by pulling content from the child elements of <tr> tags…

VBA project example
VBA project example

Now let’s say we want to sort the data and make the headings bold. I think the VBA code to make text bold is something like Range(“A1”).Font.Bold, but honestly I don’t remember the exact syntax so I’d usually just take a guess or two then turn to Google to jog my memory. Same with sorting — I’d definitely need some help with that one.

But an easier way, especially for the harder stuff, is to simply record what you want to happen in a macro, then steal the VBA code that Excel created dynamcally during recording.

Let’s try it…

Make sure Excel’s Developer tab is showing and click the Record Macro button…

'Record Macro' button in Dev tab
‘Record Macro’ button in Excel Developer tab

Give your new macro a name…

Name your new Excel macro
Name your new Excel macro

Perform the actions you want to capture code for (in this case, sorting columns)…

Sorting Excel data in 4 clicks
Sorting Excel data in 4 clicks

Click the Stop Recording macro button…

'Stop Recording' macro button in Excel's Dev tab
‘Stop Recording’ macro button in Excel Developer tab

Click the newly-created module in the Visual Basic Editor. We can see a new VBA subroutine was automatically created by Excel when we recorded our macro…

New macro code dynamically-created by Excel in new module
New macro code dynamically-created by Excel in new module

Cut & paste the above macro code into your module, then call it by name in your Subroutine…

Cut & paste macro code to your module
Cut & paste macro code to your module

Now for making the column headings bold … you give that one a try!

To download this Excel VBA project: getelementsbytagname2.xlsm

Facebooktwittergoogle_plusredditlinkedintumblrFacebooktwittergoogle_plusredditlinkedintumblr