Macro elapsed time bar (same as download time elapsed bar)
 

Hi,

I was wondering if anyone know how to program into a macro a time-elapsed
bar showing the time left for a macro to run. I don't know the proper name
for this feature, but is the same seen when you download something. You will
see a horizontal bar that is filled in increasingly more as you near the
completion of your download.

TIA!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Macro elapsed time bar (same as download time elapsed bar)
 

John walkenbach has a simple implementation of a Progress Bar

http://www.j-walk.com/ss/excel/tips/tip34.htm

you can see how that works and see that tracking the progress has to be
implemented in the running code - the progress bar does not magically know
how the macro is progressing.

If you want to implement it on that status bar, this sample code was
previously posted by Michael Pierron.

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

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi,

I was wondering if anyone know how to program into a macro a time-elapsed
bar showing the time left for a macro to run. I don't know the proper name
for this feature, but is the same seen when you download something. You will
see a horizontal bar that is filled in increasingly more as you near the
completion of your download.

TIA!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

Macro elapsed time bar (same as download time elapsed bar)
 

Thanks Tom!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098


"Tom Ogilvy" wrote:

John walkenbach has a simple implementation of a Progress Bar

http://www.j-walk.com/ss/excel/tips/tip34.htm

you can see how that works and see that tracking the progress has to be
implemented in the running code - the progress bar does not magically know
how the macro is progressing.

If you want to implement it on that status bar, this sample code was
previously posted by Michael Pierron.

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

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi,

I was wondering if anyone know how to program into a macro a time-elapsed
bar showing the time left for a macro to run. I don't know the proper name
for this feature, but is the same seen when you download something. You will
see a horizontal bar that is filled in increasingly more as you near the
completion of your download.

TIA!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098