Let property of VBA class modules - is it possible

Question

This question already has answers here:

How do optional Parameters in Let/Get Properties work? (2 answers)

Closed 4 years ago.

My understanding of using the Let property in a class module so far is that you set it up in the class modules like this:

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

And then you after you've created an object of this class you can set this property like so:

MyObject.Name = "Larry"

Question: Is it possible to somehow enter multiple arguments into a class property? For instance:

Dim pFirstName as String, pLastName as String
Public Property Let Name(FirstName As String, LastName As String)
    pFirstName = FirstName
    pLastName = LastName
End Property

How would you then go about setting this property outside the class?

MyObject.Name = ??

Or is this just plain not possible to do?

Answer 1

As per your comment if you would prefer to encapsulate this logic then you can use something similar to the below.

Below includes the sub and function. The function returns a Person object:

Public Sub testing()

    Dim t As Person

    Set t = PersonData("John", "Smith")

End Sub


Public Function PersonData(firstName As String, lastName As String) As Person

    Dim p As New Person

    p.firstName = firstName
    p.lastName = lastName

    Set PersonData = p

End Function

Person Class:

Dim pFirstName As String, pLastName As String

Public Property Let FirstName(FirstName As String)
    pFirstName = FirstName
End Property

Public Property Get FirstName() As String
    FirstName = pFirstName
End Property

Public Property Let LastName(LastName As String)
    pLastName = LastName
End Property

Public Property Get LastName() As String
    LastName = pLastName
End Property

Answer 2

I realise there already are 2 workarounds but I thought answering your original question was worth giving a shot due to the amount of views this question is receiving.

The answer to

Is it possible to have multiple arguments in the Let Property in VBA?

is

YES! It's possible.

First let's talk about the GET property. Consider this being the Class1

Private firstName as String
Private lastName as String

Public Property Get Name() As String
    Name = firstName & " " & lastName
End Property

The Name property will return the full name, that's great but how to use the Let property to assign firstName & lastName in one go?

Side note: You could pass a single string separated with a special character and split it inside the body of the Let and assign the first and last names but forget that, let's get it done properly...

OK, in VBA the default Let property for the current setup would take 1 parameter and assign it to either first or last name...

Something like:

Public Property Let Name(value As String)
    firstName = value
End Property

Rule: the Get takes no parameters and the Let takes one. That is very logical because the Get returns the underlying value but the Let needs to grab a value from somewhere in order to assign it to the data it's representing. Worth remembering at this point that the Let property is assigned via the = sign ie. myObject.Name = "IdOnKnuu"

we know that if we stay consistent with the above rule, theoretically we should be able to add one parameter to the Get and one more to the Let.

Let's forget the Let - comment it out for now - and add a parameter to the Get property.

Private firstName As String
Private lastName As String

Public Property Get Name(first As String) As String
    Name = firstName & " " & lastName
End Property

'Public Property Let Name(value As String)
'    firstName = value
'End Property

Going back to the Module1 create an instance of Class1 and type c.Name(

oh, intelli-sense expecting something? Awesome!

At this point it's worth understanding that our Get property returns first + last which are both empty at the moment, so it doesn't matter what you are going to pass to the c.Name() it will return an empty string.

Ok, let's uncomment and tweak the Let property now...

Side node: if you jump back to Module1 and type c. and don't get intelli-sense it pretty much means that something in Class1 is broken... We already know - it's the Let property that's causing it

We have added a parameter to the Get property, let's do the same with the Let property...

Public Property Let Name(first As String, value As String)
    firstName = value
End Property

Let's go back to the Module1 and try to use the Let property:

Remember, how you have used the Let property before? You needed to assign it a value

c.Name = "John"

But now, your Let property is expecting an extra parameter first as String.

Why don't we try this:

c.Name("John") = "Smith"

Hey! that compiles and runs (a quick F5)

Great, let's check the results!

Debug.print c.Name("John")

Uhm... that only shows Smith in the Immediate Window (Ctrl+G)... Not exactly what we were looking for....

Going back to the Let property we notice that we are grabbing 2 values via arguments but we only make use of one of them? We have the 2 different values coming in to the function, right? Let's treat the first one as the firstName and the second one as lastName

Public Property Let Name(first As String, last As String)
    firstName = first
    lastName = last
End Property

Going back to Module1

Sub Main()

    Dim c As New Class1

    c.Name("John") = "Smith"

    Debug.Print c.Name("John")

End Sub

and re-running out current code gives us what we need ... it prints John Smith but wait!!! why do we have to pass the first name in order to retrieve the full name?

Ha! The trick to this is to make the first parameters of both properties Optional

Summarising, the code:

Class1.cls

Private firstName As String
Private lastName As String

Public Property Get Name(Optional first As String) As String
    Name = firstName & " " & lastName
End Property

Public Property Let Name(Optional first As String, last As String)
    firstName = first
    lastName = last
End Property

Module1.bas

Sub Main()

    Dim c As New Class1

    c.Name("John") = "Smith"

    Debug.Print c.Name ' prints John Smith

End Sub

So basically the assignment of two (or more) values through the Let property is possible in VBA. The thing that may throw you off a bit is the Syntax for it but I hope my explanation in this answer has helped you understand where things come from and why.

The Get property takes an optional parameter - it's really just a dummy parameter... it's not used anywhere within the Get property but it's allowing us to get the desired Let signature and allows us to pass two parameters to it. It also grants the easy to remember c.Name syntax.

The call

Debug.Print c.Name("first")

is still possible, however the "first" parameter just acts like a dummy and has no effect on the actual underlying data. It's a dummy and has no effect on the actual data - dump it and use:

Debug.print c.Name

definitely more convenient :)

Answer 3

Use as Public Sub procedure within the class to perform this:

Public Sub testing()
Dim myPerson As New Person
myPerson.SetName "KaciRee", "Software"

End Sub

Person Class:

Dim pFirstName As String, pLastName As String

Public Property Let FirstName(FirstName As String)
    pFirstName = FirstName
End Property

Public Property Get FirstName() As String
    FirstName = pFirstName
End Property

Public Property Let LastName(LastName As String)
    pLastName = LastName
End Property

Public Property Get LastName() As String
    LastName = pLastName
End Property

Public Sub SetName(FirstName As String, LastName As String)
pFirstName = FirstName
pLastName = LastName
End Sub