CODEWITHSUNDEEP
vbaexcelmoduleworksheet
Jan Horčička
Jan Horčička

Reputation: 21

Variable between worksheet and module not cooperating

My Module1 macro doesn't see the value of variable stated in Worksheet code. Any idea why that is? Everything is public.

In my Worksheet I have this:

Public oldVal As Variant

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
oldVal = Target.Value
End Sub

And in Module1 I have a macro connected to a button, that should (for testing purposes) just print the oldVal, but it isn't working. The message box is empty.

Public Sub button_Click1()
MsgBox oldVal
End Sub

Any idea why it isn't taking the value of the variable "OldVal" from the worksheet?

Thanks.

Upvotes: 2

Views: 289

Answers (2)

gembird
gembird

Reputation: 14053

The oldVal is like a property of a class where class is the worksheet. Therefore use the name of the worksheet as well e.g. MsgBox Sheet1.oldVal

Maybe the variable oldVal should be implemented as read-only property in class of the worksheet. So it will be only possible to set the value in the class when the selection changes and not from outside of the worksheet class. Something like the following.

Worksheet class module:

Private m_oldVal As Variant

Public Property Get OldVal() As Variant
    OldVal = m_oldVal
End Property

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
    m_oldVal = Target.Value
End Sub

Upvotes: 0

R3uK
R3uK

Reputation: 14537

You should declare your Public variables in a regular module to avoid this.

Because Sheet's modules, ThisSession, ThisWorkbook, ... are class's modules and so the variable that you declare is considered as a property of that class/object.

Your regular module code 

Public oldVal As Variant

Public Sub button_Click1()
    MsgBox oldVal
End Sub

Your Sheet module code 

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
     oldVal = Target.Value
End Sub

Upvotes: 1

Related Questions