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 https://stackoverflow.com/a/2781710/5962841 — Mafii, Jul 06 '17 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, Jul 06 '17 at 14:52
have to handle it as a variant, or loop over it and assign every item to the string array. yes. — Mafii, Jul 06 '17 at 14:55
I am OK with handling it as a Variant, but then how would I access the data? Thanks again. — Bob, Jul 06 '17 at 14:56

Mafii · Accepted Answer · 2017-07-06T14:59:15.637

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 06 '17 at 14:59

answered Jul 06 '17 at 14:54

Mafii

7,227
1
35
55

Thank you. But now in the calling function it says "compile error: can't assign to array" – Bob Jul 06 '17 at 14:56
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? – Bob Jul 06 '17 at 15:04
@Bob glad I could help. Please consider accepting the answer as we normally do on stack overflow when something solved your problem(s) – Mafii Jul 06 '17 at 15:05
Sure thing I will do that. Any chance I can get some clarification on the edit I just made? – Bob Jul 06 '17 at 15:06
I'm getting syntax errors when trying to do this with an array of Integers – Bob Jul 06 '17 at 15:10
instead of `CStr` you have to use `CInt` (conversation to int, not to string). does that help? – Mafii Jul 06 '17 at 15:13
is your target array from type integer? – Mafii Jul 06 '17 at 15:17
I'm returning a Variant array loaded with Integers, analogous to the String array version you got to work for me – Bob Jul 06 '17 at 15:19
Dim myArray() as string should be Dim myArray as Integer – Mafii Jul 06 '17 at 15:21
It is not working. I will edit the original question with the code that isn't working – Bob Jul 06 '17 at 15:24
rather post a new question so this one stays in context! – Mafii Jul 06 '17 at 15:25
OK I guess I can't. Here it is: Dim pathTimeList() As Integer ReDim pathTimeList(0 To stepCount) pathTimeList = set_path_time_list(stepCount) The function is Function xx() As Integer and returns type Integer() – Bob Jul 06 '17 at 15:25
the function also has to return Variant! – Mafii Jul 06 '17 at 15:26

score 0 · Answer 2 · answered Jul 06 '17 at 14:53

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 06 '17 at 14:53

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? – Mafii Jul 06 '17 at 14:54
Thank you. But now in the calling function it says "compile error: can't assign to array" – Bob just now edit – Bob Jul 06 '17 at 14:56
Bob, the compilation error is obviously due to something else. Can you post the "NEW" code? – Jul 06 '17 at 15:17

score 0 · Answer 3 · answered Jul 06 '17 at 14:56

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 06 '17 at 14:56

fmedv

153
1
2
12

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. – Mathieu Guindon Jul 06 '17 at 15:31
That's correct, but I was trying to answer him to his context ("return a string array from a function") – fmedv Jul 06 '17 at 16:24

Can't Return String Array in VBA

3 Answers3