Recording macros in VBA – A great way of learning

Sometimes when you are working with some VBA module of yours you wonder how to, for example, bolden the text in a cell or how to add borders to a selection. Well, finding out how to do that is quite simple as Office lets you record macros in Excel (and other programs). When you start a macro recording, everything you do will be recorded in code for you to review later.

This is handy as you can just record a macro with whatever you want to learn and then view the code afterwards. In this example we’ll record a macro where we format a cell to see what text formatting looks like in VBA.

First off, if you don’t have the Developer tab open in your excel instance, go to the Office button in the top-left corner and select Excel Options. In the options window, under the Popular-tab, check the Show Developer tab in ribbon-checkbox and press ok. You should now have an extra tab visible called Developer. If this isn’t working out you may have to check out your security settings.

In the developer tab you will see a small button called Record macro and that’s what we’re gonna use:

The record macro button
The record macro button

When you press that you are shown a prompt where you’ll add the name of your recording and a potential short cut to it. We won’t bother with the short cut this time but give it a pleasing name:

Name your macro
Name your macro

Now select cell A1 and get back to the Home tab. Add the text “Foo” to the cell and make it bold. Add whatever format you want to it actually, it will all be recorded. I made it bold and italic. I also changed the font size. Afterwards, get back to the Developer-tab and press Stop recording which shows in the same spot as where Start recording was shown before.

To view your recorded macro, enter the Editor (by pressing ALT + F11), my recording looked something like this:

Sub MyFirstMacro()
'
' MyFirstMacro Macro
'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Foo"
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    With Selection.Font
        .name = "Calibri"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End Sub

This is quite handy as I can now see how to select a cell, how to make it bold etc. Use this technique every time you wonder how something is done in VBA. Remember to just record exactly what you need to know though, as these scripts tend to grow into enormous size quite quickly.

Formatting cells with VBA

Formatting cells in VBA isn’t that hard and I thought I’d just show an example of how to format 5 cells in a cross form. If you need help creating a module or opening the editor I refer to a previous post on the subject.

The following sub routine will alter five cells in various way which will show some of the formatting you can do in VBA:

Sub AlteringTheSheet()
 
    'Select Sheet1
    Sheets("Sheet1").Select
 
    'Select cell B2
    Range("B2").Select
 
    '==============B2=====================
    'Set value of B2 to "B2"
    Selection.Value = "B2"
 
 
    '==============B1=====================
    'Move up one step
    Selection.Offset(-1, 0).Select
    'Set value of B1 to B1
    Selection.Value = "B1"
 
 
    '==============A2=====================
    'Move one step left and one down
    Selection.Offset(1, -1).Select
    'Set background color to gray
    Selection.Interior.Color = RGB(240, 240, 240)
    Selection.Value = "A2"
 
 
    '==============C2=====================
    'Move two steps to the right
    Selection.Offset(0, 2).Select
    Selection.Font.Bold = True
 
    'Found a list of colorindexes here:
    'http://dmcritchie.mvps.org/excel/colors.htm
    Selection.Font.ColorIndex = 3 '3 being the color of red...
 
    Selection.Font.name = "Arial"
    Selection.Font.Italic = True
    Selection.Value = "C2"
 
 
    '==============B3=====================
    'Move down one step and left one step
    Selection.Offset(1, -1).Select
 
    'Add a border to the cell
    With Selection.Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Value = "B3"
 
End Sub

This will generate a sheet that looks something like this:

The resulting cross
The resulting cross

Posted in VBA

Using collections in VBA

When working in languages such as Java or C#, collections are a very basic part of your toolbox. The reason you want to work with collections rather then arrays is that you want to avoid having to resize your arrays all the time. Arrays are basically only useful as long as you know, in advance, the size of your data collection. If you don’t, you will have to resize it regularly to adapt to the data.
Of course, the resizing will have to be done anyways, but preferably not by you. Most modern languages have built in collection types (List in Java, Collection in VB and ArrayList in C #) and they are easier to use than creating a class of your own. The VBA collection is the same as the VB collection with the syntactical changes that comes with the languages (more info on the changes between the VBA and VB Collection can be found here).

To create a collection of names you write this:

    Dim names As New Collection

This creates a new collection object with which we’ll work and it has 4 methods: Add(), Count(), Item() and Remove(). These ought to be quite simple to figure out so I won’t go any further into them.

Now, I’ll simply show you an example of when I had to use a collection as examples are a perfect way of understanding the use of things.
The issue at hand was an excel sheet with data like this:

Our example data
Our example data

What I want to do is get a list of all unique names of people who have spent money on my site. This is of course a silly example but it will have to do. I only want the people who have actually spent money and I don’t want duplicates. This is a bit tricky to get done using only simple functions so I thought I would do a sub routine in VBA for it. I will show you the code, which is commented, and then make some remarks afterwards:

Sub FindGoodCustomers()
    'Declase variables
    Dim names As New Collection
    Dim nameIndex As Integer
 
    'Select Sheet1
    Sheets("Sheet1").Select
    'Select cell B1
    Range("B2").Select
 
    'Loop through all the cells in column B
    Do Until Selection.Value = ""
 
        'We only care about the people who spent moneh
        If Selection.Offset(0, 1).Value > 0 Then
            'Call our adding routine
            Call AddUniqueToCollection(names, Selection.Value)
        End If
 
        'Move down one step
        Selection.Offset(1, 0).Select
    Loop
 
    '====SWITCH SHEET AND OUTPUT ALL NAMES====
    Sheets("Sheet2").Select
    Range("A1").Select
 
    For nameIndex = 1 To names.Count
        Selection.Value = names.Item(nameIndex)
        Selection.Offset(1, 0).Select
    Next nameIndex
 
End Sub
 
 
 
'A routine to add a name to the collection ONLY
'if it is not already in it
Private Sub AddUniqueToCollection(names As VBA.Collection, name As String)
    Dim nameIndex As Integer
 
    For nameIndex = 1 To names.Count
        If (names.Item(nameIndex) = name) Then
            Exit Sub 'This means the name is already in this sub, so stop it
        End If
    Next nameIndex
 
    'The name was not found, add it to the collection
    names.Add (name)
End Sub

I use two sub routines, FindGoodCustomers and AddUniqueToCollection. The FindGoodCustomers routine is the one we actually call. It will create a collection of names and then loop through the list of names in Sheet 1, column B starting on the second row. We will only bother to add customers who’s actually bought something so we check the column next to the name, which is the column containing how much money they’ve spent, to see if they’ve spent anything at all.
If they have spent money we use our other routine to add them to the list. The AddUniqueToCollection routine just loops through the list making sure the new name is not in it. To stream line it (as far as stream lining could be done in VBA) we stop immediately after we find a match.
After creating the list we output it in Sheet 2, this could of course be done anywhere, even in an external text file or something.

Hopefully this will be useful for somebody somewhere…

Your first VBA module

When using VBA extensivly it is a good idea to know how to use VBA. VBA means Visual Basic for Application and can be used to automate functions that are hard to achieve using simple functions. VBA uses the same syntax as Visual Basic and is quite simple to use.
To write a VBA application you need to open the Visual Basic Editor (VBE) which you do by pressing ALT + F11. Choose Insert => Module in the menu to create a new module.
Enter the following code:

Sub Test()
 
    'Select cell C1
    Range("C1").Select
 
    'Write "Foo" in the selected cell
    Selection.Value = "Foo"
 
End Sub

If you press run (either F5 or press the green triangle icon in the icon tray) you’ll see how the module has written Foo in cell C1. So, you ask, do you have to run modules from inside the VBE? Of course not, that would be dull. If you get back to your excel sheet there are differences to running VBA script depending on which Office pack you are using. I’m using Office 2007 so I will show you how to do it in Office 2007, however, if you are running 2003 or perhaps an even newer version then it shouldn’t be too different.

In Office 2007 press the big office button in the top left corner (where you go if you want to save or open a file) and choose Excel Options in the bottom right of the menu. In the options Popular tab check the “Show Developer tab in the Ribbon“. You might have to change your security settings in the Trust Center to get it to work.

This has now opened the Developer tab and if you go to it you should be able to select your macro by pressing the Macro-icon:

The Macro menu
The Macro menu

If you press Run the script will execute and you’ll see the effect in cell C3. You have now written your first VBA script that does something!