VBA global class variable

问题:

My obstacle is trying to get multiple subs to recognize class variables. When I try to declare them globally, I get a compile error: "Invalid outside procedure". Then, when I run a public function or sub to declare the variables, they remain undefined in the other subs. I want multiple subs to recognize the variables because their values are supposed to be altered via UserForm, and then utilized in a different sub.

If it could work in this manner, great, but I understand that my design could fundamentally be flawed. Please advise!

This is my Class definition, inserted as a Class module named "cRSM":

Option Explicit

Private pName As String
Private pDesiredGrowth As Double

'Name of RSM
Public Property Get Name() As String
Name = pName
End Property

Public Property Let Name(Value As String)
pName = Value
End Property


'Growth property
Public Property Get DesiredGrowth() As Double
DesiredGrowth = pDesiredGrowth
End Property

Public Property Let DesiredGrowth(Value As Double)
If Value > 0 And Value < 1 Then
    pDesiredGrowth = Value
End If
End Property

This is invalid procedure error (which I put in the Global Declarations section):

'Bedoya
Dim Bedoya As cRSM
Set Bedoya = New cRSM
Bedoya.Name = "Bedoya"

And this is the "variable not defined error" (within a private sub):

Private Sub Add_Click()
**Bedoya.DesiredGrowth** = Txt2.Value

Thank you for your time

回答1:

In a standard module (I name mine MGlobals), put

Public Bedoya As cRSM

Then in another standard module (I name mine MOpenClose), put

Sub Initialize()
    If Not Bedoya Is Nothing Then
        Set Bedoya = New cRSM
    End If
End Sub

Any default properties you want set should be set in the Class_Initialize procedure. In any procedure that you want to use Bedoya, use

Initialize

and it will instantiate the global variable if necessary. The only difference between this and the New keyword is that you can't accidentally instantiate the variable with this method. You either call Initialize or you don't. A lot of VBA developers use New, but almost never do for that reason.

回答2:

If I understood well You want a global object.

You can put the declaration in module like

public Bedoya As cRSM

then you have create the object ... you can use a global event inside the Workbook like

Private Sub Workbook_Open()
   Set Bedoya = New cRSM
   Bedoya.initialize("Bedoya") 'a method to initialize private variables
End Sub

Now you can use the global object. You have to restart the excel file or run this method manually.

Is not good style to use global variables, but sometimes is the more easy to do :P

What you want to do nowadays is done using singleton Software Pattern, but this is for other day hehehe