Sometimes I just want to quickly see the difference between two cells or groups of cells. Excel puts some great aggregates in the status bar.
and you can even customize them. Right click on the those aggregates.
But I wanted the difference. So I wrote some code to find it. I already had a class module with an Application object declared WithEvents, so I added this SheetSelectionChange event procedure.
If TypeName(Selection) = "Range" Then
ShowDifferenceStatus Selection
End If
End Sub
That event procedure calls this procedure in a standard module.
Dim wf As WorksheetFunction
Dim vStatus As Variant
On Error Resume Next
Set wf = Application.WorksheetFunction
If rSel.Areas.Count = 1 Then
If rSel.Columns.Count = 2 Then
vStatus = "Difference: " & Format(wf.Sum(rSel.Columns(1)) – wf.Sum(rSel.Columns(2)), "#,##0.00")
ElseIf rSel.Rows.Count = 2 Then
vStatus = "Difference: " & Format(wf.Sum(rSel.Rows(1)) – wf.Sum(rSel.Rows(2)), "#,##0.00")
Else
vStatus = False
End If
ElseIf rSel.Areas.Count = 2 Then
If (rSel.Areas(1).Columns.Count = 1 And rSel.Areas(2).Columns.Count = 1) Or _
(rSel.Areas(1).Rows.Count = 1 And rSel.Areas(2).Rows.Count = 1) Then
vStatus = "Difference: " & Format(wf.Sum(rSel.Areas(1)) – wf.Sum(rSel.Areas(2)), "#,##0.00")
End If
Else
vStatus = False
End If
Application.StatusBar = vStatus
End Sub
If the selection is contiguous (Areas.Count = 1), it determines if there are two columns or two rows. Then it uses the SUM worksheet function to sum up the first and subtract the sum of the second. Anything other that two columns tow rows resets the StatusBar by setting it to False. Subtracting one cell from the other is easy enough, but I wanted the ability to subtract one column from the other (or one row). Using SUM also avoids me having to check for text or other nonsense that SUM does automatically. Here’s one where I only have one Area selected and it contains two columns. It sums the numbers in column B and subtracts the sum of column C.
When the selection is not contiguous (Areas.Count = 2), then it determines if both areas have only one column or only one row. If either has more than one, it resets the status bar. But if they both have one (of either), it subtracts them. Here I’ve selected B2:B3, then held down the Control key while I selected C3:C4. That’s two areas, but each only has one column, so it assumes I want to subtract columns.
The next feature I want to add is to recognize filtered data. Often I’m working with a filtered Table and although two cells appear to be adjacent, selecting them without holding down Control really selects all those filtered cells in between. I guess I’ll need to loop through and determine what’s visible, build a range from only those cells, and sum that. For now, I’m just holding down control and using the mouse to select them. If you’re not familiar, the “mouse” is that blob of plastic several inches away from home row (aka the productivity killer). Excuse me while I get off my soap box and finish this post.
I tried to glean the NumberFormat of the cells selected and use that in the display. You can see from the code above that I punted and just used a comma and two decimals. But that stinks for really small numbers. Originally, I had something like
But look at the craziness when the cell as the Accounting format (_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_))
It works well for times though.
Apparently the syntax for cell formatting is slightly different than for the VBA.Format function. I haven’t worked out what the differences are, but maybe someday I will.
