Category: Bugs

Excel 2013 v Excel 2010 Speed Test

Posted on by Dick Kusleika

There are a lot of good comments on the Excel 2013 Is Unreasonably Slow post, but none of them worked for me. Even a generous offer from keepItCool, but I can’t send the problem child due to proprietary information. So I’m trying to demonstrate the problem in a simpler fashion.

I create a template and insert it into a new workbook five times. This is more or less what my other code is doing. Actually it’s quite a bit less and the templates in the other code are a lot fatter. But I think it demonstrates the point.

Split Excel 2013 (Home) Excel 2010 (Home) Excel 2013 (Office, Local) Excel 2013 (Office, Network)
Start 0.75 0.55 0.66 2.82
Create template 257.68 161.60 445.96 562.40
Insert template 1 449.85 246.10 739.31 927.03
Insert template 2 638.71 345.13 1,058.17 1,292.53
Insert template 3 832.92 429.95 1,367.04 1,716.61
Insert template 4 1,030.99 517.06 1,696.34 2,109.36
Insert template 5 1,247.35 611.32 2,023.30 2,467.81
End 1,247.63 611.70 2,024.43 2,523.20

Excel home is my machine. I have both versions installed. The office times are where my original code runs in production. The “Local” times are when the code is on the desktop and the “Network” time is when the code is on a server.

There’s a lot I’m not happy about here. If I could cut the 2013 times in half, I would be not-unhappy. Note also that I’m not protecting or unprotecting anything – a known change in 2013 that slows things down.

Give it a try, if you like, and let me know what your times are. The home hardware is Intel i7 860 @ 2.8Ghz with 8GB RAM / Windows 7 64 bit, Excel 32 bit.

You can download TemplateInsertTimeTest.xlsm

Excel 2013 VBA Unreasonably Slow

Posted on by Dick Kusleika

I’m not the kind of guy to upgrade too soon. I spend a lot of time in VBA, so new features in Excel don’t generally inspire me. Recently a client of mine upgraded to 2013 with, let’s say, disasterous consequences.

First, there’s the Single Document Interface (SDI). That’s where every document is in it’s own application container. But not really. It’s all depends on where you draw the line. If you have two linked documents open and you calculate, both documents go into the calculation tree. So it’s not exactly separate application containers. Its main purpose is to allow you to put one spreadsheet on one monitor and a different spreadsheet on another monitor. A worthy goal.

My problem with SDI is that I was still using Excel 2003 menus for five addins. They don’t work so well in SDI. It forced me to rewrite them using the RibbonUI – not a bad thing, but my client probably wasn’t including that cost in the cost of upgrade. But we have fancy new ribbon icons, so all is well, right?

Next I learn that Excel uses SHA1 to encrypt spreadsheets instead of the previous method, which I assume was some sort of XOR with a one-character password. With the new encryption scheme, protecting and unprotecting a worksheet in code takes a touch longer. And by touch a mean a shit-ton.

I don’t know what exactly to do about the protection problem. My code opens templates, unprotects them, writes stuff, combines them into a final report, and reprotects them. The first thing I did was get stingy about my protection. Whereas before I was pretty liberal, protecting and unprotecting whenever I needed to, now I’m unprotecting and reprotecting exactly one time. That made a little difference, but not enough. As an experiment, I removed all protection from the templates and removed all the unprotecting and reprotecting from my code. Here’s the before and after timing of that.

Process Cum Time ThisProcess Description
Start 0
End 18,700.37 18,700.37 Current 2013
Start –
End 10,149.81 10,149.81 No protection 2013

That cuts it in half, although I can’t release this into the wild with no protection. My client reports that it takes ~90 seconds on his machine. Not surprisingly, my desktop has better specs than his company-issued machine. But I think it all scales. I can cut his time down to ~45 seconds by removing the protection. Even if we could stomach having unprotected templates, that’s not good enough.

I sprinkled some splits around the code to see if there was one area causing the problem. Then I ran the same code in Excel 2010. There must be something else in 2013 that’s taking a disproportionately long time compared to 2010.

Process Cum Time ThisProcess Description
Start –
Open Templates 1,544.60 1,544.60
Fill and validate dump 1,630.98 86.38
Dump page setup 1,763.43 132.45
Store dump properties 2,062.55 299.12
Fill solutions 2,186.50 123.95
Fill waves 4,380.67 2,194.16
Sort Solutions 4,388.46 7.79
Fill Finals 9,937.29 5,548.83
End 9,937.60 0.31 No protection splits 2013
Start –
Open Templates 513.72 513.72
Fill and validate dump 547.94 34.22
Dump page setup 571.68 23.74
Store dump properties 666.54 94.86
Fill solutions 735.07 68.54
Fill waves 2,146.86 1,411.78
Sort Solutions 2,154.69 7.83
Fill Finals 4,288.38 2,133.69
End 4,288.79 0.40 No protection split 2010

Nope. Everything just takes longer as far as I can tell. I’m going to increase my number of splits, but I’m not hopeful I’ll find the golden key.

Have you experienced performance problems with 2013? What caused them? How did you solve it?

Recent Update of Office causes problems with ActiveX controls

Posted on by jkpieterse

Hi,

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.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

#####UPDATE Dec 22, 2014#####
Microsoft has published a so-called Fixit to make resolving this matter easier:
https://support.microsoft.com/kb/3025036/EN-US?wa=wsignin1.0

Learning from my Errors

Posted on by Jeff Weir

There’s an annoying bug in VBA whereby if you’re trying to change the .visible status of a PivotItem, and if the PivotField had a number format set to General, and if you live in New Zealand, then you’re out of luck:

Unable to set visible property

If you live in the US however, you’ll be fine. No error for you.

Don’t believe me? Either change your Windows region to New Zealand and run this code, or swing down to my place and see it for your own eyes. Here’s where you’ll find me:

247 Rintoul Street

(Aside: Check out those awesome ocean views. Why if it wasn’t for that annoying continent-sized lump of Uranium and Gold Ore off to the West, we’d pretty much have 365 degree views of the entire Pacific. Fortunately they’re busy bulldozing that annoying outcrop and shipping it off to uranium reactors and jewelery stores across the globe. So we should have a completely unfettered view in 2 billion years or so).

Ok, so this issue isn’t just an issue for New Zealanders…it actually affects any place where you haven’t got your Windows ‘region’ set to US, with New Zealand being the only place where I’ve actually encountered such egocentric behavior to date. (I don’t get out much. Or rather, they don’t let me out much. Or rather they make it clear that I can go out, but I can’t come back in.)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

This nasty bug caused quite a bit of workaround in my FilterPivot routine. I used to do this horrible check on every single item in a potentially exhaustively long list of PivotItems in order to avoid the possibility of an error caused by this unlikely combination occurring:

If Not IsNumeric(Pi.Value) Then
'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
If IsDate(Pi.Value) Then
If Not bDateWarning Then
On Error GoTo ErrHandler
Err.Raise Number:=997, Description:="Can't filter dates"
On Error Resume Next
End If
Else: Pi.Visible = True
End If
Else: Pi.Visible = True
End If

But prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

So now I can just do this:
pi.Visible = True

Or rather, I could just do that if everyone had Excel 2013. But they don’t. So I can’t. I still have to somehow catch this error. And as written above, my code rather inefficiently looks for possible trouble caused by a combination of things that is probably unlikely to occur. (I mean, how many people would dare to have their Windows region set to a non-US region while trying to filter a PivotItem that happens to be a date in a PivotField that happens to have a General format?) All that preemptive error checking can’t be good for business.

The error of my ways?

I’m sure you’ve already seen what looks to be like the error of my ways… why bother checking for errors just so I can avoid them? Why not embrase them: just plow ahead, and if the s#!t hits the fan, just deal with it. Something like this:

On Error Goto Errhandler
pi.Visible = True

'some other code

ErrHandler:
If Err.Number <> 0 Then
Select Case Err.Number
Case 1004 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
i = i + 1
ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
strDateItems(i) = pi.Value
End If
Case Else 'Some other error code here
End

Great: now that bug fix code in the Errhandler only gets fired up in the rare event that it is actually triggered. Sure beats neurotically checking each and every PivotItem to see if it might cause an issue.

Unfortunately in this particular case the code snippet in question sits within a larger loop, and the code immediately before needs to have an On Error Resume Next statement applied. That’s because in order to work out whether a PivotItem should be hidden, I’m adding it to a Dictionary object that also contains my desired filter terms, in order to see if it matches any of those filter terms. Which looks something like this:

On Error Resume Next
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number <> 0 Then
pi.visible = true
...

So I’d need to put an On Error Goto Errhandler before the pi.Visible = True bit so that my bug fix code in Errhandler would get triggerred, and an On Error Resume Next bit after it, so that the Dictionary test occurs for the very next item. And those will get executed for every single PivotItem – which kind of defeats the efficiency ‘dividend’ of putting my handling code within Errhandler. So I figure I might as well just do this:


pi.Visible = True
If Err.Number = 1004 Then 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
i = i + 1
ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
strDateItems(i) = pi.Value
End If
End If

Two steps forward, one step backwards.

Maybe I shouldn’t have that On Error Resume Next in there in the first place…maybe I should catch errors from the Dictionary.add in Errhandler too, or even do the dictionary check in another procedure – something that Dick mentions here. Anyone got any advice here?

All I know is that this is a lot of work-around for a combination that is pretty unlikely, but potentially fatal to someone’s analysis.

Indecent Add-in Exposure

Posted on by Dick Kusleika

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?

A date with PivotItems

Posted on by Jeff Weir

Howdy, folks. Jeff here again. I’ve been sharpening up some code to manually filter a PivotField based on an external list in a range outside the PivotTable. It works blazingly fast. Unless there’s dates as well as non-dates in my PivotField. In which case it errors out.

Try this:
Put “Pivot” in A1
Put =TODAY() in A2
Put =VALUE(TODAY()) in A3

Now make a PivotTable out of that data.

Pivot_DDOE

Now put this code into the VBE and step through it:

If the same thing happens to you as happens to me, you will either be speaking aloud the title of this routine, or you will be speaking aloud this:
Unable to get the PivotItems property of the PivotField class.

Go type these in the immediate pane:

What the…?

Now try these:

So it seems can’t do certain stuff to a PivotItem if that PivotItem is a date but your PivotField number format is set to General.

That’s weird.

Equally weird, try this:
Select the PivotTable, and record a macro as you change it’s number format back to General.

What the …? Change the PivotField Number Format, and you get a macro that tells you that you changed the PivotField name!

So what happens if you run that macro? Well, it changes the name of the PivotField:
PivotField_20131110

It does nothing to the number format.

Strangely enough, I found some info on this problem at one of my most revisited blogposts that I had somehow missed: Jon Peltier’s Referencing Pivot Table Ranges in VBA

Stranger still, the answer was by Jon Peltier back in 2009 in relation to a question asked by….wait for it…me. Don’t know how I missed that. Must have been sleeping.
So I’ve come across this problem before, found an answer, completely forgotten about it, and then wasted 2 days trying to work out what the problem was, purely so I could Google my own answered question.

I’m going to read through all 238 ( and counting) comments in that thread and see what else Jon has told me over the years I’ve been learning VBA.
There’s also something on this at stackoverflow

Jeff

–edit–
Jon’s method was to loop through the pivot items, and compare the pivot item caption to what he was looking for:

But now I know it’s probably easier just to change the format of the PivotField.

—edit 30 May 2014 —
Prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

— edit over —

PivotItems Are Wrong when Calculated Field Is Present

Posted on by Dick Kusleika

I have a procedure where I loop through a pivot table and create a general journal entry to be imported. I used the same procedure to create another journal entry, but with one change. For this new journal entry, I wanted to round all of the amounts to the nearest $10. They were estimates and I don’t like to post estimates to the penny because it implies a precision that just isn’t there.

To accomplish this seemingly simple task, I created a calculated field and adjusted the procedure to pull from that field. There was just one problem: my debits didn’t equal my credits! I know, I gasped too.

It turns out that looping through the pivot table with For Each pi In pf.PivotItems was starting on the row below the first pivot item and ending on the row below the last pivot item. It was offset one row.

I was puzzled for quite a while. The old procedure worked fine. Then it dawned on me that I had added a calculated field. When I removed the calculated field, it worked as expected. So I modified the procedure to do the rounding in VBA.

Next, I wanted to see if this was a fluke. I generated some sample data, namely First Name, Last Name, and City. Then I created a pivot table with City in the Row Labels section and Count of Last in the Value section. Finally, I created a calculated field expertly named Field1.

The calculated field is set to zero. It’s not really calculating anything. I added that to the Values section.

Now to test. I wrote some code to demonstrate that the PivotItems were pointing to the wrong cells. As part of my code, I wanted to remove the calculated field. I kept getting errors trying to remove the calculated field so I recorded a macro to see how it’s supposed to be done.

Yep, that’s what I was trying to do. Only that doesn’t work. So I found a work around. I love finding bugs when I’m investigating other bugs.

Here’s the code:

And here’s the result:

Above the dashed line is what happens when calculated field is present. The PivotItem, which is in A4, shows A5 as its LabelRange. When I search for its Value, however, I get A4. When I removed the calculated field, it all works as expected.