VBA query (Excel)

[Jim Gamma]

I've recently had to create a few large VBA Excel applications, all of which take some time to run. However, I've noticed that holding down the ALT key while it's running speeds it up. (I have a counter in the Status Bar, which increases much faster with the ALT key than without it. I've tried entire runs with and without the key pressed, with exactly the same input data, and it's always faster. A LOT faster.)

My question is, why would this be? And how can I replicate the effect without holding down the Alt key?

 

[USS KG5]

Are you sure you are still getting the correct results?

 

[Jim Gamma]

I believe so - why, what does it do?

 

[USS KG5]

I believe so - why, what does it do?

I had a quick google and other people seem to have had similar weird experiences with the alt key, causing errors etc when alt-tabbing or similar.

I code with vba quite a lot and i'm not sure what is happening here - you see the alt key is an odd one to choose as it serves so many other functions in Windows!

 

[Allyn Gibson]

One thing you can do to speed up an Excel macro is to add the following line:

Application.ScreenUpdating = False

I've never heard about the "Alt" key trick. I think I'll try it myself.

 

[Jim Gamma]

Hmm... well I've not had any obvious errors...

I did actually first notice it while alt-tabbing. I held my finger on alt whilst I checked something I was reading, then noticed that the counter was whizzing along, which it doesn't usually do.

If I'm on another application and press alt, I don't get the same speed increase, so clearly it's a "feature" of Excel.

EDIT: Just double-checked, no difference in the actual results.

 

[USS KG5]

If anyone knows anyone at Microsoft it might be worth asking - it is certainly weird.

Is it possible that Alt bypasses screen updates? This might make the application considerably quicker (all that flashing about on the cells must use a few cycles).

 

[Jim Gamma]

Possibly - although I don't actually do any screen switching. My code would be of the form:

Dim Startpoint As Range, i As Integer
Set Startpoint = Range("Sheetname!A1")
For i = 0 To 9
    Startpoint.Offset(i, 0).value = i
    ' etc etc etc
Next i

(using direct range allocation)

Usually I have focus on a different worksheet whilst this is going on (hence the use of the sheet name in the range assignment).

 

[USS KG5]

Looks like a pretty standard loop then - I'll be very interested if anyone knows about what "Alt" does to your sheet here...