Subtracting Cells in the Status Bar

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.

Private Sub mxlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
    If TypeName(Selection) = "Range" Then
        ShowDifferenceStatus Selection
    End If

End Sub

That event procedure calls this procedure in a standard module.

Public Sub ShowDifferenceStatus(rSel As Range)
       
    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

vStatus = "Difference: " & Format(wf.Sum(rSel.Columns(1)) - wf.Sum(rSel.Columns(2)), rSel.Cells(1).NumberFormat)

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.

Caution: Pointless Message

I was walking through the Wellington CBD the other day on one of those rare fine days that occasionally falls between earthquakes that warrant a 7.2 on the sphincter scale and hurricane-force Southerlies, when I came across this sign:
Caution - Pointless Message

I’m not really sure of the sign’s intention. What kind of caution should one exercise here? Run fast to avoid falling people? Refrain from undoing the hand-brake on those scaffold wheels? Don’t look up in case they are Scottish migrant workers? Damned if I know, really. Had I not donned a hard-hat I would have scratched my head.

Anyway, I thought that question on warning messages crossed with kilt imagery would segue nicely into a “Show us ya Messages” open post.

I’ll go first.

Here’s a couple from a Project Costing Model I put together recently.
Caution - Pointless Message - New Option

Caution - Pointless Message - Faster than light

How ’bout you all? Anything snigger-worthy from your apps that you’d like to share?

Indecent Add-in Exposure

Every so often, for reasons I can’t explain, an add-in shows up in my Excel UI.

addinshowingup

Today it was BlogHelpers.xla, but other times it’s different. It’s always in a non-maximized window view. That is, the workbook name is in the window’s title bar and not the application’s title bar. You can see that the IsAddin property is still True. If I close the UI window, the Add-in remains in the VBE projects window. But I’m too chicken to leave it in that state for long, so I always reboot.

Have you ever seen that before?

Chart LeaderLines in Excel 2010 or earlier.

I love the Leader Lines that Excel 2013 puts in for you if you’re using Excel 2013 and you drag a datalabel somewhere. If you want to use Leader Lines in earlier versions, then they are only available if you use a pie chart. And we all know what Peltier would say about that, don’t we.

So I thought I’d have a crack at programatically adding leader lines to ‘Peltier-approved’ chart types via VBA shapes.

First, let’s look at what those native leader lines in Excel 2013 look like, eh?
Leader lines in Excel 2010

Ha! Fooled you…those are my programaticaly applied lines in Excel 2010.

I added these via using a Freeform shape, and the code lives inside a Class Module, so that if you move a data label around, the leader line gets redrawn. For instance, let’s drag that 4th data label North-East:
Leader lines in Excel 2010 2

Just like in Excel 2013, my routine intelligently connects the leader line to the appropriate side of the data label, depending on it’s position relative to the point it connects to. For instance, let’s park that 5th data label directly over the point it refers to:
Leader lines in Excel 2010 3

…and now let’s put it on the left:
Leader lines in Excel 2010 4

And if the data label obscures the point, no leader line is produced:
Leader lines in Excel 2010 5

I used a Class Module to do this. In fact, this is the first Class Module I have ever written:

Option Explicit

Public WithEvents clsChart As Chart


Private Sub clsChart_Mouseup(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim IDNum As Long
Dim a As Long
Dim b As Long
Dim shpLeaderLine As FreeformBuilder
Dim pt As Point
Dim lKink As Long
lKink = 5
Dim bLabel As Boolean
Dim shp As Shape

ActiveChart.GetChartElement x, y, IDNum, a, b

If IDNum = xlDataLabel Then
    On Error Resume Next
    ActiveChart.Shapes("LeaderLine_" & a & "_" & b).Delete
    On Error GoTo 0
    bLabel = True
    Set pt = ActiveChart.SeriesCollection(a).Points(b)
    Set shpLeaderLine = ActiveChart.Shapes.BuildFreeform(msoEditingAuto, pt.Left, pt.Top)
        With shpLeaderLine
            Select Case pt.Left
                Case Is < pt.DataLabel.Left
                    .AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left - lKink, pt.DataLabel.Top + pt.DataLabel.Height / 2
                    .AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left, pt.DataLabel.Top + pt.DataLabel.Height / 2
                Case Is > pt.DataLabel.Left + pt.DataLabel.Width
                    .AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width + lKink, pt.DataLabel.Top + pt.DataLabel.Height / 2
                    .AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width, pt.DataLabel.Top + pt.DataLabel.Height / 2

                Case Else
                    Select Case pt.Top
                        Case Is < pt.DataLabel.Top
                            .AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width / 2, pt.DataLabel.Top
                        Case Is > pt.DataLabel.Top + pt.DataLabel.Height
                            .AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width / 2, pt.DataLabel.Top + pt.DataLabel.Height
                        Case Else
                            bLabel = False
                    End Select
            End Select
            If bLabel Then
                Set shp = .ConvertToShape
                With shp
                    .Name = "LeaderLine_" & a & "_" & b
                    .Line.ForeColor.ObjectThemeColor = msoThemeColorBackground1
                End With
            End If
        End With
   End If
End Sub

I use the Workbook_Open event to trigger this:

Option Explicit
Dim myChart As New clsChart
Private Sub Workbook_Open()
If Application.Version < 15 Then
    Set myChart.clsChart = Sheet1.ChartObjects("Chart 1").Chart
End If
End Sub

I can’t say I fully understand Class Objects yet. For instance, I’d like to amend this code so that it works on multiple charts. I know the answer to this will be buried within Chip Pearson’s exhaustive but dense page at http://www.cpearson.com/excel/classes.aspx …but I’m too dense to absorb all this. I was kinda hoping you’d find it for me ;-)

I also note that the code doesn’t always trigger for some reason, when moving the data label. I suspect it’s because I’m still moving the mouse at the time that I release the click button, as this only seems to happen when I’m moving the data label from left to right. Anyone got any pointers on this?

Here’s the file:

Segmenting-customers-by-revenue-contribution_V11

Changing the Sign of a Cell

You probably already know the trick for changing the signs on a bunch of cells. But if not, here it is:

  1. Type -1 into an unused cell on your worksheet
  2. Copy that cell
  3. Select the cells whose sign you want to change
  4. Paste Special – Multiply

I do this quite a bit. Recently when I had to enter a pretty long list of numbers, most of which were negative, I decided to enter them with their sign reversed (for ease of entry) and employ this technique. Then I thought that it was just too much work. So I wrote a macro. It doesn’t do exactly what the Copy – Paste Special – Multiply trick does, but arguably does it better.

Private Const msFORMADD As String = ")*-1"
Private Const msFORMST As String = "=("

Sub ChangeSign()
   
    Dim rCell As Range

    gclsAppEvents.AddLog "^+n", "ChangeSign"
   
    If TypeName(Selection) = "Range" Then
        For Each rCell In Selection.Cells
            If CellCanChangeSign(rCell) Then
                If rCell.HasFormula Then
                    If CellFormulaHasSignChange(rCell) Then
                        rCell.Formula = RemoveFormulaSignChange(rCell.Formula)
                    Else
                        rCell.Formula = Replace(rCell.Formula, "=", msFORMST, 1, 1) & msFORMADD
                    End If
                ElseIf IsNumeric(rCell.Value) Then
                    rCell.Value = -rCell.Value
                End If
            End If
        Next rCell
    End If
   
End Sub

Function CellCanChangeSign(rCell As Range) As Boolean
   
    CellCanChangeSign = rCell.Address = rCell.MergeArea.Cells(1).Address And Not IsEmpty(rCell.Value)
   
End Function

Function CellFormulaHasSignChange(rCell As Range) As Boolean
   
    CellFormulaHasSignChange = Left$(rCell.Formula, Len(msFORMST)) = msFORMST _
        And _
                                Right$(rCell.Formula, Len(msFORMADD)) = msFORMADD
       
End Function

Function RemoveFormulaSignChange(ByVal sFormula As String) As String
   
    Dim sReturn As String
   
    sReturn = Left$(sFormula, Len(sFormula) - Len(msFORMADD)) 'remove last characters
    sReturn = Replace$(sReturn, msFORMST, "=", 1, 1) 'remove first paren
   
    RemoveFormulaSignChange = sReturn
   
End Function

ChangeSign is the entry point procedure and is called with Ctrl+Shift+n (more on what AddLog is in a later post). After it determines that there is a range selected (and not a shape, for example), it calls CellCanChangeSign to make sure it’s OK to move forward. In CellCanChangeSign I try to deal with merged cells. I don’t use merged cells a ton, so I can never remember how to avoid problems in VBA when dealing with them. I decided that if the cell was the first cell in the merge area, that was good enough. If the cell isn’t merged, its MergeArea is equal to itself, so this would still return True. Also, I don’t want to do anything to empty cells so I make sure that’s not the case.

Once I verify that the cell is good to go, I treat cells with formulas different than cells with values. When you use Paste Special – Multiply, Excel takes whatever formula you have and changes the formula to multiply by -1. For example, =SUM(F6:F14) becomes =(SUM(F6:F14))*-1. I’m not sure why it doesn’t negate the formula with -(formula), but it doesn’t. I made mine match what Excel does.

One problem with changing the signs of formulas is that if you do it twice, then =SUM(F6:F14) becomes =((SUM(F6:F14))*-1)*-1. Of course what else could it do. It’s a multiplying operation, not a toggle. I tried to make mine a toggle at least where I could. If you monkey with the formula between operations, you may get multiple multiplications, but if you just run the code twice it will remove what it did. I’ve tested this nearly a half dozen times, so I’m sure it’s rock solid.

CellFormulaHasSignChange checks that "=(" is at the start of the formula and ")*-1" is at the end. If they are then [cci]RemoveFormulaSignChange[cci] removes them and sets the formula back to its original.

For cells with numbers and not formula, the Value is simply negated.

“Always” vs “Never”

I always select data from the top down, when I go to make a chart.
I never think to hit the Ctrl + Down Arrow first, so that I can select the range while leaving the active cell at the top.

I always end up with a chart waaay down at the bottom of that very long selection, where my active cell is.
I never want it all the way down there.

I always select the wayward chart, then move it gingerly up towards the Headings bar, in the vain hope that Excel will go into scroll mode, and let me release the chart where it belongs…at the top.
I never seem to hit that magic sweet spot, where Excel starts scrolling quicker than a crawl.

I always curse, then cut the chart, hit Ctrl + Up Arrow, then paste it up where it belongs.
I never remember this monkey business the next time I go to make a chart.

I always hope that MS will sort out basic usability stuff like this in the next release.
I never hold my breath.

“Yes please” vs “WTF?”

DDOE_Thank you vs. WTF_Formula Notation
Yes please.

DDOE_Thank you vs. WTF_Table Notation
WTF? That formula is the same as the last one, only it uses Table notation!

–Edit–

Out of interest, here’s how that 1st message looks in Excel 2010. (Apologies for the old-school look of these next two screenshots, vs the previous ones. I’m too tight to install Excel 2010 AND 2013 on all the machines in our house, so this screenshot comes from my wife’s PC, which runs XP, on account of that same monetary tightness.)

DDOE_Yes Please vs WTF_Old Correction

Let’s say I do decide to take the option offered in that 2nd bullet point – to close the message and correct the formula myself – and click NO. What do I get?

DDOE_Yes Please vs WTF_Old Yes

You told me that one click ago. Get out of my way, so I can do what I said I was going to do one click ago…i.e. fix the damn thing!

I spend heaps and heaps of my time on usability things when I build stuff in Excel. I can’t comprehend why these really crappy legacy usability issues are still perpetuated by the MS developers release after release. WTF.

Should I learn PowerPivot yet?

Rob’s got a good post called What is Power Pivot’s #1 Competitor? which has some great comments, as well as a hilarious YouTube clip.

One of his musings is that companies like Tableau – who are indeed competitors to Power Pivot – don’t really view Power Pivot as an obstacle to Tableau getting new customers. They know their biggest competitor is still just Excel itself.

That’s also PowerPivot’s biggest competitor. To the point that the opportunity cost to MS of users foregoing PowerPivot for Tableau is an order of magnitude smaller that the opportunity costs to MS of Excel users that simply never realize PowerPivot might be something they could use to sack lots of overpriced, underwhelming reporting analysts.

As per my comment I left on that post, I haven’t embraced PowerPivot yet because I’m still too busy panicking about not knowing everything I think I need to know about Excel and VBA. There’s still plenty of posts on this site that I don’t even partially comprehend, and only when I’ve got these under my belt will I STOP panicking about “this” and then START panicking about “that” (PowerPivot). Guess I’ve got a one-track mind.

Last time I looked, PowerPivot and those other new-fangled inventions weren’t programmable by VBA to a great extent. So in my mind – and based partly on what I do know and largely on what I don’t – I get the feeling that I can’t very easily integrate these new-fangled inventions into my existing factory. While I’m sure that I can probably replace quite a bit of that VBA-driven factory with these new-fangled inventions, I’m loathe to step away from the production line while I do this, because of a fear that orders are going to be piling up somewhere.

What’s more, because I switch companies every 18 months to 3 years, while I could possibly put these new-fangled inventions to work where I work now, there’s no guarantee that my new skills will be required in a new role, given PowerPoint ain’t exactly as ubiquitous as Excel is. In fact I think an increase in my old skills will probably be a better bet.

That all said and not done, why to I still get the feeling that I’m missing the opportunity of a lifetime by not investing now. Why do I have a vague sinking feeling that I’m sinking almost imperceptibly into a career tar-pit, rather than evolving into a higher life form?

Ah well, if I’m going to be a living dinosaur, then I’m in good company and in the right place:
DDOE_Should I learn PowerPivot

Formatting Pivot Tables Version 3

I’ll try to tone down the hyperbole from the last post. It all started with a simple macro to format the selection with the comma style.

Sub MakeComma()
   
    If TypeName(Selection) = “Range” Then
        Selection.Style = “Comma”
    End If
   
End Sub

I loved this little macro. Then I decided that formatting pivot tables was just as tedious as applying styles. I made Version 2, and I saw that it was good.

Sub MakeComma()
   
    Dim pf As PivotField
   
    If TypeName(Selection) = “Range” Then
        On Error Resume Next
            Set pf = ActiveCell.PivotField
        On Error GoTo 0
       
        If pf Is Nothing Then
            Selection.Style = “Comma”
        Else
            pf.NumberFormat = “#,##0.00″
        End If
    End If
   
End Sub

The other day I was making a pivot table that used the Count aggregation. I don’t use Count anywhere near as much as Sum, but there I was. Showing two decimal places with Count isn’t the worst thing in the world, but I didn’t like it. Counting is done with whole numbers. Nor did I like changing the number format manually. Version 3 was born.

Sub MakeComma()
   
    Dim pf As PivotField
   
    Const sNODECIMALS As String = "#,##0"
    Const sTWODECIMALS As String = "#,##0.00"
   
    If TypeName(Selection) = "Range" Then
        On Error Resume Next
            Set pf = ActiveCell.PivotField
        On Error GoTo 0
       
        If pf Is Nothing Then
            Selection.Style = "Comma"
        Else
            If pf.NumberFormat = sTWODECIMALS Then
                pf.NumberFormat = sNODECIMALS
            Else
                pf.NumberFormat = sTWODECIMALS
            End If
        End If
    End If
   
End Sub

My initial revision checked whether the PivotField.Function property was xlSum or xlCount and applied formatting appropriately. That felt too rigid. Once my fingers are on Ctrl+M, it really doesn’t matter to me how many times I have to press down (within reason of course). So I went with a toggle between two decimals and no decimals. Now I’m happy again.

To assign to a shortcut key, I have these two statements in my Auto_Open and Auto_Close procedures, respectively.

Application.OnKey "^m", "MakeComma"
Application.OnKey "^m"

New and Improved Wrap Sheets Hotkey

Hey, remember back in 2010 when I had that bright idea about a repurposing Ctrl+PgUp and Ctrl+PgDn? Sure you do. The idea was that when I’m at the end of a workbook with a lot of worksheets, it would be easier to hit Ctrl+PgDn and wrap around to the first sheet rather than hold down Ctrl+PgUp until I got to the first sheet. I gave that shortcut the ol’ college try, but in the end I abandoned it. It turns out that I like holding down Ctrl+PgUp to get to the first page. But when I do that and this utility is active, it blows right by the first page and wraps around.

There’s this thing called Fitts’ Law and it indicates some things about infinity

Fitts’ law indicates that the most quickly accessed targets on any computer display are the four corners of the screen, because of their pinning action, and yet, for years, they seemed to be avoided at all costs by designers.

Fitts’ Law deals with pointing devices and targets, but the same principle applies to hotkeys. In Excel, if you hold down Ctrl+PgDn for infinity, you will end up on the last visible sheet of your workbook. That’s powerful because it removes all of your precision responsibility. I don’t care how imprecise you are, you can certainly hit an infinitely wide barn. Contrast that with the File menu in Excel 2010. Press Alt+F and then hold the down arrow for infinity. If the UI designers had been paying attention you would end up on “Exit” (the last item on the list), but you don’t. The cursor just keeps looping through the items on the list.

Even though I abandoned the hotkey, I can’t seem to get it out of my head that it’s a good idea. I’m stubborn like that. What if I could have the best of both worlds? What if I could race to the end of a workbook by holding down Ctrl+PgDn, but still wrap around to the first sheet when I wanted to? I changed the code to add a little delay. I started with 1 second, but determined that 1/2 second works better. Now, if the time between the last time I pressed Ctrl+PgDn and now is greater that 0.5 seconds, sheet activation will wrap around to the first sheet. If it’s less than 0.5 seconds, it’s assumed that I’m looking for infinity and remains on the last worksheet. And it all works the same for Ctrl+PgUp, just in reverse. I start with a module level variable and a module level constant.

Private msnLastWrap As Single
Private Const msnWRAPBUFFER As Single = 0.05

The Single msnLastWrap will keep track of the last time I pressed the hotkey. The rest of the code is the same from the prior post except that I added a couple of If statements to check the time differential and of course to set msnLastWrap.

Sub WrapSheetsUp()
           
    If ActiveSheet.Index = FirstVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            ActiveWorkbook.Sheets(LastVisibleSheetIndex).Activate
        End If
    Else
        ActiveWorkbook.Sheets(NextVisibleSheetIndex(False)).Activate
    End If
   
    msnLastWrap = Timer
   
End Sub

Sub WrapSheetsDown()
   
    If ActiveSheet.Index = LastVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            ActiveWorkbook.Sheets(FirstVisibleSheetIndex).Activate
        End If
    Else
        ActiveWorkbook.Sheets(NextVisibleSheetIndex(True)).Activate
    End If
   
    msnLastWrap = Timer
   
End Sub

In WrapSheetsDown when it gets to the last sheet (ActiveSheet.Index = LastVisibleSheetIndex) it checks to see how much time has elapsed (Timer – msnLastWrap). Timer is a VBA function that returns the number of seconds since midnight. Regardless of the results of that test, I assign Timer to my module level variable.

The rest of the code is unchanged, but for completeness, here’s what I have in my Auto_Open and Auto_Close procedures respectively.

    Application.OnKey "^{PGUP}", "WrapSheetsUp"
    Application.OnKey "^{PGDN}", "WrapSheetsDown"

    Application.OnKey "^{PGUP}"
    Application.OnKey "^{PGDN}"

And the rest of the procedures needed.

Public Function FirstVisibleSheetIndex() As Long
   
    Dim lReturn As Long
    Dim sh As Object
   
    For Each sh In ActiveWorkbook.Sheets
        If sh.Visible Then
            lReturn = sh.Index
            Exit For
        End If
    Next sh
   
    FirstVisibleSheetIndex = lReturn
   
End Function

Public Function LastVisibleSheetIndex() As Long
   
    Dim lReturn As Long
    Dim i As Long
   
    For i = ActiveWorkbook.Sheets.Count To 1 Step -1
        If ActiveWorkbook.Sheets(i).Visible Then
            lReturn = i
            Exit For
        End If
    Next i
   
    LastVisibleSheetIndex = lReturn
   
End Function

Public Function NextVisibleSheetIndex(bDown As Boolean) As Long
   
    Dim lReturn As Long
    Dim i As Long
   
    If bDown Then
        For i = ActiveSheet.Index + 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    Else
        For i = ActiveSheet.Index - 1 To 1 Step -1
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    End If
   
    NextVisibleSheetIndex = lReturn
   
End Function