Private modules and global variables

[davidm]

While subroutines in private modules (as in userforms) can access global variables in general modules publicly declared , I am having trouble engineeering a reverse flow ( to transfer variable values across from private to general module).

Is there a way codes in a general module can access variables generated in Userform or Class Modules, assuming these are declared public at the module level?


Thanks for any help.


David

[Bob Phillips]

Yes, declare them as Public as usual, and when accessing them qualify with
the class name, such as

Userform1.myVar

or

Sheet1.myVar

--

HTH

RP
(remove nothere from the email address if mailing direct)


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> While subroutines in private modules (as in userforms) can access global
> variables in general modules *publicly declared *, I am having trouble
> engineeering a reverse flow ( to transfer variable values across from
> private to general module).
>
> Is there a way codes in a general module can access variables
> generated in Userform or Class Modules, assuming these are declared
> public at the module level?
>
>
> Thanks for any help.
>
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=488169
>

[davidm]

Hi Bob,

Thanks for your reply. I am however still at sea. Suppose, for an illustration, I have a code in a Userform module that counts the number of controls on the Form and wish to retieve the count in a general module. The standard code in the USerform module will look like:

Private Sub ShowNumberofControls()
Dim c as Control
For Each c In Me.Controls
k = k + 1
Next

...And in the general module

Sub RetrieveCounter()
Call ShowNumberOfControls
Msgbox Userform1.k
End Sub


The above stalls at Call ShowNumberOfControls with a Complie Error: Sub or Function not defined message.


Have I misconstrued your guidance?

David.

[Dave Peterson]

I put this behind the userform:

Option Explicit
Public k As Long
Sub ShowNumberofControls()
Dim c As Control
k = 0
For Each c In Me.Controls
k = k + 1
Next c
'k = me.Controls.Count 'maybe better???
End Sub

And I put this in a general module:
Option Explicit
Sub RetrieveCounter()
Call UserForm1.ShowNumberofControls
MsgBox UserForm1.k
End Sub

===

Or I could dump the routine behind the userform and use:

Sub retrieveCounter2()
MsgBox UserForm1.Controls.Count
End Sub

davidm wrote:
>
> Hi Bob,
>
> Thanks for your reply. I am however still at sea. Suppose, for an
> illustration, I have a code in a Userform module that counts the number
> of controls on the Form and wish to retieve the count in a general
> module. The standard code in the USerform module will look like:
>
> Private Sub ShowNumberofControls()
> Dim c as Control
> For Each c In Me.Controls
> k = k + 1
> Next
>
> ..And in the general module
>
> Sub RetrieveCounter()
> Call ShowNumberOfControls
> Msgbox Userform1.k
> End Sub
>
> The above stalls at *Call ShowNumberOfControls *with a *Complie Error:
> Sub or Function not defined* message.
>
> Have I misconstrued your guidance?
>
> David.
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=488169

--

Dave Peterson

[Bob Phillips]

I never said that you can run a procedure in the userform, you can, but that
is not what I said. I just showed you how to get the userform variable.

To do that, you need to declare the procedure as public and use this code

Sub RetrieveCounter()
Load UserForm1
UserForm1.ShowNumberofControls
MsgBox UserForm1.k
Unload Userform1
End Sub

Don't forget to declare k as a modula public variable else it will be local
to the procedure.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> Thanks for your reply. I am however still at sea. Suppose, for an
> illustration, I have a code in a Userform module that counts the number
> of controls on the Form and wish to retieve the count in a general
> module. The standard code in the USerform module will look like:
>
> Private Sub ShowNumberofControls()
> Dim c as Control
> For Each c In Me.Controls
> k = k + 1
> Next
>
> ..And in the general module
>
> Sub RetrieveCounter()
> Call ShowNumberOfControls
> Msgbox Userform1.k
> End Sub
>
>
> The above stalls at *Call ShowNumberOfControls *with a *Complie Error:
> Sub or Function not defined* message.
>
>
> Have I misconstrued your guidance?
>
> David.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=488169
>

[davidm]

Hello Bob,

Excuse my obtuseness. If the procedure

Sub RetrieveCounter()
Load UserForm1
UserForm1.ShowNumberofControls
MsgBox UserForm1.k
Unload Userform1
End Sub

is run from a general module, it throws up another compile error message: Member or data member not found with both the sub title Sub RetrieveCounter()and UserForm1.ShowNumberofControls highlighted. In all this, k is declared Public at the Userform code module level and should be truly global. I am using Xl2000 and could this put down to paying the laggards' price?

David.

[Bob Phillips]

Not sure David.

I tried her also on XL 2000, and it worked fine.

Want to post a workbook to me?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> Hello Bob,
>
> Excuse my obtuseness. If the procedure
>
> Sub RetrieveCounter()
> Load UserForm1
> UserForm1.ShowNumberofControls
> MsgBox UserForm1.k
> Unload Userform1
> End Sub
>
> is run from a general module, it throws up another compile error
> message: *Member or data member not found* with both the sub title Sub
> RetrieveCounter()and UserForm1.ShowNumberofControls highlighted. In all
> this, k is declared *Public* at the Userform code module level and
> should be truly global. I am using Xl2000 and could this put down to
> paying the laggards' price?
>
> David.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=488169
>