Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems There are 2 methods, a simple version DisplaySimpleProgressBarStep that defaults to ' 20% Complete' and a more generalized version DisplayProgressBarStep that takes a laundry list of optional arguments so that you can make it look like just about anything you wish. Here is a generalized version that I wrote. I needed an Excel VBA Progress Bar and found this link: Excel VBA StatusBar. I know this is an old thread but I had asked a similar question not knowing about this one. I'm not sure how practical this solution is, but it might look somewhat more 'official' than other methods stated here. SendMessage(hwndPB, PBM_SETRANGE, 0, MAKELPARAM(0, 100)) MAKELPARAM = loWord Or (&H10000 * hiWord) hwndParent = FindWindowEx(Application.hwnd,"MsoCommandBar","Status Bar")Īfter the progress bar has been created you must use SendMessage() to interact with the progress bar: Function MAKELPARAM(ByVal loWord As Integer, ByVal hiWord As Integer) This should therefore be relatively simple using FindWindowEx() function. For that one could use the status bar, or a custom form! Here's the window structure of Excel found from Spy++: HwndParent Should be set to the parent window.
' back to pass through the bar length calculation
#Bar access code#
' Your production code would go here and cycle ' This sample code automatically runs 1 to 100 ' ' Set intMax to your total number of passes ' ' This section is where you can use your own ' Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)īar1.Tag = Bar1.Width ' Memorize initial/maximum width
#Bar access software#
' remove after software testing is complete ' This is used to create a delay to prevent memory overflow Then add this code to UserForm1: = Attach the following code to UserForm1 = Sample Usage: Dim progressBar As New ProgressBarĬall progressBar.Update(i, 100, "My Message Here", True)Īpplication.Wait (Now + TimeValue("0:00:01"))ĭisplay the integers used to drive the progress bar If Len(display) > MAX_LENGTH Then display = Right(display, MAX_LENGTH) ' chop off to the maximum length if necessary If DisplayPercent = True Then display = display & " (" & Value & "%) " ' Closing character to show end of the bar If MaxValue > 0 Then Value = WorksheetFunction.RoundUp((Value * 100) / MaxValue, 0)ĭisplay = display & String(Int(Value / (100 / NUM_BARS)), BAR_CHAR)ĭisplay = display & String(NUM_BARS - Int(Value / (100 / NUM_BARS)), SPACE_CHAR) ' If the maximum is set then adjust value to be in the range 0 to 100 If Value 100 And MaxValue = 0) Then Exit Sub ' DisplayPercent : Display the percent complete after the status bar ' Status : optional message to display for user Optional ByVal DisplayPercent As Boolean = True) ' set the progress bar chars (should be equal size)Īpplication.DisplayStatusBar = statusBarStateĪpplication.ScreenUpdating = screenUpdatingStateĪpplication.EnableEvents = enableEventsState ScreenUpdatingState = Application.ScreenUpdating StatusBarState = Application.DisplayStatusBarĮnableEventsState = Application.EnableEvents ' Save the state of the variables to change Private Const MAX_LENGTH As Integer = 255
Once the object goes out of scope it will automatically clean up and release the StatusBar back to Excel. Also by using a class, you can set it up to handle initializing and releasing the StatusBar automatically. You can set the length of the bar by changing NUM_BARS. Just select one according to how much space you want to show between the bars. 9608 - 9615 are the codes I tried for the bars. Here's another example using the StatusBar as a progress bar.īy using some Unicode Characters, you can mimic a progress bar.
0 kommentar(er)
