Progress Meter during macro execution

[Rob]

I know how to programatically change the text that is
displayed in the statusbar, but how do i access the
progress meter (displays during saves and calculations)
with code. I have looked thru the object browser, mostly
in the application object, and havent found any reference
to it. I am guessing that it is accessable via the panels
collection of the status bar but i cant find any
documentation about it.

I am open to other suggestions. with out going in to a lot
of detail, the setup is:

1) user clicks button
2) code executes (duration varies)
3) control is returned to user

i would like some way of informing the user that the macro
is still running and not frozen.

 

[mudraker]

Rob


What version of Excel are you using as i beleive the later version
have a progress bar built in to it that you can access.

If you are using 97 or earlier you would need to build your own using
form to display it.

I use my own progress built progress bar within access 9

 

[Rob]

Im using excel 2000. how do i access it via the object
model (i.e. application.statusbar.progressmeter)?

 

[Tom Ogilvy]

Posted by Michael Pierron (May 28, 2004)
Microsoft.public.excel.programming

Private Declare Function FindWindow& Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$)
Private Declare Function CreateWindowEX& Lib "user32" Alias _
"CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _
, ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _
, ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _
, ByVal hMenu&, ByVal hInstance&, lpParam As Any)
Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&)
Private Declare Function SendMessage& Lib "user32" Alias _
"SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any)
Private Declare Function GetClientRect& Lib "user32" _
(ByVal hWnd&, lpRect As RECT)
Private Declare Function FindWindowEx& Lib "user32" Alias _
"FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal lpsz2$)

Private Type RECT
cl As Long
ct As Long
cr As Long
cb As Long
End Type

Sub PBarDraw()
Dim BarState As Boolean
Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT
hWnd = FindWindow(vbNullString, Application.Caption)
hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString)
GetClientRect hWnd, R
h = (R.cb - R.ct) - 6: y = R.ct + 3
pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _
, &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&)
SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125)
BarState = Application.DisplayStatusBar
Application.DisplayStatusBar = True
For i = 1 To 50000
DoEvents
Application.StatusBar = Format(i / 50000, "0%")
SendMessage pbhWnd, &H402, Val(Application.StatusBar), 0
Next i
DestroyWindow pbhWnd
Application.StatusBar = False
Application.DisplayStatusBar = BarState
End Sub

 

[Rob van Gelder]

I have a couple of progress meter examples on my web site.
One in the Status Bar (based on post by Michel Pierron)
One on a Command Bar (toolbar)

 

[Ken]

Tom,
If I have a macro like:

Dim X as String

Sub Test()

 

[Ken]

Tom,
Sorry if this is the second post - hit the wrong key
earlier.

If I have a macro like:

Dim I as String

Sub Test()
Code
Code
End Sub

How/where does the code below fit in?
I don't know where to put it and how it gets "invoked."

 

[Tom Ogilvy]

This is the part of the macro where the statusbar is advanced:

For i = 1 To 50000
DoEvents
Application.StatusBar = Format(i / 50000, "0%")
SendMessage pbhWnd, &H402, Val(Application.StatusBar), 0
Next i

so you would adjust the portion of code above facilitate doing your work and
updating the progress bar. Your code has to make the determination on what
progress is being made and then set the progress bar to reflect that. In
Michael's demo, he loops 50000 times, so he passes the
val(format(i/50000,"0%")) to the API that is managing the progress bar.