VBA Excel - Passing String Arrays to Class Variant type using Get/Let Properties

Question

I have been scouring for an answer to simply pass and return a string array to a class module in vba. Below is my example code. I keep getting the error "Can't assign to array" on the line

Orgs.pIDOrgList = ID

Any thoughts?

Class Code:

'Class COrgList
Private m_vpIDOrgList() As Variant
'Public pGROrgList() As String

Function getOrgList(Comp As String) As String()
    If Comp = "Gram Stain" Then
        getOrgList = m_pGROrgList
        ElseIf Comp = "Identification" Then
        getOrgList = m_pIDOrgList
    Else
        MsgBox "Incorrect Comp Name"
    End If

End Function

Public Property Get pIDOrgList() As Variant()

    pIDOrgList = m_vpIDOrgList()

End Property

Public Property Let pIDOrgList(vpIDOrgList() As Variant)

    m_vpIDOrgList = vpIDOrgList

End Property

Module Test Code:

Sub test()


Dim Orgs As COrgList
Set Orgs = New COrgList
Dim ID(2) As String
Dim GR(2) As String

ID(0) = "0"
ID(1) = "2"
ID(2) = "1"


Debug.Print ID(0)

Orgs.pIDOrgList = ID

Debug.Print Orgs.getOrgList("Identifciation")(1)

End Sub

Thank you!

Answer 1

You cant assign one array to another array, but you can assign one array to a simple variant.(http://msdn.microsoft.com/en-us/library/office/gg264711(v=office.14).aspx or http://www.cpearson.com/excel/passingandreturningarrays.htm)

so it should be:

'Class COrgList
Private m_vpIDOrgList As Variant
'Public pGROrgList() As String

Function getOrgList(Comp As String) As String()
    If Comp = "Gram Stain" Then
        'getOrgList = m_pGROrgList
    ElseIf Comp = "Identification" Then
        getOrgList = m_vpIDOrgList
    Else
        MsgBox "Incorrect Comp Name"
    End If

End Function

Public Property Get pIDOrgList() As Variant

    pIDOrgList = m_vpIDOrgList

End Property

Public Property Let pIDOrgList(vpIDOrgList As Variant)

    m_vpIDOrgList = vpIDOrgList

End Property

also indentification is spelled wrong in Debug.Print Orgs.getOrgList("Identifciation")(1)