Excel code - why so slow?

[amberH]

Hi all,
I'm hoping someone can help me with this. For some reason my code is REALLY slow.
I'm pasting a few lines below:

Sheets("paper-data&quot.Cells(1, 1).Value = Yr
Sheets("paper-data&quot.Cells(2, 1).Value = monStart
Sheets("paper-data&quot.Cells(3, 1).Value = monEnd


Now earlier in the code I have values assigned to Yr, monStart, and monEnd...there is nothing fancy about this, yet it takes around 2 minutes to step through each of the above lines of code. I can't figure out why.
Any suggestions?
Thanks,
amber

 

[Zathras]

There's something you're not telling us. Even in the worst case with 255 sheets (with "paper-data" at the end) and using variants instead of integers the code you indicate takes less than 1 second to execute on my computer. What else is going on?

 

[Nikita6003]

Hiya,

I've had problems with some fairly big spreadsheets as well; turned out Excel was recalculating all the time. I now have the code set Calculation to Manual at the beginning, & do a single recalculate & switch calculation back to its initial setting when I'm done processing.

    Dim l_iCalcMode As Integer
    
    'Save initial Calculation setting
    l_iCalcMode = Application.Calculation
    
    Application.Calculation = xlCalculationManual
    
    
    'Your code stuff here ...
    
    'Force a calculation
    Application.Calculate
    
    'Set back to initial calculations etting
    Application.Calculation = l_iCalcMode

Might help ...

Cheers
Nikki