Status Bar Every Nth Loop

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I just put a status bar within a loop. When I run it with the status
bar it takes 1 minute and 55 seconds. When I comment out the statusbar
logic it takes 6 seconds. I didn't realize how much memory the status
bar takes within large loops(10,000X244). Is there a way to only run
the status bar every nth loop?


AMax = 10000
BMax = 244
ReDim DataArray(1 To AMax, 1 To BMax)
StartTime = Now()
For A = 1 To AMax
For B = 1 To BMax
Randomize
RandomVar = Rnd()
DataArray(A, B) = RandomVar
Application.StatusBar = "Filling VBA Array " & (A * BMax -
(BMax - B)) / (AMax * BMax) * 100 & "%."
Next B
Next A
 
A very simple way would be to put the status bar update outside the B loop,
but within the A loop. Reduces the number or writes by 244.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
one way to dispaly on every 10th loop

if left(b,1)="0" then
Application.StatusBar = "Filling VBA Array " & (A * BMax - (BMax - B))
/ (AMax * BMax) * 100 & "%."
end if

you could also use select case statement
 
ExcelMonkey

You could try something like this to limit the number of times you update
the Status Bar

If A Mod 100 = 1 Then
Application.StatusBar = "Filling VBA Array " & _
(A * BMax - (BMax - B)) / (AMax * BMax) * 100 & "%."
End If

Regards

Trevor
 
Back
Top