Excel VBA UDF Array function returns the same value for all cels

1
votes

I'm trying to make my Excel VBA UDF function to return an array. As of now the function either returns #VALUE! error if I define the argument as double array or all of the cells in the array have the same value if I define the argument as variant even though I checked in the Locals window in VBE and the return array variable actually contains different values.

Here is my code:

Function VaRScenariosTest(ByRef dblRealRates() As Double/Variant) As Double()
'dblRealRates() is defined as either Double or Variant
Dim intCount As Integer
Dim dblTemp() As Double

For intCount = LBound(dblRealRates) To (UBound(dblRealRates) - 1)
  ReDim Preserve dblTemp(1 To intCount)
  dblTemp(intCount) = dblRealRates(intCount + 1) - dblRealRates(intCount)
Next intCount

VaRScenariosTest = dblTemp

End Function

Function Range2dblArray(ByRef rngRange As Range) As Double()

Dim dblTemp() As Double
Dim intCount As Integer

For intCount = 1 To rngRange.Count
  ReDim Preserve dblTemp(1 To intCount)
  dblTemp(intCount) = rngRange.Cells(intCount)
Next intCount

Range2dblArray = dblTemp

End Function

I call the function in the spreadsheet in the following way: function call in excel with dblRealRates() as Double/Variant

Column E contains the input to function, column F the values that should be returned by the function and column G the function itself. If I define the dblRealRates() variable type as double, the formula returns the #VALUE! error. If I define it as variant, the returned values in the spreadsheet are the same. If I pause the execution of the code at End Function line and look in the Locals WIndow, you can actually see that the values in the array are different, not all equal to 0.000016287 as the output in the spreadsheet suggests:Function Array values in Locals Window

Can someone please advise why does the formula returns an error when the dblRealRates() variable is defined as double, and why does it return identical values if it's defined as variant?

2
arraysexceluser-defined-functionsvba
Is there a reason why you need to use Range2dblArray as a UDF? Things would be easier if it didn't need to be usable from an Excel cell, and I'm not sure what use it would have as a standalone Excel function. If it didn't need to be a UDF as well, then you could just pass a Range to VaRScenariosTest and then that could call Range2dblArray as the first thing it did. – YowE3K
The VaRScenariosTest function is an intermediate step in a bigger calculation, therefore it will take on vba variables as arguments instead of Range objects. However for testing purposes I want to be able to have a look at all the intermediate steps, therefore I implemented the Range2dblArray function to be able to pass Range objects into the intermediate functions, where I want to check the intermediate results – VBAEnthusiast
This seems like a lot of effort just to avoid using the Locals Window to see intermediate results. – YowE3K
That is due to the fact that this function will later have more arguments and thus will need to be tested for all the possible combinations of those arguments which will be probaly 100 or more. It will be easier for me to have the arguments in cells and just drag the formula to correspond to appropriate cells. It will also allow me to compare the values much more easily. – VBAEnthusiast
I would still suggest that you don't make your ultimate code work as UDFs. Use a UDF as a simple wrapper that just calls your proper code. That way you only need to worry about the conversion from Excel data types to your internal data types at one layer, and not within everyone of your functions. – YowE3K

2 Answers

2
votes

A one-dimensional array is treated as being shaped as one row by x columns, so that is why your values are always coming out the same. (The entire result column is assigned the value of the first column returned.)

The following code works for your situation (but will undoubtedly give issues if you change things to work on rows instead of columns):

Function VaRScenariosTest(ByRef dblRealRates() As Variant) As Variant()
    Dim intCount As Integer
    Dim dblTemp() As Double

    For intCount = LBound(dblRealRates) To (UBound(dblRealRates) - 1)
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = dblRealRates(intCount + 1, 1) - dblRealRates(intCount, 1)
    Next intCount

    VaRScenariosTest = Application.Transpose(dblTemp)
End Function

Function Range2dblArray(ByRef rngRange As Range) As Variant()
    Dim dblTemp() As Double
    Dim intCount As Integer

    For intCount = 1 To rngRange.Count
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = rngRange.Cells(intCount)
    Next intCount

    Range2dblArray = Application.Transpose(dblTemp)
End Function

Alternatively, by doing the transpose of the results of Range2dblArray in the Excel formula itself

{=VaRScenariosTest(TRANSPOSE(Range2dblArray(E2:E21)))}

you can leave the original Range2dblArray untouched and only modify VaRScenariosTest:

Function VaRScenariosTest(ByRef dblRealRates() As Variant) As Variant()
    Dim intCount As Integer
    Dim dblTemp() As Double

    For intCount = LBound(dblRealRates) To (UBound(dblRealRates) - 1)
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = dblRealRates(intCount + 1, 1) - dblRealRates(intCount, 1)
    Next intCount

    VaRScenariosTest = Application.Transpose(dblTemp)
End Function

Function Range2dblArray(ByRef rngRange As Range) As Double()
    Dim dblTemp() As Double
    Dim intCount As Integer

    For intCount = 1 To rngRange.Count
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = rngRange.Cells(intCount)
    Next intCount

    Range2dblArray = dblTemp
End Function

And by also doing the transpose of the results of VaRScenariosTest in the Excel formula itself

{=TRANSPOSE(VaRScenariosTest(TRANSPOSE(Range2dblArray(E2:E21))))}

you can allow VaRScenariosTest to also return a one-dimensional array (but will still need to pass dblRealRates into the function as a two-dimensional Variant array):

Function VaRScenariosTest(ByRef dblRealRates() As Variant) As Double()
    Dim intCount As Integer
    Dim dblTemp() As Double

    For intCount = LBound(dblRealRates) To (UBound(dblRealRates) - 1)
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = dblRealRates(intCount + 1, 1) - dblRealRates(intCount, 1)
    Next intCount

    VaRScenariosTest = dblTemp
End Function

Function Range2dblArray(ByRef rngRange As Range) As Double()
    Dim dblTemp() As Double
    Dim intCount As Integer

    For intCount = 1 To rngRange.Count
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = rngRange.Cells(intCount)
    Next intCount

    Range2dblArray = dblTemp
End Function
0
votes

Finally settled on the following version, as per @YowE3K guidance:

Function VaRScenariosTest(ByRef dblRealRates() As Variant) As Variant()
    Dim intCount As Integer
    Dim dblTemp() As Double

    For intCount = LBound(dblRealRates) To (UBound(dblRealRates) - 1)
        ReDim Preserve dblTemp(1 To intCount)
        dblTemp(intCount) = dblRealRates(intCount + 1) - dblRealRates(intCount)
    Next intCount

    VaRScenariosTest = Application.Transpose(dblTemp)
End Function

Function Range2Array(ByRef rngRange As Range) As Variant()
    Dim varTemp() As variant
    Dim intCount As Integer

    For intCount = 1 To rngRange.Count
        ReDim Preserve varTemp(1 To intCount)
        varTemp(intCount) = rngRange.Cells(intCount)
    Next intCount

    Range2Array = varTemp
End Function

Works well when used in a spreadsheet, and only the data types of the function VarScenariosTest and array argument dblRealRates() have to be changed back to double if I want to use exclusively in VBA, not in the spreadsheet, although the variant version would work here as well. @YowE3K thank you for your help!