Excel 2010 vba array as a class member error

Question

I am working on a project and have run into something that I don't understand. When assigning an array to a class member, the Let and Get names cannot be the same. If they are, I get the error:

Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter

Can anyone tell me if I'm just doing something wrong, or if this is just how it is. The code below generates the above message.

Test Code:

Sub loadServer()
Dim testServer As AvayaServer
Dim i As Long
Dim arr() As Variant

arr = Array("1", "2", "3", "4", "5")

Set testServer = New AvayaServer
testServer.Name = "This Sucks"
testServer.Skill = arr
MsgBox testServer.Skills(4)
MsgBox testServer.Name

End Sub

Class Code:

Private pName As String
Private pSkills() As String
Public Property Get Skills() As Variant
Skills = pSkills()
End Property

Public Property Let Skills(values() As Variant)
ReDim pSkills(UBound(values))
Dim i As Long
For i = LBound(values) To UBound(values)
    pSkills(i) = values(i)
Next
End Property

@simoco Thank you! I knew I had to be missing something silly — SelfTaughtCodingIdiot
– SelfTaughtCodingIdiot, Commented Jun 27, 2014 at 16:35
@vbnoob it is indeed silly (the thing, not you missing it) - a bizarre quirk. values As Variant will be a Variant which you later use to store an array, values() As Variant is an array of Variants. The Array function can only be assigned to the former. Also I assume testServer.Skill (note no "s") is just a spelling mistake above. If it isn't then change your code and I suggest putting Option Explicit at the top of your modules. — Cor_Blimey
– Cor_Blimey, Commented Jun 27, 2014 at 18:00
@Cor_Blimey, the testServer.Skill was there only by partial intent. Originally, for the code to work that in conjunction with Let Skill(values() As Variant had to be used. It really was an oversight in the post since I didn't correct all of the code. Thanks for catching that. — SelfTaughtCodingIdiot
– SelfTaughtCodingIdiot, Commented Jun 30, 2014 at 13:58

JustinJDavies · Accepted Answer · 2014-07-16 05:47:29Z

1

Change values() As Variant to values As Variant:

Class Code:

Private pName As String
Private pSkills() As String
Public Property Get Skills() As Variant
Skills = pSkills()
End Property

Public Property Let Skills(values As Variant)          'Fixed here
ReDim pSkills(UBound(values))
Dim i As Long
For i = LBound(values) To UBound(values)
    pSkills(i) = values(i)
Next
End Property

Explanation:

values As Variant will be of type Variant, which you later use to store an array. values() As Variant is an array of type Variant, to which an Array cannot be assigned; an Array can only be assigned to the former.

answered Jul 16, 2014 at 5:47

JustinJDavies

2,6934 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JustinJDavies Over a year ago

Just collating comments from the question as these answered the question. Not claiming this as my own work.

Collectives™ on Stack Overflow

Excel 2010 vba array as a class member error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related