564 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 five 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.
Alexander Pope wrote:
You can't edit a macro until you can find it. To do this, press ALT + F11 to go into the VBA code editor (this opens up a separate application). You should then find your macro:
Expand the workbook into which you recorded your macro (here the personal macro workbook), then expand the Modules category. You can then double-click on Module1.
A module is the name given to the place in which VBA programming code is stored. Think of it as a blank sheet of paper into which Excel writes instructions in its own special language.
Here are some ideas for how to simplify your code! Note that your macro won't look exactly the same as this one, but you should be able to get ideas from what follows. Don't worry about making changes - you can add, delete and edit lines in the code window as you see fit, to change the way that your macro behaves.
Perhaps a good start is to remove unnecessary comments:
Comments are the lines which begin with an apostrophe - ' - and are in green. The ones shown selected add no value, and can be deleted!
The next bit copies a contestant's name - I've added a comment before it:
'copy the contestant's name
The next thing is to go to the first blank cell on the Results sheet, and paste (again, I've added comments):
'go to the results sheet
'select the top of the list of names, and
'from there go down to the bottom (note that we switched from
'absolute to relative recording mode while recording this)
'go one cell further (to first blank cell)
'now paste in contents of clipboard
When the macro has finished copying and pasting, it's time to look at the formatting. Firstly, my macro removes any background colour from the pasted cells:
'clear the "dancing ants" effect - we've finished copying
Application.CutCopyMode = False
'select the whole row to be formatted
'remove any background colour
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
The code to change the font size of the left-hand cell is a fair bit longer than it needs to be:
'if you change the font size, Excel records a command
'for every part of the FORMAT CELLS dialog box
'all we need is ...
Selection.Font.Name = "Calibri"
'the original code is left in here for reference
' With Selection.Font
' .Name = "Calibri"
' .Size = 14
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .Underline = xlUnderlineStyleNone
' .ColorIndex = xlAutomatic
' .TintAndShade = 0
' .ThemeFont = xlThemeFontMinor
' End With
The code to set vertical alignment likewise reproduces every part of the Alignment tab of the FORMAT CELLS dialog box:
'change the vertical alignment to centred
'(all unnecessary lines commented out)
' .HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
The code to set borders carefully considers every possible part of a cell's borders:
When setting the borders for a cell, you can set:
Based on this information, you should be able to simplify these lines of code!
Excel will automatically save any changes you've made to your personal macro workbook when you exit the application (or you could press CTRL + S or click on the usual disk icon to save your changes).
It's now time to run your macro to see what it does! There are several ways to do this ...
|Parts of this blog|
25 Aytoun Street