CODEWITHSUNDEEP
arraysvbaexcel
sparta
sparta

Reputation: 33

VBA: trying to return an array but only getting the first value

I think this question has been asked a thousand times but after hours of searching I still can't find any help. So I need to use a vector of numbers as an input, do a calculation to all of those numbers and then return the results as a vector with a same dimension as the input vector. So lets say that I want to take some numbers and then subtract all of them by one and so my Excel sheet should look like this

    A   B
1   6   5
2   7   6
3   8   7
4   9   8 
5   10  9

where column A is the input vector and B should be the output. But my output is only a column of zeros. Debugging also tells that the right numbers are there in the output vector but I can only print the first value which then goes to all cells. At the moment my code looks like this

Public Function Vector(values As Range) As Variant

    Dim arraySize As Integer, i As Integer
    arraySize = values.rows.Count
    Dim returnArray() As Variant
    ReDim returnArray(arraySize)

    For i = 1 To arraySize
        returnArray(i - 1) = values(i, 1) - 1
    Next i

    Vector = returnArray

End Function

Upvotes: 2

Views: 2852

Answers (2)

Tim Williams
Tim Williams

Reputation: 166316

Public Function Vector(values As Range) As Variant

    Dim r As Long, c As Long, returnArray As Variant

    returnArray = values.Value

    For r = 1 To ubound(returnArray, 1)
    For c = 1 To ubound(returnArray, 2)
        returnArray(r, c) = returnArray(r, c) - 1
    Next c
    Next r

    Vector = returnArray

End Function

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Use a 2-dimensional array:

Public Function Vector(values As Range) As Variant

    Dim arraySize As Integer, i As Integer
    arraySize = values.Rows.Count
    Dim returnArray() As Variant
    ReDim returnArray(1 To arraySize, 1 To 1)

    For i = 1 To arraySize
        returnArray(i, 1) = values(i, 1) - 1
    Next i

    Vector = returnArray

End Function

enter image description here

NOTE:

When entering the array formula in the worksheet, hi-light cells B1 through B5, then click in the Formula Bar and then enter the formula with Ctrl + Shift + Enter

Upvotes: 2

Related Questions