Recording macros in Excel Visual Basic - VBA Macros
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.

  1. Excel VBA Macros - Free On-Line VBA training
  2. What is Excel VBA, and how can you use it?
  3. Starting to Record Macros in Excel VBA
  4. Recording your Macro and Finishing
  5. Tidying up your macro that has been recorded (this blog)
  6. Playing back your macro recorded in Excel VBA

This blog is part of our Excel macros online tutorial series - complemented, of course, by Wise Owl's excellent training courses in VBA and Excel for businesses like yours!

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:

A little learning is a dangerous thing;
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:

Finding the macro you have recorded

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:

Getting rid of 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

Range("C4").Select

Selection.Copy

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

Sheets("Results").Select

'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)

Range("B4").Select

Selection.End(xlDown).Select

'go one cell further (to first blank cell)

ActiveCell.Offset(1, 0).Range("A1").Select

'now paste in contents of clipboard

ActiveSheet.Paste

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

ActiveCell.Offset(0, -3).Range("A1:D1").Select

'remove any background colour

With Selection.Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

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)

With Selection

' .HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

' .WrapText = False

' .Orientation = 0

' .AddIndent = False

' .IndentLevel = 0

' .ShrinkToFit = False

' .ReadingOrder = xlContext

' .MergeCells = False

End With

The code to set borders carefully considers every possible part of a cell's borders:

Diagram of borders round cell

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 ...

 

This blog has 0 threads Add post