CODEWITHSUNDEEP
arraysexcelvbaexcel-formula
cpage
cpage

Reputation: 39

Function Not Properly Returning Array Though Can See in Debug.Print

I am writing an Excel VBA function that returns an array to use in other functions. When I test out the function GenerateBlendedReturnSeries in my Excel Sheet and use ctlr-shift-enter to see the full result, the entire array is all zeros. Strangely, however, when I check Debug.Print (BlendedReturnSeriesArray(300, 1)), the proper non-zero value is returned. Why is the function unable to properly return this result? I array being returned is 329 x 1 and contains "" values in rows when the multiplication/addition in the for loop returns an error.

Function GenerateBlendedReturnSeries(AccountID1 As String, Account1Proportion As Double, _
     Optional ByVal AccountID2 As String, Optional ByVal Account2Proportion As Double, _
     Optional ByVal AccountID3 As String, Optional ByVal Account3Proportion As Double) As Variant 'Vs. As Double()

' CODE IN BETWEEN

Dim BlendedReturnSeriesArray As Variant
    ReDim BlendedReturnSeriesArray(ArraySize, 1)

    Debug.Print (ArraySize)

    On Error Resume Next
    For i = 0 To UBound(BlendedReturnSeriesArray)

        BlendedReturnSeriesArray(i, 1) = _
          Account1PeriodReturnSeriesArray(i, 1) * Account1Proportion _
        + Account2PeriodReturnSeriesArray(i, 1) * Account2Proportion _
        + Account3PeriodReturnSeriesArray(i, 1) * Account3Proportion
        'Debug.Print (BlendedReturnSeriesArray(i, 1))
        'Debug.Print (i)

    Next i

    On Error GoTo 0

    Debug.Print (BlendedReturnSeriesArray(300, 1))

    GenerateBlendedReturnSeries = BlendedReturnSeriesArray 'BlendedReturnSeriesArray

End Function

Upvotes: 0

Views: 113

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Unless you do this

ReDim BlendedReturnSeriesArray(1 To ArraySize, 1 To 1)

or you're using Option Base 1 (never a good idea), your return array will be sized as (0 to ArraySize, 0 to 1), so likely you're only seeing the first "column" of the array (the zero index) on your worksheet. If you expand the formula to cover two columns you'll see the numbers you're missing.

Upvotes: 1

Related Questions