CODEWITHSUNDEEP
excelvba
bigbucky
bigbucky

Reputation: 417

How do you use a public variable in a Class Module?

I'm using a Class Module to make a collection of save buttons all do the same thing. But when I try to get them to run a sub that requires a variable I can't get the variable passed to them.

Edited using @Teasel's advice about properties. The problem seems to be the Let Property is not allowing me to set the variable from Module1.

Class1

Public WithEvents SaveBtn As MSForms.CommandButton

Dim currentrow As Long

Private Sub SaveBtn_Click()
     SendMessage
     `Even if I just have it Msgbox currentrow it returns 0
End Sub

Property Let GetRow(myrow As Long)
    currentrow = myrow
End Property

Property Get GetRow() As Long
    GetRow = currentrow
End Property

Module1

`Trying to send the value into the Class using Let
Private Sub SendRow_Click()
Module1.GetRow = 22
End Sub

`Trying to Get the value back from the Class
Public Sub SendMessage()
    Dim therow As Long
    therow = Module1.GetRow
    `I get the "Method or Data Member not found" error in the line above
    MsgBox therow
End Sub

UserForm1

`This part works fine
Dim colSaveButtons As New Collection

Private Sub UserForm_Initialize()

Dim i As Long
Dim ctl As MSForms.Control
Dim obEvents As Class1

For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.CommandButton Then
        For i = 0 To 5
            If ctl.Name = "btnSavePage" & i Then
                Set obEvents = New Class1
                Set obEvents.SaveBtn = ctl
                colSaveButtons.Add obEvents
            End If
        Next
    End If
Next ctl
End Sub

Upvotes: 0

Views: 1734

Answers (2)

Tim Williams
Tim Williams

Reputation: 166456

Add a "CurrentRow" field to your class module:

Public WithEvents SaveBtn As MSForms.CommandButton
Public CurrentRow As Long '<< add this

Private Sub SaveBtn_Click()
     SendMessage CurrentRow 
End Sub

In your loop:

...
If ctl.Name = "btnSavePage" & i Then
    Set obEvents = New Class1
    obEvents.CurrentRow = 10 'or whatever...
    Set obEvents.SaveBtn = ctl
    colSaveButtons.Add obEvents
End If
...

And your SendMessage method:

Public Sub SendMessage(CurrentRow As Long)
     MsgBox "This works"
End Sub

Upvotes: 1

Teasel
Teasel

Reputation: 1340

You can use two differents ways to achieve that.

1. Public Property

To simple access your variable's value you need a Get property and to set its value you need a Let property.

In your Module:

'Your module private variable
Dim nameOfYourModuleVariable As String

...

'Set property to assign a value to your variable
Public Property Let nameOfYourProperty(value As String)
    nameOfYourModuleVariable = value
End Property

'Get property to return the value of your variable
Public Property Get nameOfYourProperty() As String
    nameOfYourProperty = nameOfYourModuleVariable
End Property

You can then use it like this:

'Set the value
MyModule.nameOfYourProperty = "foo"

'Get the value
MyModule.nameOfYourProperty

I highly recommend to use properties to do such things however you can also simply set your variable as public as shown in point 2.

2. Public Variable

Define your variable to be public so you can access it from everywhere.

In your Module:

Public nameOfYourVariable As String

Get or set the value from another module:

'Set the value
MyModule.nameOfYourVariable = "foo"

'Get the value
MyModule.nameOfYourVariable

Upvotes: 1

Related Questions