This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. How to Record Macros in Excel Visual Basic 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 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!

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.

How to Record Macros in Excel Visual Basic
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

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

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:

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

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:

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

Parts of this blog
How to Record Macros in Excel Visual Basic 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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC