BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Excel VBA Macros - Free On-Line VBA training
- What is Excel VBA, and how can you use it?
- Starting to Record Macros in Excel VBA
- Recording your Macro and Finishing
- Tidying up your macro that has been recorded (this blog)
- Playing back your macro recorded in Excel VBA
Posted by Andy Brown on 30 June 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Tidying up your macro
Alexander Pope wrote:
drink deep, or taste not the Pierian spring:
there shallow draughts intoxicate the brain,
and drinking largely sobers us again.
Finding your Macro
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.
Tidying up the code
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!
Saving your Changes
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 ...