VBA Compile Error: Can't Assign to Read-Only Property Returning Array from Custom Class Get Property

Question

I'm having an issue with the Get property from a custom class in VBA (Excel 2010). If an index argument is not given, then my Get property should return a reference (at least that's my impression) to the Class' array. If an index is given, it should return the value in the given index in the private array.

' Custom Class Properties
Private pMtbSheets() As String

'Get and Let Methods
Public Property Get MtbSheets(Optional index As Variant) As String()
    If IsMissing(index) Then
        ReDim MtbSheets(1 To UBound(pMtbSheets))
        MtbSheets = pMtbSheets()
    Else
        ReDim MtbSheets(1 To 1)
        MtbSheets(1) = pMtbSheets(index) '**Compiler error occures here**
    End If
End Property

Thanks for any help anyone is able to offer

Your function specification says the return value is a `String array`. You're getting the error because your Else condition is attempting to return a single string. To me anyway, this would be a confusing return from a function because I'd have to check the Type of the returned variable before using it. However, to solve your function as written, change the return value from `as String` to `as Variant`. — PeterT, Apr 06 '16 at 13:37

score 2 · Accepted Answer · answered Apr 06 '16 at 13:35

2

You need a temporary array to avoid ambiguity between MtbSheets(i) being interpreted as a property/method/function call vs an array access:

ReDim temp(1 To 1) As String
temp(1) = pMtbSheets(index)
MtbSheets = temp

answered Apr 06 '16 at 13:35

Alex K.

171,639
30
264
288

That did it! Thanks Alex. Additionally, I needed to use a temp array in the first part of the If statement. – Bryan Harper Apr 06 '16 at 18:21

Vincent G · Answer 2 · 2016-04-06T13:44:35.550

Edit: of course my answer won't work, you will need to use a temporary array as mentioned by Alex K. in his answer.

Just return the array like you do in your IsMissing() branch:

' Custom Class Properties
Private pMtbSheets() As String

'Get and Let Methods
Public Property Get MtbSheets(Optional index As Variant) As String()
    If IsMissing(index) Then
        ReDim MtbSheets(1 To UBound(pMtbSheets))
        MtbSheets = pMtbSheets()
    Else
        ReDim MtbSheets(1 To 1)
        MtbSheets = pMtbSheets(index)
    End If
End Property

VBA Compile Error: Can't Assign to Read-Only Property Returning Array from Custom Class Get Property

2 Answers2