First bug of the year

And it’s a funny one. Save all your work, then make up a simple datasource that has a formula in the header:

Now, turn the Macro Recorder on, and while it’s recording, turn that datasource into an Excel Table. (I use the Ctrl T shortcut for that)
Create Table
Excel will warn you that the formula will be converted to static values. Click No.

Happens for me using Office 365. Pretty obscure, granted.

What Is the Most Awesome Microsoft Product

As if you didn’t know.

As the people talked, we started to get a sense for why they thought Excel is cool – Excel enables people to be rockstars at their jobs and get recognition from their peers. Excel makes people feel powerful. Excel is a work tool that people *love* to use.

You can also read the rest of the answers. Lots of OneNote and Kinnect. Even a Visio and a Minesweeper.

Finding the Next Row in a ListObject in VBA

ListObjects (Tables in Excel’s UI) are structured ranges. I use them constantly. I love the built-in named ranges and referring to them in VBA without a lot of hullabaloo. It’s as close to a database as you’re going to get in Excel. Recently I decided to automate a process of adding some payroll records to the end of a table. If I were using just a range, I would find the next available row like

Set rStart = wshSalaries.Cells(wshSalaries.Rows.Count,1).End(xlUp).Offset(1,0)

That works most of the time for ListObjects too. It returns the row right below the last row of the ListObject. In most cases, when you add some data to that row, the ListObject expands. In the case where there is no data in the ListObject and there is only a blank row, however, it doesn’t work. The ListObject doesn’t expand, and even if it did, you would have a blank row.

The ListObject object has a InsertRowRange property that returns a Range object. When a ListObject has no data, it has a header row and a blank row[1] ready to accept data.

When you enter something into that row, it doesn’t give you a new insert row, it just sits there.

When I’m trying to write something to the end of a ListObject, I test to see if InsertRowRange is nothing[1]. Here’s a snippet

Set lo = wshSalaries.ListObjects(1)
If lo.InsertRowRange Is Nothing Then
    Set rStart = lo.HeaderRowRange.Cells(1).Offset(lo.ListRows.Count + 1)
    Set rStart = lo.InsertRowRange.Cells(1)
End If

If InsertRowRange is Nothing, then table isn’t empty and I offset down however many rows there are plus one. The old method of End(xlup) works in this situation too. I don’t find top down better or worse than bottom up, so use whatever you like. If InsertRowRange isn’t Nothing, that means there’s no data in the table. In that case, I can insert starting in InsertRowRange.

Here’s the whole procedure, if you’re looking for context.

Public Sub ProcessWageFile()
    Dim clsEmployees As CEmployees
    Dim clsActives As CEmployees
    Dim clsEmployee As CEmployee
    Dim aOutput() As Variant
    Dim lCnt As Long
    Dim lo As ListObject
    Dim rStart As Range
    Set clsEmployees = New CEmployees
    clsEmployees.FillFromRange wshEmployee.ListObjects(1).DataBodyRange
    clsEmployees.FillCompsFromRange ActiveSheet.UsedRange.Offset(1)
    Set clsActives = clsEmployees.FilterByActive(True).FilterByHasComps
    ReDim aOutput(1 To clsActives.Count, 1 To 5)
    For Each clsEmployee In clsActives
        lCnt = lCnt + 1
        aOutput(lCnt, 1) = clsEmployee.FullName
        aOutput(lCnt, 2) = clsEmployee.Comps.Period
        aOutput(lCnt, 3) = clsEmployee.Comps.TotalWages
        aOutput(lCnt, 4) = clsEmployee.TotalBenes
        aOutput(lCnt, 5) = clsEmployee.Comps.TotalTaxes
    Next clsEmployee
    Set lo = wshSalaries.ListObjects(1)
    If lo.InsertRowRange Is Nothing Then
        Set rStart = lo.HeaderRowRange.Cells(1).Offset(lo.ListRows.Count + 1)
        Set rStart = lo.InsertRowRange.Cells(1)
    End If
    rStart.Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
End Sub

[1]: Now you get the disclaimer. There’s a lot you can do with Tables in Excel. You can have a header row or now header row. You can have a totals row or not. And you can have a bunch of other stuff that makes this code not work. I use Tables a lot from a UI perspective and sometimes I have various features on or off. But the way I’m using a ListObject in this example is as a datastore. It’s not meant to be messed with – only for the VBA to read from and write to. In those cases, I make the Table the only thing on the sheet, it always has a header, and it never has a total row. If you want to use Tables differently, you’ll have to modify the code to accommodate the differences.

Tidying Up the Project Explorer

You know that pressing Ctrl+R in the VBE takes you to the Project Explorer. Of course you do. I’m not going to sit here and insult your intelligence by pretending that you don’t. But that means you are probably also just as confused as I am about which projects the VBE “chooses” to expand and which stay closed.

I have a lot of add-ins and that makes my Project Explorer very messy. Ideally I want the active project to be expanded and everything else collapsed. But I can’t have that. I decided I’ve had enough and added the following hotkey to my AutoHotKey VBE.ahk file.

    Send ^r
    Send {End}
    Loop 20 {
        Send {Left 2}{Up}

When I’m in the VBE (an IfWinActive condition in VBE.ahk) and press Ctrl+Shift+R, the focus goes to the Project Explorer, goes to the last project, then closes up to 20 of them. Two left arrows will collapse an expanded project and has no effect on a collapsed project. The up arrow goes to the next project. Genius, you say? I humbly agree.

I don’t keep my modules in Folders (Toggle folders using the button at the top of the Project Explorer). If you do, you’ll need to change the {Left 2} to {Left 4}.

Handling Specific Errors

A few years ago I posted my code for pasting values. It’s changed a bit since then. This morning, it looked like this:

Sub CopyPasteValues()
    gclsAppEvents.AddLog "^+v", "CopyPasteValues"
    If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then
        Selection.PasteSpecial xlPasteValuesAndNumberFormats
    ElseIf Application.CutCopyMode = xlCut Then
        If Not ActiveSheet Is Nothing Then
        End If
    End If
End Sub

If I’m copying, then I paste both the values and the number formats (but not other formats, comments, data validation, etc). I found that this was my most common need. When I only want values, I use Alt+E+S like in the old days. If I’m cutting, I can’t PasteSpecial, so I just Paste.

Usually I select the first cell where I want to paste and press Ctrl+Shift+V and the selection expands to fit the data. Sometimes, however, that expanded selection contains merged cells which causes an error. I’ve just ignored the error in the past, but I figured it was time to fix it. Now my code looks like this:

Sub CopyPasteValues()
    gclsAppEvents.AddLog "^+v", "CopyPasteValues"
    If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then
        On Error GoTo ErrHandler
        Selection.PasteSpecial xlPasteValuesAndNumberFormats
    ElseIf Application.CutCopyMode = xlCut Then
        If Not ActiveSheet Is Nothing Then
        End If
    End If
    Exit Sub
    Select Case Err.Number
        Case 1004
            If IsNull(Selection.MergeCells) Then
                MsgBox "The range " & Selection.Address & " has merged cells. Can't paste"
                MsgBox Err.Description
            End If
        Case Else
            MsgBox Err.Description
    End Select
    Resume ErrExit
End Sub

Above the PasteSpecial line, I put an On Error statement to direct the program flow to an error handler. I didn’t apply that to the Cut portion of the code because cutting and pasting prompts me to unmerge cells and that’s the behavior I want.

In the error handling block, I check for the specific error 1004. I didn’t want to gloss over any old error, just this one in particular. If the error is 1004 (PasteSpecial method of Range class failed, or something like that), I then check to see if the new, expanded selection has any merged cells. While this is the only scenario that I’ve experienced that produces that error, it’s a pretty generic error and I’m sure there’s more. So I wanted to see the error description for any other errors.

To check for merged cells, I use IsNull(Selection.MergeCells). When the selection is more than one cell, the MergeCells property returns True if all the cells are merged, False, if none of the cells are merged, and Null if only some of the cells are merged. I don’t try to fix the situation, just display a somewhat meaningful error message. I don’t use merged cells all that often (it’s usually someone else’s spreadsheet, but not always). When I encounter this error, it’s usually because I copied more cells than I thought – hidden cells in the copy range. So it’s best to go back and start over.

At then end of the error block, it Resumes to ErrExit where it simply exits the sub. If there are no errors, the Exit Sub is executed before the ErrHandler label is reached.

Recent Update of Office causes problems with ActiveX controls


Yesterday, I installed a host of updates, including some of Office.
As it happens, I tried to add an ActiveX control to a worksheet and received an error.
After some research I discovered the cause of the error to be two-fold:

1. The controls were updated by the update
2. Excel did not clean up after itself properly and left some temporary files behind.

The solution is to:
– Quit Excel
– Open Explorer
– Select C: drive
– Search for *.exd
– Remove all files found.

Hope this helps other people who might be suffering from the same problem.


Jan Karel Pieterse

#####UPDATE Dec 22, 2014#####
Microsoft has published a so-called Fixit to make resolving this matter easier:

Evaluate Mid

In my last post, I created an array from formula text by using VBA’s Evaluate method, in order to roll my own FormulaArray function that displays the array returned by a formula, for documentation purposes.

In the course of this, I’ve discovered something a bit weird about how this method evaluates the arrays returned by a MID function.
Let’s use this snippet:

Sub EvaluateThis()
Dim var As Variant
var = ActiveSheet.Evaluate(ActiveCell.Formula)
End Sub

First, let’s look at how it handles an array generated by the COLUMN() function:
Evaluate COLUMN array2

Now watch what happens when we use that array to split apart a string using the MID() function:
Evaluate MID array2

So if you push F9, you get an array, but if you use the Evaluate method you don’t…you just get the first letter. Is this weird, or am I missing the point?


Since Excel 2013, Microsoft has given us a FORMULATEXT function, which if you point at a cell will do just that. Here’s a naughty snapshot of FORMULATEXT playing with itself in the corner:

I thought I’d have a go at writing a FormulaArray function to complement it, because when I’m building up a complicated formula that uses lots of array manipulation, then I like to document how all the different arrays within it fit together. (I was going to say “come together” there, but after that crack about FORMULATEXT playing with itself, I thought better of it. But now that I’ve said crack, I’m gonna throw caution to the wind and say wind too.)

Currently I document my formula beasts by either either array-enter a sub-part in the sheet with some notes, like this:
Documentation 1
…which shows how my ExtractNumber formula works, or I enter the desired formula in one cell with a ShowFormula to the left and a hand-rolled hard-coded array to the right, like in this table where I’m documenting a few ways to dynamically generate consecutive integers:
Documentation 2
I get that ResultArray manually, by clicking in the formula bar, pushing F9, copying the resulting evaluated array, then pasting it in another cell. Tedious. Especially when I later make a change to that sub-part, because then I get to do those steps all over.

So I started to roll my own FormulaArray function. I got a bit bogged down in the joining bit, but after about an hour of Googling, I rediscovered Nigel Heffernan’s code for joining two dimensional arrays. Which is very concerning, because I discovered it like just 10 days ago, and even wrote an extensive blog post on it right here. Senility is obviously setting in. If I start saying the same thing over and over like my mother does, just shoot me. If I start saying the same thing over and over like my mother does, just shoot me.

Anyways, Nigel’s function needs a 2D array. You can create an array from formula text by using VBA’s Evaluate method. If the formula returns a Row vector or a 2D vector, then Evaluate nicely turns it into a 2D vector. But here’s the rub: if the formula returns a Column vector, then Evaluate only gives us a 1D vector, which ain’t gonna wash with Nigel’s function:
Row vs Column
So what we need to do is TRANSPOSE any formulas that would return Column vectors, because chucking a TRANSPOSE into the mix has the desired effect:
Row vs Column2
Note that I’m using the square brackets [ ] shortcut for Evaluate. I could just have easily done it like this:
Row vs Column3

Okay, so we know that if our formula string returns a Column vector, we’ve got to transpose it. But how can we tell that ahead of time? I can’t think of a way. So I just do this:

Function FormulaArray(Target As Range) As String
Dim strInput As String
Dim var2 As Variant
Dim lb As Long

strInput = Mid$(Target.Formula, 2)
var2 = ActiveSheet.Evaluate(strInput)
On Error Resume Next
lb = LBound(var2, 2)
If Err.Number <> 0 Then
    var2 = Application.Transpose(ActiveSheet.Evaluate(strInput))
    FormulaArray = Join2d(var2, ",", ";")
    FormulaArray = Join2d(var2, ";", ",")
End If
End Function

So I evaluate the formula as if it’s a Row vector, then check if I’ve got 2 dimensions as a result. If not, it must have been a column vector, in which case I transpose it, then reevaluate it. Shame about the double evaluation, but I can’t think of a foolproof way to do it differenty, other than perhaps array entering the formula into a 2D range on the worksheet and looking at where the #N/A! errors fall.

Anyway, it seems to work just fine:

..unless you happen to be using Structured Table References, and your arguments happen to use the @ table notation to point at something on the same row:
…or unless you happen to have a formula with the INDIRECT function in it:
With the ThisTableRow thing, I guess I can just replace the @[SomeColumn] bit with the actual address, but I can’t think of easy ways around the INDIRECT thing. Anyone got any ideas?

Edit: Thinking about this some more, all I need to do is substitute the INDIRECT(SomeExpression) with whatever gets returned by RANGE(SomeExpression).value

Sample workbook:


There’s a handy post over at Charles Williams’ site that talks about some other quirks of Evaluate that’s worth checking out: