Anyone else having inexplicable bugs with Excel 2013?

[ClimoC]

I've been programming for Excel since about 2002, and have never run into problems quite like these.

Some users in our office are moving over to 2013, and from minute one, problems are starting to show.

The core of the WB is that it's an ODBC connection to a database (Query Table), and then change_events in the sheet either fire off data writes to the server, or selectionchange_events validate the data types allowed.

Some weird behaviours I've started seeing:

Slow - like really slow. 2010 will open the workbook from a server, run login scripts, update 6 query tables, and be ready for user-input within about 5 seconds. 2013? 15 seconds to load.

Screen Freezes - despite my testing post-script run, (ScreenUpdating, EnableEvents, Calculation all return as 'True') - the screen stops drawing. Windows seems to be fine, but Excel is neither 'Not Responding' nor can you interact with it in anyway other than to kill Excel or minimise it. Minimising the window and reopening it causes it to hang for a second, and then resume fine as if nothing had ever happened.

Random(?) Range.Validation error - As above with the selection_change events, some fields present a dropdown when a cell in that field is clicked on. I get 'Application or object defined error'. Clicking resume on the code and it runs just fine, and then doesn't present a problem for the next 5-20 clicks. Then it happens again.

And does anyone know how to turn off their 'sexy' Selection-Cursor tails when jumping cells? I'm sure it's unnecessary overhead

 

[DataAnalyst]

Hi, I'm not programming like you, but ever since Excel 2007 it has seemed bug-ridden. Even worse, my boss' Excel bugs are different from mine.
In the interest of time management and so I won't go insane from frustration, I've learned to work around the bugs. So the correctly typed formula still won't work? FINE! I'll delete it and start over. THAT will show Excel!!! I do this with pivot tables also. It's much easier to delete and re-create a pivot table than to try to work with a buggy one.
I make a pivot report in which I filter by associate name to make individual report. The pivot won't hold the formatting of the top rows and column headers, so I make a macro to re-do this formatting for each one. Finding solutions like this helps. Don't try to work through the bugs, ignore or delete them and curse out the MS programmers under your breath...
Hope this helps!
Oh, and when I first got this Excel I turned off all the animation options and that really seemed to help. Good luck!

 

[mole999]

be glad its not 2016

 

[DataAnalyst]

A good example of what I mean came up just yesterday. I was working with data sent to me for Jan.-June 2015 and includes several text fields, some date fields and an alpha-numeric code field.

I wanted to make a pivot to show certain codes for each associate. First column associate name, then posting date, then code field, then code description. Then two value fields, one a count and the other a sum of productivity credit.

I grouped the date field by month, then filtered the code field to show about 12 codes out of hundreds.

It showed only January - May. I could not make it bring in June. I tried re-formatting and re-copying the dates, nothing. I re-made the pivot and on the third or fourth try noticed June didn't go away when I grouped the dates, it was when I filtered the alpha-numeric codes.

I re-formatted that field as text. I tried re-copying to a new, pre-formatted column. Still no June.

Solving Excel glitches is not my assigned work. My boss doesn't want me to spend days on such things. I spent too long on this and reluctantly decided to put the June data in a separate pivot and combine them manually. While I was getting ready to leave yesterday I thought of something I hadn't tried: make a duplicate code field and put it in the filter section of the pivot.

This morning I finished the project manually so my boss had it for a meeting. While doing this I pulled similar data from a different database and did the same thing to it - and June came in like it's supposed to. The only difference was there was one value field instead of two.

Then I tried my idea of a second code field in the problem data set, in the filter section of the pivot.

Still no June!

This is what I mean... I could spend my life trying to fix this, or find a work-around. Maybe one day MS will care enough about its users to fix these things.

If anyone knows why this happened and how to fix it, it would be good to know for next time... I looked online and didn't find much...

Good luck everyone!

 

[mole999]

maybe this, in a helper column point to the date cell and use =--date cell (two minus signs), then build the pivot

 

[DataAnalyst]

maybe this, in a helper column point to the date cell and use =--date cell (two minus signs), then build the pivot

Thanks! I'll try that next time.

 

[Zack Barresse]

There are some known issues with 2013 performance, especially compared to 2010. There were several changes which occurred in 2013, some of which to handle the new connection/data type(s) infrastructure. It's usually recommended you redo all of your query tables if going up to 2013 or beyond.

As far as Excel 2016 is concerned, I've found much better performance than 2013, but still some oddities here and there. Personally I still like 2010 the best for performance and stability, then 2016, then 2013, skip 2007, then 2003...