Progressbar during the CalulateFull execution

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?
 
Hi Knokie
I use an API to run a progressbar it wont take much to add it to project.
HTH
Charles

Private Declare Function CreateWindowEX _
Lib "user32" _
Alias "CreateWindowExA" ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) _
As Long

Private Declare Function DestroyWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Private Declare Function SetParent _
Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) _
As Long

Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Dim rTemp
Dim m_frmHwnd As Long
Dim m_pbHwnd As Long
Dim m_strAviFile As String



Sub UpDateProgressBar()
'
iVal = Format(X / iMax, "0%")
Label3.Caption = iVal
SendMessage m_pbHwnd, &H402, ByVal Val(iVal), 0& '&H402
DoEvents
End Sub

Sub SetupProgressBar()
'
m_frmHwnd = FindWindow(vbNullString, Me.Caption)
W = Me.InsideWidth * 4 / 3
H = Me.InsideHeight * 4 / 3
Me.Repaint
'// ProgressBar [Leds: &H50000000] [Smooth: &H50000001]
m_pbHwnd = CreateWindowEX(0, "msctls_progress32", "", &H50000000, 7, H -
90, W - 15, 25, m_frmHwnd, 0, 0, 0)
SetParent m_pbHwnd, m_frmHwnd
DoEvents
End Sub
Sub EndProgressBar()
'
Label3.Caption = ""
DestroyWindow m_pbHwnd
DoEvents
End Sub
 
Knokie said:
Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?
Click to expand...
---------------------

Chip Pearson has a nice "thermometer" style progressor bar that a lot of people
here like. I use it for long running programs. Here is the web page which
describes and provides it.

http://www.cpearson.com/excel/Progress.htm

Good luck...

Bill
 
Your approach interests me so I decided to play with it. I did a simple
copy/paste from your note into a VBE module and set up a dummy macro to call it
from. When I "Call SetupProgressBar" though, I get an immediate compile error
"Invalid use of Me keyword" in the first line of your routine.

The thing which comes to mind is that I'm still running Excel97 (under XP-Home
with all updates). Should your code work with that level of Excel, or does the
problem lie elsewhere?

Thanks...

Bill
--------------------
vqthomf said:
Hi Knokie
I use an API to run a progressbar it wont take much to add it to project.
HTH
Charles

Private Declare Function CreateWindowEX _
Lib "user32" _
Alias "CreateWindowExA" ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) _
As Long

Private Declare Function DestroyWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Private Declare Function SetParent _
Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) _
As Long

Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Dim rTemp
Dim m_frmHwnd As Long
Dim m_pbHwnd As Long
Dim m_strAviFile As String



Sub UpDateProgressBar()
'
iVal = Format(X / iMax, "0%")
Label3.Caption = iVal
SendMessage m_pbHwnd, &H402, ByVal Val(iVal), 0& '&H402
DoEvents
End Sub

Sub SetupProgressBar()
'
m_frmHwnd = FindWindow(vbNullString, Me.Caption)
W = Me.InsideWidth * 4 / 3
H = Me.InsideHeight * 4 / 3
Me.Repaint
'// ProgressBar [Leds: &H50000000] [Smooth: &H50000001]
m_pbHwnd = CreateWindowEX(0, "msctls_progress32", "", &H50000000, 7, H -
90, W - 15, 25, m_frmHwnd, 0, 0, 0)
SetParent m_pbHwnd, m_frmHwnd
DoEvents
End Sub
Sub EndProgressBar()
'
Label3.Caption = ""
DestroyWindow m_pbHwnd
DoEvents
End Sub



:

Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?
Click to expand...
Click to expand...
 
Hi Charles,

The problem is still that during the execution of the CalculateFull
statement no other patrs of the code can be executed. The solution must be in
an external program the shares a part of the processor running time. An
example is the ProgramManager. This indicates that the job is still running,
but gives not a progress indication.

Leo


vqthomf said:
Hi Knokie
I use an API to run a progressbar it wont take much to add it to project.
HTH
Charles

Private Declare Function CreateWindowEX _
Lib "user32" _
Alias "CreateWindowExA" ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) _
As Long

Private Declare Function DestroyWindow _
Lib "user32" ( _
ByVal hwnd As Long) _
As Long

Private Declare Function SetParent _
Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) _
As Long

Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Dim rTemp
Dim m_frmHwnd As Long
Dim m_pbHwnd As Long
Dim m_strAviFile As String



Sub UpDateProgressBar()
'
iVal = Format(X / iMax, "0%")
Label3.Caption = iVal
SendMessage m_pbHwnd, &H402, ByVal Val(iVal), 0& '&H402
DoEvents
End Sub

Sub SetupProgressBar()
'
m_frmHwnd = FindWindow(vbNullString, Me.Caption)
W = Me.InsideWidth * 4 / 3
H = Me.InsideHeight * 4 / 3
Me.Repaint
'// ProgressBar [Leds: &H50000000] [Smooth: &H50000001]
m_pbHwnd = CreateWindowEX(0, "msctls_progress32", "", &H50000000, 7, H -
90, W - 15, 25, m_frmHwnd, 0, 0, 0)
SetParent m_pbHwnd, m_frmHwnd
DoEvents
End Sub
Sub EndProgressBar()
'
Label3.Caption = ""
DestroyWindow m_pbHwnd
DoEvents
End Sub



Knokie From Holland said:
Hi,

I have a very large worksheet that collect data from more then 50 other
sheets. During the CalculateFull statement the processor runs at 100% for 80
minutes.
Only the Taskmanager indicates that the program is stil running.

I need a progressbar to get a direct control in my worksheet.

Where is the tip that solves my problem?
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Implement a progress bar when the macro run inside another workboo 2
ProgressBar Out of Sync 2
Progress bar during workbook save 6
Progressbar 1
execution speed issue when closing files 5
Making the macro's execution faster 2
Macro finishes faster when left clicking during execution 1
how to reduce execution time of my code? 6

Back
Top