VBA: Public variable vs property

Question

I have 3 different modules and all of them have Subs that use at some stage the workbook the code is stored in. Module3 is where my ranges are being set and I have also put the public workbook variable there. For clarification: the two main subs complete different tasks and have nothing to do with each other - except that both use some of the same range and row variables, that's why I put them in an extra module.

I also have a userform that uses ThisWorkbook in its Initialize event. So far I've been using a public variable to declare the workbook (wbTest) in Module3, but I've been reading up on properties, which I have never used before. I was thinking about passing the wbTest from Module2 to the Userform, which I found out isn't possible for Initialize and that's how I came upon properties.

My question is: From examples I can see that it's fairly convenient to use properties when there is some kind of error handling or calculation associated with it. But in my case it is simply a Set wbTest command, nothing more - does choosing a property over a public variable still make sense in this case?

Module1:

Option Explicit

Private Sub MainSub1()
    Call Module3.DefiningRanges
    MsgBox wbTest.Range("A1").Value
End Sub

Module2:

Option Explicit

Private Sub MainSub2()
   Call Module3.DefiningRanges
   MsgBox wbTest.Range("A2").Value
   frmSelection.Show
End Sub

Module3:

Option Explicit
Public wbTest As Workbook

Public Sub DefiningRanges()
   Set wbTest = ThisWorkbook   
End Sub

Userform frmSelection:

Option Explicit

Private Sub UserForm_Initialize()
  MsgBox wbTest.Sheets(1).Range("B1").Value
End Sub

So this is a fairly general question, but on google I haven't found a simple case like my one. I now do know that public variables are frowned upon and many try to avoid them if possible - that's why I'm asking if I should replace it with a property here.

What if instead using either, you just pass the wb variable as parameter for each function from the first? — Damian
I generally always use Properties even in the case of only setting the backing variable. Why? Mainly for consistency, although some purists will insist upon always using Properties. — Brian M Stafford
@Damian Yeah, that seems like the easiest option. I overlooked this possibiliy because until today, when I was cleaning up my code, it would have been not as straightforward as it is now.Thank you! — Alex
@BrianMStafford If I were to use a property, I'd only have to use Public Property Get, right? I don't need to change the workbook once it's set, so Property Set isn't necessary? — Alex
@Alex Yes, you can do just the Property Get (read-only) or just the Property Set (write-only). So with your current design just the Property Get should be good. — Brian M Stafford

NautMeg NautMeg · Accepted Answer · 2020-02-26T12:38:30

as you're talking about using properties it sounds as though you want to use Module3 as an Object, in which case it needs to have been created as class module. That seems a bit overkill for simply returning a range - why don't you just convert it to a function that returns a range object? You could just change the Sub in Module3 as follows:

Public Function DefiningRanges() as WorkBook
   Set DefiningRanges = ThisWorkbook   
End Function

Then in your other modules you could create a new object and populate it with the result from this function as follows:

Private Sub MainSub1()
    Dim wb as WorkBook 
    Set wb = Module3.DefiningRanges()
    MsgBox wb.Range("A1").Value
End Sub

Note that you no longer would refer to wbTest and I have replaced it with the new WorkBook [wb] in the MsgBox line. This way, there's no global variables, only a "global function" which returns a reference to the WorkBook.

VBA: Public variable vs property

1 Answers