Paste and Transpose icon in QAT crash Excel 2013

Hi all

I add a new page on my site with a workaround for this problem. I hope that there are no more of this problem icons for the QAT.

Not easy find the problem with bugs like this, you not think that an Excel icon in the QAT can crash Excel.

Paste and Transpose icon in QAT crash Excel 2013

Regards Ron de Bruin

Daylight Saving Time Error

It’s DST here in the US and I couldn’t be happier. I don’t care if my drive to work is pitch black, but the drive home? That’s another story.

One thing I learned since the clocks sprung forward is that the GetTimeZoneInformation API doesn’t work the way I thought. The TIME_ZONE_INFORMATION return type has a Bias property. Bias tells you how many minutes you are away from GMT. Or so I thought. It actually tells you how many minutes you are from GMT in standard time. The TIME_ZONE return value tells you if it’s daylight saving time or standard time. So you have to take both into account to get the correct time.

Here’s the API declaration

    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

    Bias As Long
    StandardName(0 To 31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(0 To 31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type

' These give symbolic names to the time zone
' values returned by GetTimeZoneInformation .

Private Enum TIME_ZONE
    TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST
    TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight
    TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard
End Enum

Private Declare Function GetTimeZoneInformation Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Sub GetSystemTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME)

And the updated procedure:

Public Function ConvertTimeToLocal(ByVal dtTime As Date, ByVal sZone As String) As Date
    Dim tz As TIME_ZONE
    Dim lGmtOff As Long
    tz = GetTimeZoneInformation(tzi)
    Select Case UCase(sZone)
        Case "EDT"
            lGmtOff = -4
        Case "EST", "CDT"
            lGmtOff = -5
        Case "CST", "MDT"
            lGmtOff = -6
        Case "MST", "PDT"
            lGmtOff = -7
        Case "PST"
            lGmtOff = -8
        Case vbNullString
            lGmtOff = -tzi.Bias / 60
    End Select
    If tz = TIME_ZONE_DAYLIGHT Then lGmtOff = lGmtOff - 1
    ConvertTimeToLocal = dtTime - (TimeSerial(0, tzi.Bias, 0) + TimeSerial(lGmtOff, 0, 0))
End Function

I also added a UCase around the zone because it’s just stupid not to have that. Enjoy saving the daylight, but remember you’ll owe it back this fall.


That’s why we write tests people.

Public Function ConvertTimeToLocal(ByVal dtTime As Date, ByVal sZone As String) As Date
    Dim tz As TIME_ZONE
    Dim lGmtOff As Long
    Dim lBias As Long
    tz = GetTimeZoneInformation(tzi)
    lBias = tzi.Bias
    If tz = TIME_ZONE_DAYLIGHT Then lBias = lBias - 60
    Select Case UCase(sZone)
        Case "EDT"
            lGmtOff = -4
        Case "EST", "CDT"
            lGmtOff = -5
        Case "CST", "MDT"
            lGmtOff = -6
        Case "MST", "PDT"
            lGmtOff = -7
        Case "PST"
            lGmtOff = -8
        Case vbNullString
            lGmtOff = -tzi.Bias / 60
            If tz = TIME_ZONE_DAYLIGHT Then lGmtOff = lGmtOff + 1
    End Select
    ConvertTimeToLocal = dtTime + (TimeSerial(0, lBias, 0) + TimeSerial(lGmtOff, 0, 0))
End Function

Public Sub Test_ConvertTimeToLocal()
    Dim dtTestTime As Date
    dtTestTime = TimeSerial(9, 46, 13)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, vbNullString) - dtTestTime) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "CDT") - dtTestTime) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "EST") - dtTestTime) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "EDT") - (dtTestTime + TimeSerial(1, 0, 0))) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "CST") - (dtTestTime - TimeSerial(1, 0, 0))) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "MDT") - (dtTestTime - TimeSerial(1, 0, 0))) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "MST") - (dtTestTime - TimeSerial(2, 0, 0))) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "PDT") - (dtTestTime - TimeSerial(2, 0, 0))) < TimeSerial(0, 0, 1)
    Debug.Assert (ConvertTimeToLocal(dtTestTime, "PST") - (dtTestTime - TimeSerial(3, 0, 0))) < TimeSerial(0, 0, 1)

End Sub

Keyboard Shortcut Metrics

You all know that I love keyboard shortcuts. There is a limit, though. Some of my custom shortcuts clear the Undo stack, which can be a real pain, so I have to balance the productivity gains of the shortcut against the side effects. Back in January, I wanted to see which shortcuts I’d been using, so I created some code to keep track. The code is below, but first the results.

No surprise to me, pasting special – values tops the list. I paste values by default unless I need to paste something else. I should really just hijack Ctrl+V. If you’re wondering why the count for this one is so low in the first two-week period, it’s because I have severely ingrained muscle memory with Alt+E+S+V to show the Paste Special dialog and select the Values option. I wasn’t really using Ctrl+Shift+V, the shortcut assigned to this macro. I made an effort to use in early February. I’m not sure if I’ve ever posted this code before, so it’s high time.

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

You can see that I like to paste the values with the number formats.

If you want to see the code for the other macros in the list, see MakeComma, SelectAdjacentCol, FrozenHome, Wrap Sheets, Formatting Taskpane, Increment Date, ChangeSign, FillSeries.

I can’t find where I ever posted my FillVirtualScreen, CopySum, or GetMappedAddress code, so I guess I should do that in a future post. I was surprised that ShowFormatting wasn’t higher. Also, I thought DecrementDate would have been more used that IncrementDate. And FillSeries only seven times in two months?

To keep track of all this, I created a CLog class and a CLogs class. CLog has LogID, DateTime, Keys, and ProcName getter/setter properties. CLogs is a typical parent class with one exception I’ll show in a bit. In my App class, I added an AddLog and WriteLog procedure.

Public Sub AddLog(ByVal sKeys As String, ByVal sProcName As String)
    Dim clsLog As CLog
    Set clsLog = New CLog
    clsLog.Keys = sKeys
    clsLog.ProcName = sProcName
    clsLog.DateTime = Now
    Me.Logs.Add clsLog
End Sub

Public Sub WriteLog()
    Dim sFile As String, lFile As Long
    If Me.Logs.Count > 0 Then
        sFile = ThisWorkbook.Path & Application.PathSeparator & "UIHelpers.log"
        lFile = FreeFile
        Open sFile For Append As lFile
        Print #lFile, Me.Logs.LogFileLines
        Close lFile
    End If
End Sub

I had to go to every procedure I wanted to track and add a call to AddLog. Then whenever my App class goes out of scope, the log is written.

Private Sub Class_Terminate()
End Sub

In CLogs, I return all the log lines as a big string to write out to the file.

Public Property Get LogFileLines() As String
    Dim aWrite() As String
    Dim clsLog As CLog
    Dim lCnt As Long
    If Me.Count > 0 Then
        ReDim aWrite(1 To Me.Count)
        For Each clsLog In Me
            lCnt = lCnt + 1
            aWrite(lCnt) = clsLog.LogFileLine
        Next clsLog
        LogFileLines = Join(aWrite, vbNewLine)
    End If
End Property

That calls CLog.LogFileLine

Public Property Get LogFileLine() As String
    Dim aWrite(1 To 3) As String
    aWrite(1) = Me.DateTime
    aWrite(2) = Me.Keys
    aWrite(3) = Me.ProcName
    LogFileLine = Join(aWrite, "|")
End Property

This file has a bunch of other stuff in it including half-finished ideas, but if you like…

You can download

Synchronising Slicers

Hi Everyone,

I’ve just added a new page to my site on how to synchronise slicers which point to different pivotcaches:
Synchronising Slicers

Enjoy the read!

Jan Karel Pieterse

What’s Up at DDoE

Something is spiking memory on my server and MySQL is shutting down as a result. It doesn’t seem to matter how much memory I throw at it, it just maxes out and shuts down.

This morning I implemented a plan of shutting down plugins until it happens again. I started with a database reset plugin and so far so good. I’m not hopeful, though, because this is the same problem I had before moving to Digital Ocean, so I imagine it’s just a matter of time before it comes back. Here’s the order I’ll be disabling plugins

  1. WordPress Database Reset
  2. Authors Widget
  3. Subscribe to Comments Reloaded 3/21/2014
  4. CodeColorer
  5. Akismet
  6. WP Super Cache

If it gets down to Akismet, there’ll be trouble. The comment spam is crazy and there’s no way to keep up with it manually.

Keep enjoying the posts or the “error establishing database connection” error message, whatever happens to be showing that day.

How do you know if a ListObject has the autofilter applied?

If you try to filter a ListObject, and someone has turned the entire filter off by deselecting the Filter icon from the Data tab, then you’ll get an error. But how the heck can you easily test if the filter is on or not?

If you fire up the macro recorder, and click the Filter icon a few times to toggle it on and off, then you just get this:

You can write

If Selection.AutoFilter = TRUE then...

but this simply serves to toggle the autofilter from it’s current state, and always returns true.

It seems to me that the only thing you can do is something like this:

Function FilterIsOn(lo As ListObject) As Boolean

Dim bOn As Boolean

bOn = False
On Error Resume Next
If lo.AutoFilter.Filters.Count > 0 Then
    If Err.Number = 0 Then bOn = True
End If
On Error GoTo 0
FilterIsOn = bOn
End Function

DataPoint Top and Left for 2007 or earlier.

Over at Datalabel height and width for 2007 or earlier Andy Pope says:

Another couple of properties that are not available prior to 2010 are the Left and Top values of the data point. If you want to know the position of the data point you need to get creative. Having determined the width and height of the data label you can then position the label left/right and above/below and calculate the data point.

Then Jon Peltier says:

Don’t spoil your afternoon moving datalabels around. If it’s an XY chart, then a little algebra goes a long way:
Horiz Position = plotarea.insideleft + plotarea.insidewidth*(X – Xmin)/(Xmax-Xmin)
Vert Position = plotarea.insidetop + plotarea.insideheight*(Ymax-Y)/(Ymax-Ymin)
… with corrections for plotting an axis in reverse order.

If it’s a line chart, the vertical position is as above, the horizontal position uses category number, total number of categories, and a correction for whether the axis crosses on or between categories.

If it’s a bar or column chart, you can get the length of the bar using the above (vert or horiz for column or bar chart), and if it’s stacked you need to sum them up appropriately. The width needs to take into account gap width, and if it’s clustered, how many series there are across each category.

All those potential Select Case statements that Jon will have to use give me the heebie-jeebies. So while I keenly await his forthcoming blog post on how to do things properly, I spent my afternoon being quick and dirty:

Function Pre2010_Position(dl As DataLabel) As String
Dim ptTop As Long
Dim ptLeft As Long
Dim dlLeft As Long
Dim dlTop As Long
Dim dlHeight As Long
Dim dlWidth As Long
Const lngPadding = 7

With dl
    dlTop = .Top
    dlLeft = .Left
    'Determine DL width and height
    dl.Left = ActiveChart.ChartArea.Width
    dlWidth = ActiveChart.ChartArea.Width - dl.Left
    dl.Top = ActiveChart.ChartArea.Height
    dlHeight = ActiveChart.ChartArea.Height - dl.Top
    dl.Position = xlLabelPositionRight
    If dl.Left + dlWidth = ActiveChart.ChartArea.Left + ActiveChart.ChartArea.Width Then
        'Datalabel is too wide to fit between point and plot edge
        dl.Position = xlLabelPositionLeft
        ptLeft = dl.Left + dlWidth + lngPadding
        ptLeft = dl.Left - lngPadding
    End If
    dl.Position = xlLabelPositionBelow
    ptTop = dl.Top - lngPadding
    dl.Position = xlLabelPositionAbove
    If dl.Top + dlHeight + lngPadding > ptTop Then ptTop = dl.Top + dlHeight + lngPadding
    'Return DataLabel to original position
    .Top = dlTop
    .Left = dlLeft
End With
Pre2010_Position = dlWidth & "|" & dlHeight & "|" & ptLeft & "|" & ptTop

End Function

To test this, just select a DataLabel and run this:

Sub test()
Dim strPosition As String
Dim dl As DataLabel
Set dl = Selection

strPosition = Pre2010_Position(dl)

Debug.Print "dlWidth: " & Split(strPosition, "|")(0)
Debug.Print "dlHeight: " & Split(strPosition, "|")(1)
Debug.Print "ptLeft: " & Split(strPosition, "|")(2)
Debug.Print "ptTop: " & Split(strPosition, "|")(3)

End Sub

Note that I’ve got a couple of DoEvents in the Pre2010_Position routine. Without them, on my 2013 install it just doesn’t seem to work properly unless you step through the code one line at a time. Tedious, and annoying because you can see everything moving on the graph. But unavoidable, it seems. And tracking this down was what took the most time. Very frustrating.

For instance, without the DoEvents I get this:
dlWidth: 102
dlHeight: 51
ptLeft: 83
ptTop: 97

…whereas with them, I get this:
dlWidth: 102
dlHeight: 51
ptLeft: 83
ptTop: 64

Here’s my revamped LeaderLines file. Anyone with 2007 or earlier fancy taking this for a spin, and advising if it works?

Datalabel height and width for 2007 or earlier.

Over at Chart LeaderLines in Excel 2010 or earlier I posted some code that draws leader-lines on charts just like Excel 2013 does.

Unfortunately that title was misleading in regards to the or earlier bit: Eric said that the code isn’t working at all in XL07, and Jon Acampora advised that the DataLabel.Height and DataLabel.Width properties are not available in XL07.

Andy Pope had a crafty workaround for this:

The trick to getting datalabel width and height is to force the data label off of the chart by setting left and top to chartarea width and height. The data labels will not actually go out of the chart so by reading the new left and top properties you can calculate the differences.

So I whipped up some functions to get the datalabel height and width:

Function dlHeight_2010(dl As DataLabel)
    dlHeight_2010 = dl.Height
End Function

Function dlWidth_2010(dl As DataLabel)
    dlWidth_2010 = dl.Width
End Function

Function dlHeight_Pre2010(dl As DataLabel)
    Dim dlTop As Long
    dlTop = dl.Top
    dl.Top = ActiveChart.ChartArea.Height
    dlHeight_Pre2010 = dl.Top - ActiveChart.ChartArea.Top
    dl.Top = dlTop
End Function

Function dlwidth_Pre2010(dl As DataLabel)
Dim dlleft As Long
    dlleft = dl.Left
    dl.Left = ActiveChart.ChartArea.Width
    dlwidth_Pre2010 = dl.Left - ActiveChart.ChartArea.Left
    dl.Left = dlleft
End Function

They are all separate functions because if I lumped them together in one, it wouldn’t compile on pre-2010 machines. So I call these from the main code with this:

If Application.Version = 14 Then
  dlHeight_2010 dl
  dlWidth_2010 dl

ElseIf Application.Version < 14 Then
    dlHeight_Pre2010 dl
    dlwidth_Pre2010 dl
End If

I’ve updated my leaderlines code to use these. If I comment out the stuff relating to 2010 and force Excel to use the pre2010 functions then it seems to work perfectly. But I asked a buddy to try it in his 2007 installation, and he advises that it doesn’t work…it just deletes the chart leader lines without redrawing them.

Anyone with 2007 or earlier fancy taking this for a spin, and advising where I might have gone off the rails?

Mucho Gracious.
Leader lines_20140225 v7

Data Validation doesn’t care about volatility.

Huh. All these years I’ve been telling people to avoid volatile functions in models – especially in dropdowns because large chains of dependents usually hang off of these – and it turns out that I’m wrong in that specific case, as per Roberto’s comment in this thread.

If you use a volatile function to feed data validation, then the formulas downstream of that data validation cell only get recalculated when you select something new from the dropdown. That is, it behaves just like a non volatile function.

Goodbye clunky INDEX-based cascading dropdowns. Hello INDIRECT and OFFSET-driven cascading dropdowns.

Opening the Addin Dialog like a Pro

Back in the old days when Excel had menus and toolbars, a guy could use Alt+t+i to open the Addins dialog (Tools – Addins). But that would only work if there was an open workbook. No open workbook, no dialog. Now in the days of the Ribbon, the shortcut is Alt+f+t a a Alt+g (File – Options – Addins – Go). You don’t need to have a workbook open, which is nice, but there is a bit of delay between the two “a’s” in the keyboard sequence.

MS did a wonderful thing when they made the old 2003 menu navigation still work in later versions. Even though there’s no longer a Tools menu, you can still use Alt+t+i to open the dialog. Unfortunately you still need to have a workbook open for it to work. I can’t imagine why that is, but it is.

Well, it’s VBA to the rescue. You can show most any dialog with Applicaiton.Dialogs().Show. But showing the Addins dialog returns an error if there is not an active workbook, just like with the old menus. It’s trivial enough to fix, to wit:

Sub ShowAddinDialog()
    Dim wb As Workbook
    'Dialog won’t show if there’s no workbook showing
    If ActiveWorkbook Is Nothing Then
        Set wb = Workbooks.Add
    End If
    'Show addin dialog
    'Close wb if it was created
    On Error Resume Next
        wb.Close False
End Sub

That creates a new workbook if needed, then shows the dialog. It keeps track of whether it created a workbook and, if so, closes it without saving. Hardly worth your time to read this post, you say? You already knew about this, you say? Here’s the real magic. Those old 2003 commandbars still lurk behind the scenes in Excel. If you create new ones, they show up on the Add-ins tab. But you can modify the existing one too. I put this little gem in the Auto_Open macro in the same workbook as my ShowAddinDialog procedure.

With Application.CommandBars(1).Controls("Tools").Controls.Add(msoControlButton, , , 1)
    .Caption = "&I"
    .OnAction = "ShowAddinDialog"
End With

And then to clean it up in Auto_Close.

On Error Resume Next

Commandbars(1) is the menu and Controls(“Tools”) is the Tools menu. I add a new control to position 1 on that Tools menu. I don’t need a fancy caption because I can’t see it anyway. I just need a caption with I as the hotkey. Whichever letter follows the ampersand (&) is the hotkey. The built-in addins menu item has a caption of Add-&Ins... making I the hotkey for it. But mine is higher up, so it wins.

In the previous post I referenced above, I add this macro to the QAT. But the muscle memory of Alt+t+i dies hard. Rather than retrain myself like a normal person, I’m embracing my quirks. I can now use Alt+t+i and get the desired results.