Progress Bar Stuck after Crashing

[John]

I'm using code like this to update the progress bar in excel:

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


My problem is this: If the code crashes and and does not destroy the
pbhWnd, the progress bar stays dispalyed as is till I restart excel.
Is there anyway to relocate the window (since its created on the fly)
and destroy it in a seperate set of code that I could run anytime it
gets hung?

Thanks in advance,

John

 

[Jim Cone]

John,

My 2 cents worth...
I experimented with code to manipulate the status display a few year back.
I had a couple of crashes and stopped using it.

There are other ways to display progress, including the
simple and reliable...
Application.StatusBar = "Progress " & Format$(lngCount/lngTotal, "00%)

Your users will not appreciate your artistic endeavors, if they lose their work
and have to reboot.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"John" <[email protected]>
wrote in message
I'm using code like this to update the progress bar in excel:

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


My problem is this: If the code crashes and and does not destroy the
pbhWnd, the progress bar stays dispalyed as is till I restart excel.
Is there anyway to relocate the window (since its created on the fly)
and destroy it in a seperate set of code that I could run anytime it
gets hung?

Thanks in advance,

John

 

[NickHK]

John,
As Excel (and Office apps in general) are complex apps that do not always
seem to behave as you expect with this kind of code, expect a lot of crashes
until you get it bullet proof.
Whilst I'm all for extending the capabilities of Excel, the end result has
to be worth it to the user.
Personally, I do not think is one of those cases.

At least you should add error checking; check the return value of the call
(or LastError if appropriate) rather than VBA error handling.

Also, the classnames of some of Excel's object are changed in 2007 version.
Not sure about the status bar, but you should check.

NickHK