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

Question

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

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

When debugging, does the line Vector = returnArray give you what you're expecting, i.e. Vector is an array of values? — tospig
– tospig, Commented May 20, 2015 at 23:29
Can you add the code you're using to write the array to the sheet? — tospig
– tospig, Commented May 20, 2015 at 23:29
It looks like your issue at the moment is that you're mostly using a 1D array. Also what is the range "values"? — Shauno_88
– Shauno_88, Commented May 20, 2015 at 23:30

Gary's Student · Accepted Answer · 2015-05-20 23:32:36Z

2

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

answered May 20, 2015 at 23:32

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

Tim Williams · Accepted Answer · 2015-05-20 23:40:10Z

0

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

answered May 20, 2015 at 23:40

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related