Thanks for nothing, ListObject

Why is it that you can do this:

Dim lo as ListObject
Set lo = ActiveCell.ListObject
If lo Is Nothing Then 'Do something

…but you can’t do this:

Dim pt as PivotTable
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then 'Do something

…and instead you have to do this:

Dim pt as PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotTable
If Err.Number > 0 then 'Do something

Huh? Huh?

Sending Images via WinSCP

Since my recent move to Digital Ocean for hosting, I’ve had to make a change to how I upload images for this blog. I used to create an FTP file and a batch file, but as far as I know that doesn’t support SFTP. I’m using WinSCP to transfer files manually and learned that it has a command line interface. I made a procedure called SendViaSCP to replace my SendViaFTP.

Public Sub SendViaSCP(vFname As Variant)
    Dim aScript() As String
    Dim i As Long
    ReDim aScript(1 To 4 + UBound(vFname))
    aScript(1) = "option batch abort"
    aScript(2) = "option confirm off"
    aScript(3) = "open sftp://username:password@"
    aScript(UBound(aScript)) = "exit"
    For i = LBound(vFname) To UBound(vFname)
        aScript(3 + i) = "put " & Dir(vFname(i)) & " /home/wordpress/public_html/blogpix/"
    Next i
    Open "winscpup.txt" For Output As #1
    Print #1, Join(aScript, vbNewLine)
    Close #1
    Shell "winscpup.bat"
End Sub

The vFname arguments is a variant array that holds all of the files I selected from Application.GetOpenFileName. The aScript array holds three lines of setup, a line for each file, and an exit line.

The commands are joined together and written to a batch file and the batch file is run. It doesn’t solve the problem that Billkamm and Haines solved of having your username and password in a batch file, but I can live with it.

You might be wondering why I don’t just use the file upload functions in WordPress. What fun would that be?

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")
            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
        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.


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 …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:


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)
                        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!


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