VBA & Slow Performance of Macro

  • Thread starter Thread starter Greg G
  • Start date Start date
G

Greg G

Excel 2002

I am dumping an array into a excel sheet via for-next
loop. I have screen updating turned off. I have
pagebreaks turned off. No addins are active. This
process used to take 2 min or so now it takes about 60
min. The array sized has not changed (1500x78) can
anyone help with possible causes and solutions. Thank you
in advance.

Greg
 
Hi Greg
maybe you can post your macro: One idea: Maybe the used range is larger
than before and you have a dynamic range in your macro?
 
The Array is fixed at 1500,78
Here is that section of the code pretty straight forward

Dim EssbaseArray(1500,78) 'The Array is already loaded at
this point.
for x = 1 to 1500
for y = 1 to 78
cells(x,y).value = EssbaseArray(x,y)
next y
Next x
 
Hi
another idea: Also turn of autocalculation within your macro. If you
have many formulas in your sheet/workbook this could cause the delay
 
Thanks I will try but that is a variable that has not
changed to my knowledge but I'll update the code to
override.

if you think of anything else let me know.

Much appreciate the efforts

GG
 
Thanks charles that Helps me I've been looking for that
type of command. Not sure if it solved my issue as
Franks suggestion about auto calcs helped tremendously.
But i am excited about your advice and it is already in
the code.
 
I think that did it. I had a bunch of Vlookups to the
array. I appreciate it, always the small things that get
ya.

:)

GG
 
Hi,

How about posting the contents of the array in one block rather than
using a For ... loop? Something like this perhaps:

Sub Array_Test()
Dim y As Variant
Dim l As Long, m As Long

ReDim y(1 To 1500, 1 To 78)
For l = 1 To 500
For m = 1 To 78
y(l, m) = Int(Rnd() * 1000)
Next m
Next l
'populate array with random numbers

With Sheet1
.Range(.Cells(1, 1), .Cells(1, 1).Resize(UBound(y, 1),
UBound(y, 2))).Value = y
End With
'pass array values to appropriately sized range

End Sub

You could also set Calculation to manual and ScreenUpdating to False
at the start of the routine.
 
Back
Top