I’m trying to multiply a variant array of coefficients by an independent variable stored in an excel sheet through a function in VBA to return the dependent value. The array has one integer in the beginning and the rest are doubles. The value in the cell is driven off of a formula and is a number, but when I try and input the array and the cell value into the function, it keeps saying something is the wrong data type.

Public eqCoefs As Variant

Function totalOffset(PA As Double) As Double
    
    'Declare calculation variables
    Dim degree As Integer
    Dim axialSB As Double
    Dim offset As Double
    Dim counter As Integer
    
    'Set count variables
    degree = CInt(eqCoefs(0))
    counter = 1
    
    'Match coefficients from regression graph to radial spring back and pitch angle with correct exponents
    For n = 1 To degree
        For i = 0 To n
            axialSB = axialSB + eqCoefs(counter) * (PA ^ (n - i))
            counter = counter + 1
        Next i
    Next n
    totalOffset = axialSB
End Function 

This is what is in the eqCoefs array, defined in another function: [4, -45.7635, -10423.7192, 0.0916, 125.3645, 13474.074, 0.0008, -0.19, -114.23, -7696.3461, 0.0, -0.0013, 0.105, 34.5752, 1638.0121, 3009.2862]

The number in the cell is 13.74, driven by the formula =DEGREES(ATAN((N25)/(PI()*coiling!$R$25)))

Any help would be appreciated.

I’ve tried putting CDbl in front of both numbers. I’ve tried taking in PA as a Range and getting the value. I’ve tried setting eqCoefs into a variable with a double data type and then multiplying. I’ve tried getting totalOffset to return a Variant instead of a double, nothing has worked.