Using named ranges and worksheet functions in Excel VBA

Bold Brackets

A couple of days ago I saw something in an article on StackOverflow, that blew my mind. I can’t find the article anymore, but I do remember what this one neat trick was, that will for ever change your VBA. It’s called a named range, and I found out I had always been doing it wrong. So have you, most likely.

Have you ever written something like this?


s = Application.WorksheetFunction.Sum(Range("A1:A10"))

You thought you were quite smart, using SUM to add some values together that would have taken a loop in VBA. You petted yourself on the back, took a beer, and applauded yourself for you being awesome. Well, you’re not awesome. This is lame. You suck. Ok, maybe not, but watch this:


s = [Sum(A1:A10)]

It’s incredible! This gives the exact same result.  You may wish to prepend with a sheet name, so it’s an exact reference. You can use any kind of name inside the square brackets. So, if A1:A10 is named ‘records’ in Excel you could rewrite this code to


s = [Sum(records)]

Note that with this notation we don’t use double quotes around the name of the range.

Vanishing Variables

CaptureA quite mighty use for this, is that we can now write code with a lot less variables, if we let Excel do the work for us.

  1. make a new sheet
  2. name it ‘variables’
  3. make three columns: name, value, description

Now you can fill the table you just made with all kinds of settings, constants or calculations that you want to use in your elaborate VBA program.

I am a lazy teacher, and I have a lot of students, so I work a lot with short macros that can help me get more spare time. Here’s an example:

    For Each subfolder In FSfolder.SubFolders
        If subfolder Like "Student *" Then
            [studentNr] = Right(subfolder, 6)
            If Not FileExists([sClass]) Then
                FS.createFolder [sClass]
            End If
            FS.MoveFolder subfolder, [newFolder]
        End If
    Next subfolder

In line 3 I fill the named range ‘studentNr’ with a value taken from a folder, which contains a student number. In the next line, I check if a folder for that students’ class has already been made, and if not, I make the folder. The variable [sClass] does not exist in my code. It only exists in my variable table in Excel. The cell contains a VLookup function to find in which class this student is currently enrolled. Similarly I have a [Teacher] variable, also with a VLookup function. NewFolder is simply a concatenation and formatting, which is also easily done in Excel.

As you can see, the values for [sClass], [Teacher] and [newFolder] are filled automatically by Excel, and I don’t have to process anything.

Programming like this in Excel is a new paradigm. You don’t churn out all your code top-to-bottom as you used to. You create sheets with lots of calculations, lookups etc, and then you make a tiny program that links all this together. Excel can do some crazy fast, complex stuff, and you should never have to program those anymore!

Crazy Caveats

Well wasn’t that incredible? You may not be used to programming like this, and I recommend this method only for experts. You should be in full control of the worksheets, or otherwise someone will mess with your program. Also, when you are part of a team, you should make sure this ‘magic’ is elaborately documented in the code (e.g. in a header of the function mention which Excel named ranges are used).

Good luck.

Learn more: FastExcel Blog

 

Advertisements