Why is this UBound not identifying the correct limit?

Question

I am new to using VBA within excel and am trying to implement a function to perform an algebraic operation on an array of numbers. This function requires creation of sub arrays with sizes relative to the upper and lower bounds of the parameter array.

I have the following function with the TestFEMISS() sub following it.

Function FEMISS(ParamArray arr() As Variant) As Variant
    Dim a() As Variant
    Dim b() As Variant
    Dim i() As Variant
    
    i = arr
    
    Dim maxA As Long
    Dim minB As Long
    Dim maxB As Long
    
    maxA = UBound(i) - 1
    minB = LBound(i) + 1
    maxB = UBound(i)
    
    ReDim a(0 To maxA)
    ReDim b(minB To maxB)
    
    For x = 0 To maxA
        a(x) = i(x)
    Next x
    
    For x = minB To maxB
        b(x) = i(x)
    Next x
    
    Dim tempArr() As Variant
    ReDim tempArr(0 To maxA)
    
    For x = 0 To maxA
        tempArr(x) = 1 / ((1 / a(x)) + (1 / b(x + 1)) - 1)
    Next x
    
    FEMISS = tempArr
End Function

Sub TestFEMISS()
    Dim testArr(0 To 2) As Variant
    Dim outputArr() As Variant
    
    testArr(0) = 0.1
    testArr(1) = 0.8
    testArr(2) = 0.8
    
    outputArr = FEMISS(testArr)
    
    MsgBox outputArr(0) & "," & outputArr(1)
    
End Sub

When I run, it bugs because it is returning 0 for both the UBound and LBound limits, and I am not sure why. In my excel sheet, I have two different cells: B21 and B22 where I am testing this formula. In B21 I have =FEMISS('Fenestration Eqns'!B16#), which is supposed to reference a dynamic array that currently contains three cells with the values 0.1,0.8,0.8; but it is only giving me an error. B22 contains =FEMISS(0.1,0.8,0.8) and is currently outputting 2 cells of calculations done perfectly as expected.

I have tried converting the dynamic array to a text string in a single cell and modifying the UBound dimensions and neither has worked. Once I make it any parameter that references the dynamic array eventually, even in nested formulas, it collapses.

Answer 1

You probably misunderstood the idea of ParamArray. ParamArray is used when you have a undefined number of parameters (of any kind), eg when you want to call a routine with

FEMISS p1    
FEMISS p1, p2
FEMISS "ABC", p2, 123

For more on paramArray, see the documentation

You call your routine with one parameter (which is an array, but it could be of any other type), so LBound(arr) and UBound(arr) are 0.

arr(0) would give you the array you passed as (only) parameter, so LBound(arr(0)) and UBound(arr(0)) would give you 0 and 2.

Function FEMISS(ParamArray arr() As Variant) As Variant
    i = arr
    Debug.Print LBound(i), UBound(i)
    i = arr(0)
    Debug.Print LBound(i), UBound(i)

This would work, but makes it more complicated then neccessary. Just remove the ParamArray in the function declaration. Now the function expects exactly one parameter, and this parameter needs to be an array (of type Variant).

Function FEMISS(arr() As Variant) As Variant
    Debug.Print LBound(arr), UBound(arr)