When Speed Counts...

[thereuare]

The following sub places numbers in columnA from Row1 to Row3000, where BoxSize is assigned from an earlier InputBox. Is there a faster way to do this, it surprisingly takes longer than i think it should:

For Count = 1 To 3000
Range("A1").Select
ActiveCell.Offset(3000 - Count, 0) = x + BoxSize
x = x + BoxSize
Next Count


Also, which of the following lines of code would be executed faster, they both do the same thing:

If CheckValue <= (CurrentBoxValue - (Reversal * BoxSize)) Then OtherSub

OR

If CheckValue<=(CurrentBoxValue-ActiveCell.Offset(z,-Reversal).Value) Then OtherSub

I'm not 100% the second code even works, but you get an idea of the syntax i want to use. Would one of these be faster than the other? (ie- the 1st code actuals performs math functions, the second looks up the value in a cell).

Thanks for any insight.

 

[Richie(UK)]

Hi,

Have you tried:

Application.Calculation = xlCalculationManual
'Your code here.
Application.Calculation = xlCalculationAutomatic

HTH

EDIT Also consider Application.ScreenUpdating=False

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-09-06 07:07

 

[thereuare]

Not familiar with either of those, could you give bried explanation of what it does and how it works?

Thanks.

Also, any idea of the bottom question, if one of those codes has faster/slower execution?

 

[Richie(UK)]

Hi,

From the VBE Help files:
Screenupdating:
True if screen updating is turned on. Read/write Boolean.

Remarks

Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
Remember to set the ScreenUpdating property back to True when your macro ends (older versions of Microsoft Excel automatically reset this property, but Microsoft Excel 97 does not).

And for calculation:
This basically stops Excel performing each and every calculation until the routine is finished (when you switch back to automatic). This is especially useful in large loops.

Give each one a try (or even both) and see if it helps.

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-09-06 08:53

 

[Tommy Bak]

one thing I know can make this faster is to eliminate the selection of A1

For Count = 1 To 3000
Range("A1").Offset(3000 - Count, 0) = x + BoxSize
x = x + BoxSize
Next Count


regards Tommy