Passing the LabelProgress.Width & lWidth? 1

[knifey]

Hi,
I have a userform setup as a progressbar with lWidth being the total width of the bar.
The progressbar works when I put all of the code in the module. But this code is called about 50 times in my VBA and I would like to have the code in just one place. Can anyone advise on where I'm gonig wrong?
Thanks,
Knifey

‘Top of module:
Public Const lWidth As Long = 270

‘In loop:
frmProgressBar.LabelProgress.Width = lWidth * (xCount / xEndCount)
frmProgressBar.LabelText = Format(100 * (frmProgressBar.LabelProgress.Width / lWidth), "0") & " % Complete."
frmProgressBar.TextBox1.Text = "Proccessing " & xCount & " of " & xEndCount & " files."
DoEvents

‘Behind progressbar userform:
Option Explicit

Private Sub UserForm_Initialize()
Dim lWidth As Long
frmProgressBar.LabelText = ""
frmProgressBar.TextBox1.Text = ""
frmProgressBar.LabelProgress.Width = 0
lWidth = 270
Application.ScreenUpdating = True
DoEvents
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

Private Sub UserForm_Terminate()
Application.ScreenUpdating = False
End Sub

 

[Andrzejek]

So, what does it do? Or, better, what doesn't it do that you want it to do? Your code I mean. What's the problem?

In the Module you declare a constant:
[tt]Public Const lWidth As Long = 270[/tt]

and in the Form you have:
[tt]lWidth = 270 [/tt]

You should be getting some kind of error since lWidth is not a variable and you can not (should not) be able to assign a value to it, other than when declared.

And where the [tt]xCount[/tt] and [tt] xEndCount[/tt] is comming from?

Have fun.

---- Andy

 

[combo]

Separate userform into defined object, use Repaint to refresh form:

'Top of module:
Public Const lWidth As Long = 270
Dim frmPB As frmProgressBar

Sub test()
Set frmPB = New frmProgressBar
frmPB.Show
End Sub

'Behind progressbar userform:
Option Explicit
Dim xCount As Long, j As Long, xEndCount As Long

Private Sub cmdGo_Click()
xEndCount = 200
For xCount = 1 To xEndCount: For j = 1 To 1000
Me.LabelProgress.Width = lWidth * (xCount / xEndCount)
Me.LabelText = Format(100 * (xCount / xEndCount), "0") & " % Complete."
Me.TextBox1.Text = "Proccessing " & xCount & " of " & xEndCount & " files."
Next j
Me.Repaint
Next xCount
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.LabelText = ""
Me.TextBox1.Text = ""
Me.LabelProgress.Width = 0
Application.ScreenUpdating = True
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

Private Sub UserForm_Terminate()
Application.ScreenUpdating = False
End Sub

combo

 

[knifey]

Hi Combo/Andy,
You'll have to excuse my knowledge of VBA (I'm a beginner).
Combo, please let me know what you mean by 'Separate userform into defined object'?
Your cmdGo_Click code looks good and I'll be trying it tomorrow morning. But I think what I need is a way of passing xEndCount to the userform on loading and pass xCount to the userform on each iteration of my loop (contained in the main module).
Any further advice would be much appreciated.
Cheers,
Knifey (London,U.K.)

 

[combo]

The frmProgressBar is in fact visual class. When you refer to it (for instance frmProgressBar.Show) vba creates (if not exists) an object with the same name, basing on this class, it is similar to existence of hidden declaration:
Dim frmProgressBar As New frmProgressBar
As a consequence, you couldn't know if you work with the same instance (object) of the class.
You can instantiate a userform explicitly (more than one instance too). To do this, declare object variable in one line (Dim frmPB) and instantaite it in a new line (Set frmPB=New frmProgressBar). This gives you control and reference to this specific instance. Controls are properties of this object, controls' properties (and methods) are properties/methods of properties of the instance of the userform.

In case of program structure, you can consider other solutions too:
1. pass userform as an argument to calculating procedure in module, called from userform (Call DoCalc(Me)):

Public Sub DoCalc (FormWithPB as frmProgressBar)
' calculate
With FormWithPB
  .LabelProgress.Width = lWidth * (xCount / xEndCount)
  ' ...
  . Repaint
End With
' loop 
End Sub

2. Compact form updating to method (public procedure) in userform:

 Public Sub SetProgressBar (Progress)
With Me
  .LabelProgress.Width = lWidth * Progress
  '.other settings
  .Repaint
End With
End Sub

Called in module (ex. 1):
FormWithPB.SetProgressBar (xCount / xEndCount)





combo

 

[knifey]

Hi Combo,
Thanks for that fantastic explaination of object instancing. Option 2 looks exactly what I need (would this slow down the running time in any way?)

Knifey

 

[combo]

It should be similar effect as in example from my first post. You can add additional test before calling this sub (just to avoid calling in each go for 1-10000 loop).

combo