Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Have tried below implementing "Displaying Progress% in Status Bar" as
recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Hi Jim,
Try: Sub RunABigOne() Dim x As Long For x = 1 To 10000 UpdateStatusBar x / 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "00%") End Sub Sub StopPC() Application.StatusBar = False End Sub I've moved the call to UpdateStatusBar into the loop and used the x variable in the argument. I've also amended the format. --- Regards, Norman "Jim May" wrote in message news:xoOsd.481$ln.305@lakeread06... Have tried below implementing "Displaying Progress% in Status Bar" as recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Try
Application.StatusBar = "Percent Completed: " & Format(PctDone, "###%") instead of Application.StatusBar = "Percent Completed: " & Format(PctDone, "100%") and add a line: UpdateStatusBar x / 10000 within the for.. ..next loop thus: Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" UpdateStatusBar x / 10000 Next x StopPC End Sub and to make the status bar update a little less frequently (and to speed up the macro): If x / 1000 = Int(x / 1000) Then UpdateStatusBar x / 10000 instead of: UpdateStatusBar x / 10000 Pascal "Jim May" wrote in message news:xoOsd.481$ln.305@lakeread06... Have tried below implementing "Displaying Progress% in Status Bar" as recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Thanks Norman;
I'll give it a go.. Jim "Norman Jones" wrote in message ... Hi Jim, Try: Sub RunABigOne() Dim x As Long For x = 1 To 10000 UpdateStatusBar x / 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "00%") End Sub Sub StopPC() Application.StatusBar = False End Sub I've moved the call to UpdateStatusBar into the loop and used the x variable in the argument. I've also amended the format. --- Regards, Norman "Jim May" wrote in message news:xoOsd.481$ln.305@lakeread06... Have tried below implementing "Displaying Progress% in Status Bar" as recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Pascal, thanks;
I'll check it out. Jim "P Daulton" wrote in message ... Try Application.StatusBar = "Percent Completed: " & Format(PctDone, "###%") instead of Application.StatusBar = "Percent Completed: " & Format(PctDone, "100%") and add a line: UpdateStatusBar x / 10000 within the for.. ..next loop thus: Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" UpdateStatusBar x / 10000 Next x StopPC End Sub and to make the status bar update a little less frequently (and to speed up the macro): If x / 1000 = Int(x / 1000) Then UpdateStatusBar x / 10000 instead of: UpdateStatusBar x / 10000 Pascal "Jim May" wrote in message news:xoOsd.481$ln.305@lakeread06... Have tried below implementing "Displaying Progress% in Status Bar" as recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Jim,
Perennial problem of progress bars is knowing what point in the loop you are. Your example is very simple because you have a very fixed boundary, 100000, so it is easy to calculate the position, but in real world examples it is not so clear cut. As the objective is to show something is happening, it is the visual feedback that is important. Because of this, what I tend to do is call a progress bar routine many times that goes from 0% to 100% relatively quickly, doing this from several points that are 'self-contained'. That way, you provide feedback regularly, you even get away with no progress showing when moving from one code section to another, and coding is more manageable. There are still elements of trial and error in the code, but less fine tuning. -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:xoOsd.481$ln.305@lakeread06... Have tried below implementing "Displaying Progress% in Status Bar" as recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
Thanks Bob for the added info...
Jim "Bob Phillips" wrote in message ... Jim, Perennial problem of progress bars is knowing what point in the loop you are. Your example is very simple because you have a very fixed boundary, 100000, so it is easy to calculate the position, but in real world examples it is not so clear cut. As the objective is to show something is happening, it is the visual feedback that is important. Because of this, what I tend to do is call a progress bar routine many times that goes from 0% to 100% relatively quickly, doing this from several points that are 'self-contained'. That way, you provide feedback regularly, you even get away with no progress showing when moving from one code section to another, and coding is more manageable. There are still elements of trial and error in the code, but less fine tuning. -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:xoOsd.481$ln.305@lakeread06... Have tried below implementing "Displaying Progress% in Status Bar" as recommended in one of J Walkenbach's books ("How to" was a bit brief...) any way as Macro RunABigOne runs the % remains unchanged at 100% << probably what I've told it to do. Can someone assist me in correcting to what is intended? TIA, Sub RunABigOne() UpdateStatusBar 0 For x = 1 To 10000 Cells(x, "C").Value = "This is a test" Next x StopPC End Sub Sub UpdateStatusBar(PctDone) Application.StatusBar = _ "Percent Completed: " & Format(PctDone, "100%") End Sub Sub StopPC() Application.StatusBar = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |