Excel VBA - Sheet Property does not assign a field

Question

new to VBA--Trying to use Sheet properties like I would use Getters and Setters in OOP languages. I have the following code in Sheet1(simplified for clarity):

Option Explicit

Private bAllowChange As Boolean

Public Property Let AllowChange(bLetAllowChange As Boolean)
    bAllowChange = bLetAllowChange
End Property

I would expect that when the AllowChange property is called by a sub in another module, the bAllowChange field will be updated accordingly. By using the debugging feature of VBE I can see that AllowChange is being passed the correct value (bLetAllowChange takes on the correct value) but the line

bAllowChange = bLetAllowChange

does not assign the value to bAllowChange. If I set bAllowChange to Public then it works as expected, however this defeats the purpose of using Properties in the first place. This is probably something simple that I'm not understanding about scope in VBA. Any suggestions? Thanks in advance.

Answer 1

The following works for me:

In Module1:

Option Explicit

Sub SetItToFalse()
    Sheet1.AllowChange = False
End Sub
Sub SetItToTrue()
    Sheet1.AllowChange = True
End Sub

In the sheet with a code name of Sheet1:

Option Explicit

Private bAllowChange As Boolean

Public Property Let AllowChange(bLetAllowChange As Boolean)
    bAllowChange = bLetAllowChange
End Property
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox bAllowChange
End Sub

With that code set up, I can invoke the SetItToFalse subroutine and then every selection change shows me it is False. After invoking SetItToTrue every selection change shows me it is True.

Answer 2

You can use the Worksheet's Names collection to store your custom Worksheets property values permanently.

The main caveat to this is that the value is returned prefixed by an equal sign. (e.g. after setting name.Value = True , name.Value would = =True). I made a workaround for this in my example.

---

Public Property Let AllowChange(bLetAllowChange As Boolean)
    Dim n As Name
    On Error Resume Next
    Set n = Me.Names("bLetAllowChange")
    If n Is Nothing Then
        Set n = Me.Names.Add("bLetAllowChange", bLetAllowChange)
    End If
    n.Value = bLetAllowChange
    On Error GoTo 0
End Property

Public Property Get AllowChange() As Boolean
    Dim result As String
    On Error Resume Next
    result = Me.Names("bLetAllowChange").Value
    result = Right(result, Len(result) - 1)
    AllowChange = Application.Evaluate(result)
    On Error GoTo 0
End Property

Answer 3

if you want persistent properties, try this (just an example of how to set/get/update/delete custom properties)

Sub customProperties()

    ActiveWorkbook.Sheets("Sheet1").customProperties.Add "abc123", 123
    Debug.Print ActiveWorkbook.Sheets("Sheet1").customProperties(1)     ' custom properties are not indexed by name

    ActiveWorkbook.Sheets("Sheet1").customProperties(1).Value = "this is my data"
    Debug.Print ActiveWorkbook.Sheets("Sheet1").customProperties(1)

    ActiveWorkbook.Sheets("Sheet1").customProperties(1).Delete


    ' CustomDocumentProperties Types
    ' msoPropertyTypeBoolean  2
    ' msoPropertyTypeDate     3
    ' msoPropertyTypeFloat    5
    ' msoPropertyTypeNumber   1
    ' msoPropertyTypeString   4


    ActiveWorkbook.CustomDocumentProperties.Add Name:="xyz", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="xyz"
    Debug.Print ActiveWorkbook.CustomDocumentProperties("xyz")

    ActiveWorkbook.CustomDocumentProperties("xyz").Value = "abcdefg"
    Debug.Print ActiveWorkbook.CustomDocumentProperties("xyz")

    ActiveWorkbook.CustomDocumentProperties("xyz").Delete

End Sub