Can't Return String Array in VBA

Question

I have been combing the internet for an answer to this and can't find anything. For the life of me, I can't return a string array from a function. I've tried as a variant as well, but all I get is syntax errors.

Private Function set_device_list(ByVal deviceListSize As Integer) As Variant()



    Dim loopIndex As Integer
    loopIndex = 0

    Dim firstRow As Integer
    Dim lastRow As Integer
    Dim firstColumn As Integer
    Dim lastColumn As Integer

    firstRow = 2
    lastRow = 2
    firstColumn = 4
    lastColumn = 63

    Dim deviceStartIndex As String
    Dim deviceEndIndex As String

    Dim deviceList() As Variant
    ReDim deviceList(0 To (deviceListSize - 1))

    deviceStartIndex = Cells(firstRow, firstColumn)
    deviceEndIndex = Cells(lastRow, lastColumn)

    For i = firstColumn To lastColumn

        deviceList(loopIndex) = Cells(firstRow, i).Value

        loopIndex = loopIndex + 1
        Next i
    Return deviceList

End Function

MacroMarc is absolutely right, see stackoverflow.com/a/2781710/5962841 — Mafii
– Mafii, Commented Jul 6, 2017 at 14:52
I'm sorry, what? So to return the string array I just put set_device_list = deviceList? I get an error in the calling function now, when I try to set a stringArray = set_device_list(num) — Bob
– Bob, Commented Jul 6, 2017 at 14:52
have to handle it as a variant, or loop over it and assign every item to the string array. yes. — Mafii
– Mafii, Commented Jul 6, 2017 at 14:55
I am OK with handling it as a Variant, but then how would I access the data? Thanks again. — Bob
– Bob, Commented Jul 6, 2017 at 14:56

Mafii · Accepted Answer · 2017-07-06 14:59:15Z

1

Just use set_device_list = deviceList, this is the vba equivalent to the return expression of most c like languages.

To assign it to a string array, do this:

Dim myArray() As String
Dim device_list() as Variant

device_list = set_device_list(yourNumber)

For i = 1 To UBound(device_list)
  myArray(i) = CStr(device_list(i))    
Next

edited Jul 6, 2017 at 14:59

answered Jul 6, 2017 at 14:54

Mafii

7,4951 gold badge40 silver badges58 bronze badges

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

13 Comments

Bob Over a year ago

Thank you. But now in the calling function it says "compile error: can't assign to array"

Bob Over a year ago

Thank you so much!! That works :) Is a function returning an Integer array OK or do I have to use the same trick with variants?

Mafii Over a year ago

@Bob glad I could help. Please consider accepting the answer as we normally do on stack overflow when something solved your problem(s)

Bob Over a year ago

Sure thing I will do that. Any chance I can get some clarification on the edit I just made?

Bob Over a year ago

I'm getting syntax errors when trying to do this with an array of Integers

|

user8221156 · Accepted Answer · 2017-07-06 14:53:07Z

0

Please drop the parentheses at the end of function declaration: "...As Variant" not "...As Variant()"

Try instead of "Return deviceList" enter "set_device_list = deviceList"

answered Jul 6, 2017 at 14:53

user8221156

3 Comments

Mafii Over a year ago

Well either it is a Variant() where every object is a string, or it is a Variant that represents a string array. IIRC both work, is that right?

Bob Over a year ago

Thank you. But now in the calling function it says "compile error: can't assign to array" – Bob just now edit

user8221156 Over a year ago

Bob, the compilation error is obviously due to something else. Can you post the "NEW" code?

fmedv · Accepted Answer · 2017-07-06 14:56:45Z

0

Actually there is no actual "return" function in VBA. What you can do is that you assign the result of the function to the variable.

For example:

Dim test As Integer
Dim dummy As Integer

Function do_something(parameter) As Integer
      For i = parameter to 5
            dummy = dummy + i
      Next i
do_something = dummy
End Function

Sub your_sub
    Dim test_var As Integer

    test_var = do_something(0)
End Sub

I hope that you can understand what I'm trying to tell

answered Jul 6, 2017 at 14:56

fmedv

1531 gold badge2 silver badges14 bronze badges

2 Comments

Mathieu Guindon Over a year ago

Technically there is a Return keyword in VBA. It's used to return from a GoSub jump, a piece of legacy that predates Sub procedures.

fmedv Over a year ago

That's correct, but I was trying to answer him to his context ("return a string array from a function")

Collectives™ on Stack Overflow

Can't Return String Array in VBA

3 Answers 3

13 Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related