CODEWITHSUNDEEP
vbaexcel
user126253
user126253

Reputation: 9

Can't Return Integer Array in Excel VBA

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

Upvotes: 1

Views: 280

Answers (1)

xidgel
xidgel

Reputation: 3145

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

Upvotes: 0

Related Questions