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

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" wrote in message
om...
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,

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Mark Worthington" wrote in message
...
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.