Please help! Excel 2010 VBA insanely slow on cell value assignment

[dtsolutions]

There are plenty of Google hits for this issue, some from this site, but I can't find any real solution, just the same old stale VBA advice-bites (my apologies if I have been lazy and missed the solution already cached on excelforum.com).

I'm reducing this to the simplest elements that come to mind, although the issue seems to occur in far more general contexts: Excel 2010 versus 2007, incredibly simple macro assigning cell values. Macro is running behind a brand new xlsm workbook, 1 worksheet, created in Excel 2010. All the macro does is assign cell values: .Cells(j, 2).Value = .Cells(j, 1).Value, 500 times.

Time to run in Excel 2007: 0.08 seconds (roughly as expected).

Time to run in Excel 2010: 16+ seconds running the first time on opening the workbook, 5+ seconds for subsequent runs (insane).

The computers I'm using are all modern and powerful enough so hardware is a non-issue. I've been using the workstation running Excel 2010 (and Win8.1) for more than a year with no Excel problems except this one ... I'm a super-ultra-extreme VBA power user running awesomely complex spreadsheets with horrendously extreme calculation loads, and the Excel 2010 box handles everything great (fastest box I have) except when it comes to issues like this ("messing around with cells", to put it generically).

I think I've tried just about every Application-level setting and Workbook-level setting that might apply, but hopefully I've missed something very simple you folks can point out.

Sample code:


Sub test_Excel10()
Dim j As Long, xTime As Double

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'Application.EnableEvents = False
'Application.DisplayAlerts = False
'Application.AutomationSecurity = msoAutomationSecurityForceDisable

xTime = Timer
With Worksheets(1)
For j = 1 To 500
.Cells(j, 2).Value = .Cells(j, 1).Value
Next j
End With
xTime = Timer - xTime
MsgBox "Time = " & Format$(xTime, "0.00")

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'Application.EnableEvents = True
'Application.DisplayAlerts = True
'Application.AutomationSecurity = msoAutomationSecurityLow
End Sub

[AB33]

dtsolutions,
Please use code tags with your code as per forum's rule.
I have run the code on my Laptop, excel 2010. It took 5.96seconds to run.

[dtsolutions]

Thanks for the post AB33, and thanks for the heads up on code tags. Ok, so from your results I conclude (1) I am not hallucinating this issue and (2) I don't have some really dumb problem like a corrupted Excel install. I await the solution ...

[AB33]

Just curiously! Are your data imported from web site or an accounting application, such as SAP, or Oracle?

[dtsolutions]

Umm ... was that post supposed to be to a different thread? Can't recall mentioning data ...

[Norie]

I just ran the code - 0.03 secs.

[Izandol]

0.08

Do you have add-ins loaded?

[dtsolutions]

Just my usual add-ins ... 2x Analysis Toolpack and 2 of my own add-ins that are very well-behaved. Anyway I just uninstalled all 4 of these and tried again - got the same results.

[jmac1947]

I just ran the posted code, excel 2010 display says "Time = 0.01"

[dtsolutions]

AB33, is your 5-second time reproducible? If so, any ideas on why we may be the only 2 people in the universe with this issue?

[Izandol]

Do you have same problem with Excel in Safe Mode?

[dtsolutions]

Thanks for the suggestion Izandol ... I get the same results in Safe Mode

[jmac1947]

@dtsolutions,

Do you get the same results if you run the code on a different machine??

[dtsolutions]

That's the first thing I would have tried if I could have found another Excel 2010 install. To do another install I would have to inflict Excel 2010 on a box I am using with Excel 2007, or do an install on an old WinXP box. Both of those would be last resorts after I try uninstall/reinstall on the Win8 box ... I was hoping for an easier solution courtesy of you gurus.

[Izandol]

Is any difference with:

Please Login or Register  to view this content.

at start?

[AB33]

Umm ... was that post supposed to be to a different thread? Can't recall mentioning data ...

The type of data you are working on could affect the speed of the code, hence my reason for asking you a question.
I have been experiencing with this issue in this forum and at work. Data imported in excel from outside sources tend to have unseen direct characters. If you work on these data with out first cleaning them, the process of execution is very slow.

[swoop99]

Just in case this is relevant.....I had an entire Excel app suddenly decide to start running macros very slowly just a few days ago. The cause turned out to be a temp sheet being used that, while not in use, is blank. Deleting and recreating the blank worksheet cured the problem. Don't ask me why.

The same thing happened a couple of years ago, there was a thread on here with all kinds of helpful people looking at why my code was suddenly running so slow......and in the finish I had to go back to an earlier version in which the problem didn't exist.

[dtsolutions]

Thanks for the suggestion Izandol ... I tried it; there was no change.

[Izandol]

I think only idea I have left is run Windows in safe mode and try again.

As reference I have tested again on different computer and time is 0.04.

[dtsolutions]

Izandol, I wasn't aware that Excel would run macros in Windows safe mode ... will try it when my computer is idle this afternoon.

AB33, there is no data involved as I'm isolating the issue to a brand new, blank spreadsheet which is the only thing open in Excel.

AB33, am I correct in reading your first post as saying that you can reproduce the issue? If so then this seems to be the best chance for keeping this thread alive ...

[AB33]

I re-run it and got 0.04

[dtsolutions]

Thanks AB33 ... ok next step is to uninstall/reinstall Excel 2010 this afternoon. In the meantime I welcome all flashes of brilliance from the gurus.

[dtsolutions]

Ok, now I am depressed ... I uninstalled and reinstalled Excel 2010 on the Win8.1 box ... no change. I installed Excel 2010 (from the same disk) on a WinXP box ... no problems, the code was blazing fast. The only thing that bugged me was that Office remembered all my settings on the Win8.1 box, so I wasn't able to test with a completely fresh install ... but anyway I think I've already checked all the Excel options that might apply(?)

To round things out, I also tested with Norton completely shut off ... no change.

I tried testing in Windows safe mode, but Excel wouldn't even start properly - apparently Microsoft thought my software was pirated or something.

Can I ask all readers of this post who tested the code themselves: Did any of you test with Win8.1 64-bit, Excel 2010 32-bit?

[Norie]

I tested on Win8.1, 64bit, Excel 2010 32-bit.

[jmac1947]

Win 7 pro, SP1, excel 32 bit 2010

[dtsolutions]

Update: this may be a problem with video hardware, or perhaps software. Right now I can't isolate the video card from the motherboard graphics for technical reasons - Dell is sending me a new motherboard (which I'm not at all convinced I need) before they will send a new video card ... eventually I will be able to isolate and test and post the results.

In the meantime, some of you may find these results as amusing as I did (and please comment if they spark any ideas or conclusions!):

1. First, I modified the code in the original post so the macro only operates on rows 500 - 1000 instead of rows 1-500 ... I did this so the macro would NOT be doing anything with any visible cells.

2. Then I started fiddling with the size of the Excel window ...

3. Visible columns test: 45 rows visible. Time with 15 columns visible: 5.5 seconds ... time with 14 columns visible: 0.02 seconds (!)

4. Visible rows test: 28 columns visible: Time with 11 rows visible: 5.2 seconds ... time with 10 rows visible: 0.01 seconds (!!)

Seriously, have any of you ever seen anything so ridiculous?

[Izandol]

Which sheet view do you use - Normal, Page Layout or Page Break Preview? Is any difference between them?

[dtsolutions]

I've been using "Normal" throughout this experiment. I just tried Page Layout and Page Break Preview and there was no change, except for a few tenths of a second out of 5-6 seconds.

[dtsolutions]

Problem solved: Folks, it was the video, almost certainly a graphics card hardware issue and not the video drivers. I installed a new video card and the problem was solved immediately.

Sorry for clogging up this forum with a hardware issue ... but on the other hand, it seems like a worthwhile bit of information for you Excel gurus to know: A graphics hardware issue can cause a very extreme issue with Excel while not causing any other noticable issues on the computer.