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
– Siphor, Commented Jun 12, 2014 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
– dankez, Commented Jun 12, 2014 at 20:54
Ok, you cant assign one array to another array (cpearson.com/excel/passingandreturningarrays.htm or msdn.microsoft.com/en-us/library/office/…) so even GR = ID wont work. But you can save the array in a simple Variant, without the braces for the array. — Siphor
– Siphor, Commented Jun 12, 2014 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
– Siphor, Commented Jun 12, 2014 at 21:13

Community · Accepted Answer · 2018-05-08 21:49:10Z

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)

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Jun 12, 2014 at 21:18

Siphor

2,5442 gold badges15 silver badges10 bronze badges

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

2 Comments

dankez Over a year ago

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")

Siphor Over a year ago

changed the variable m_pIDOrgList to m_vpIDOrgList and commented the line with the other list and now it works for me

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related