Can't Return Integer Array in Excel VBA

Question

I was just having a similar problem with a String array and now that's working but this isnt. I tried returning an Integer array as Integer() type and as Variant() and looping through and converting each element with Cint(). I get a type mismatch either way. Here is the code:

Dim pathTimeList() As Integer
ReDim pathTimeList(0 To stepCount)
pathTimeList = set_path_time_list(stepCount)

Here is the function code:

Private Function set_path_time_list(ByVal stepCount As Integer) As Integer


    Dim pathTimeList() As Integer
    ReDim pathTimeList(0 To stepCount - 1)

    Dim loopIndex As Integer
    loopIndex = 0

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

    firstRow = 3
    lastRow = 27
    firstColumn = 2
    lastColumn = 2

    For i = firstRow To lastRow

        For j = firstColumn To lastColumn

            pathTimeList(loopIndex) = Cells(i, j).Value

        Next j

        loopIndex = loopIndex + 1

    Next i

    set_path_time = pathTimeList

End Function

I get no type mismatch. Please indicate which line the code is failing at. — Vegard
– Vegard, Commented Jul 7, 2017 at 7:07

xidgel · Accepted Answer · 2017-07-06 17:33:03Z

0

First, to make the function return an array of integers, in the function declaration follow the return type with () to indicate an array:

Private Function set_path_time_list(ByVal stepCount As Integer) As Integer()

Second, change the last statement to:

set_path_time_list = pathTimeList

A couple of optional clean-up items. After the last statement consider deallocating the storage you got with Redim:

Erase pathTimeList

Finally, you should declare i and j as Integer or Long.

Hope that helps

answered Jul 6, 2017 at 17:33

xidgel

3,1452 gold badges15 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Can't Return Integer Array in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related