Public Variables with UserForms

[Mark Worthington]

I have read with interest many forum posts about the use of Public
variables in Excel VBA.
I was blithely unaware of variable lifetimes (coming from a Fortan/VAX
background) and came a cropper recently. I now try to do without
Public declarations, directly pass the variables from one sub to the
next & declare everything within the procedure.

However, my question is how to do this with UserForms. For instance, I
have a Chart progress UserForm which has the activation following code
:

Private Sub UserForm_Activate()
Dim PctDone As Single
PctDone = Active_Chart / cochar
With UF_UpdateAllCharts_Progress
.FrameProgress.Caption = ForMat(Round(PctDone, 2), "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
.Repaint
End With
If PctDone >= 1 Then
Unload UF_UpdateAllCharts_Progress
Else
UF_UpdateAllCharts_Progress.Hide
End If
End Sub

I call the UserForm as normal from a procedure,
UF_UpdateAllCharts_Progress.Show

I need to transfer the Active_Chart and cochar integer values from the
module … the only way I can think is to use Public variables.

Can anyone please point me in the right direction?

Cheers,

Mark

 

[Bob Phillips]

Mark,

Surely the progress bar is always visible, so from your code you would just
re-paint the progress bar a bit further advanced.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark Worthington]

Bob,

As usual, the error of my ways has been succinctly pointed out! I am new
to UserForms and have just realised that I can control them direct from
the module code, rather than use the UserForm_Activate event handler. (I
ended up with no code at all in the UserForm but I did have to show the
form with vbModeless, though …). That still leaves the (very small)
issue of the extra overhead of updating the progress indicator … but one
can't get away from that.

One other point : there is no UserForm equivalent of EnableEvents =
False .... so I use a module level Boolean check.

Thanks for pointing me in the right direction,

Mark

 

[Bob Phillips]

Bob,

That still leaves the (very small)
issue of the extra overhead of updating the progress indicator . but one
can't get away from that.

It has to happen somewhere, it's our lot to have to do it.

One other point : there is no UserForm equivalent of EnableEvents =
False .... so I use a module level Boolean check.

That's how I do it. If set on entry, exit, else set it and reset on exit.
Again, a major hassle, but it works well.