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

Question

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

Where does ArraySize come from? Or Account1PeriodReturnSeriesArray etc ? — Tim Williams
– Tim Williams, Commented Oct 19, 2018 at 0:09
ArraySize comes from the Rows.Count of the Range that you get from an Index,Match using the AccountID’s. Account1PeriodReturnSeriesArray is a Variant of dimensions ArraySize x 1 that comes from the Range Account1PeriodReturnsSeries, which you get from an Index,Match using the AccountID’s. I omitted this code since as the Debug.Print shows, the final array is working as expected except when I try to return it. — cpage
– cpage, Commented Oct 19, 2018 at 2:43

Tim Williams · Accepted Answer · 2018-10-19 03:05:41Z

1

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.

edited Oct 19, 2018 at 3:05

answered Oct 19, 2018 at 2:53

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

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

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