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!

I think its because ID is a String array and orglist is a variant array — Siphor, Jun 12 '14 at 20:50
Shouldn't a variant be able to encompass a string array? I attempted the same structure but with String() rather than variant and it led to the same result. — dankez, Jun 12 '14 at 20:54
Ok, you cant assign one array to another array (http://www.cpearson.com/excel/passingandreturningarrays.htm or http://msdn.microsoft.com/en-us/library/office/gg264711(v=office.14).aspx) so even GR = ID wont work. But you can save the array in a simple Variant, without the braces for the array. — Siphor, Jun 12 '14 at 21:12
So Public Property Let pIDOrgList(vpIDOrgList() As Variant) should be Public Property Let pIDOrgList(vpIDOrgList As Variant), Private m_vpIDOrgList() As Variant should be Private m_vpIDOrgList As Variant and Public Property Get pIDOrgList() As Variant() should be Public Property Get pIDOrgList() As Variant — Siphor, Jun 12 '14 at 21:13

score 1 · Accepted Answer · edited May 08 '18 at 21:49

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)

Thanks Siphor that solves the problem of inputting the array. Do you know how I can later extract the value from the variant object? `Orgs.getOrgList("Identification")(1)` does not work nor does `GR = Orgs.getOrgList("Identification") ` — dankez, Jun 13 '14 at 13:12
changed the variable m_pIDOrgList to m_vpIDOrgList and commented the line with the other list and now it works for me — Siphor, Jun 13 '14 at 13:21

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

1 Answers1