Category: VBA Training

Excel VBA is used for all sorts (!) of data manipulation tasks. One of the most common is sorting your data. Performing an Excel VBA sort seems trivial but some parts of it may misbehave if you’re not diligent.

I’ve seen some people try to write their own sort routine in Excel VBA, but frankly Microsoft has spent thousands (millions?) of dollars refining their Excel Sort technique, so why not just record a macro where you use that?

It starts simply enough. View > Macros > Record new macro, fill in the details, hit Sort then Stop Recording.

Play it back – seems fine.

Add some new rows, play it again – still fine.

But it isn’t.

The problem comes when you look at what has been sorted. If you notice, your macro has only worked on the same range as your original recorded macro. Extra data outside of that first selection will not be included.

A quick peek in the Visual Basic Editor (ALT + F11) will demonstrate this clearly.

Boom, there it is. A reference to a fixed range of cells (A2:H31). Aside from the other typically overcautious lines from the macro recorder (thank goodness the xlPinYin method was set!) these cell references are the bits that will cause you problems. It’s the worst kind of error too, the one that doesn’t cause an error but silently causes havoc in the background.

The key is then to create a dynamic range. This is the source of many issues with beginner code in VBA. As with most VBA problems, there are many ways to solve it. Try this out:

Sub MySortMacro()

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

End Sub

Let’s take a look at that.

    Dim LastRow As Long

Create a new variable called LastRow as a Long datatype (an integer that goes roughly up to 2 billion). Why a Long and not an Integer? An Integer goes up to ~36,000. If your data is 36,000+ rows, you will run into the overflow error. 2 billion is plenty!

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Count how many rows are in the first column (,1). Go to that last cell and do a CTRL + up arrow motion. In most sets of data this will hit the last item of data in that column. Get the row number then store that in the LastRow variable.

    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

This heavily truncated command concatenates (or ‘joins’) the LastRow variable to the ranges required. The “A2:H” bit is the full range of data, “C3:C” is which column you want to sort by.

There you go. Simpler code, easier to read and will work with any range of data.

VLOOKUP is one of the most useful and versatile functions in Excel. As you work further with macros it’s not uncommon to make your create an Excel VBA VLOOKUP macro. With this you get the ability to reference your tables of data, but automated.

Wait, what’s a VLOOKUP function?

The Vertical Lookup is one of Excel’s most popular commands. It’s most common use allows you retrieve data from another table of data based on a key value. For example, in one Excel sheet you may have a list of one customer’s invoice numbers, and in another sheet a list of all your invoice numbers plus other columns, such as amount, customer and invoice date. A VLOOKUP function can use the invoice number as a reference point to extract one or more other related columns of data. This avoids sloppy copy-and-paste and ensures the data remains up to date.

There are other smart uses of the VLOOKUP, such as being able to search for duplicates, group values into buckets and check to see if items exists but this is enough detail for now.

For a more thorough discussion of the VLOOKUP function, check out our article here. Even better, come on one of our Excel Advanced courses!

So what about using Excel VBA VLOOKUPs?

You can retrieve data from sheet to sheet programmatically using VBA alone, usually with nested FOR NEXT loops and variables to track your current cell position. These can be a bit fiddly and the learning curve can be a little steep (if you want to learn how to do this check out our Excel VBA Introduction / Intermediate course).

Luckily, VBA provides you with the Application.WorksheetFunction method which allows you to implement any Excel function from within your macro code.

So if your original VLOOKUP in cell B2 was something like this:

=VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)

The VBA version would look like this:

Range("B2") = Application.WorksheetFunction.VLookup(Sheets("Input").Range("A2"), Sheets("Data").Range("A1:X200"), 5, False)

Notice a couple of things:  I had to insert the Sheets and Range objects so VBA could properly interpret it.

I want a sneakier version!

Don’t want to do that Sheets and Ranges business? You can adopt a little-known punctuation trick in VBA that converts things into cell references: the square brackets. Did you know you can turn this:

Range("A1") = "Fred"

into

[A1] = "Fred"

With a little lateral thinking we can do the same to our VLOOKUP:

[B2] = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

Make it more robust

The code lines above will do the bare minimum. They’ll get the job done. What if you grab the result of the VLOOKUP and store it in a variable?

result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

And then throw an unexpected value at it?

Let’s add a little error-handling so your code doesn’t come to a screeching halt. There’s a number of ways you can test this, and various things you can do with the error, so here’s only one suggestion:

On Error GoTo MyErrorHandler:

  result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

MyErrorHandler:
  If Err.Number = 1004 Then
    MsgBox "Value not found"
  End If

Or possibly

On Error GoTo MyErrorHandler:

  result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

MyErrorHandler:
  If Err.Number = 1004 Then
    result = ""
  End If

The first example throws the error right up in your face. The second one is a ‘silent’ error that pushes a null string into the result variable. Not usually advisable as you can’t actually spot the problem but on some occasions you just want to move past the code issue. You could replace the empty string with your own custom error message.

Make it dynamic

This is all well and good but what if your data grows and shrinks? You should be on the lookout for dynamic methods. You can consider things such as dynamic range names and similar, but here’s a VBA option you can consider:

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim TargetRange As Range

    On Error GoTo MyErrorHandler:

    Set ws = Sheets("Data")

    LastRow = ws.Cells(Rows.Count, "X").End(xlUp).Row
    Set TargetRange = ws.Range("A1:X" & LastRow)

    result = Application.WorksheetFunction.VLookup(Sheets("Input").Range("A2"), TargetRange, 5, False)

    MsgBox result

MyErrorHandler:
    If Err.Number = 1004 Then
      MsgBox "Value not found"
    End If

Note I went back to standard sheet and range references; they’re tricky to mix and match with square bracket notation.

What’s happening here? We set up three variables, to house the sheet name, last row and final range. Then we calculate what the last used row is with the “xlUp” method. This is equivalent to pressing CTRL + up on the keyboard when working in Excel. This finds the last row on the worksheet, and finally we set this as the range used.

There’s lots of variations on this, depending on whether you need dynamic columns too and how regular your data is, but this is a great method for getting you started.

So there it is, from soup to nuts, ways to implement VLOOKUP functions in Excel VBA.