Status Bar Every Nth Loop
 

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


---
Message posted from http://www.ExcelForum.com/

Status Bar Every Nth Loop
 

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)

"ExcelMonkey " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Status Bar Every Nth Loop
 

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


---
Message posted from http://www.ExcelForum.com/

Status Bar Every Nth Loop
 

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


"ExcelMonkey " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/