Feed arrays into trend function in VBA, results wrong

Question

Can anybody advise why the excel trend function (in VBA) does not give a result when working with an array of the type variant and why it gives a wrong result when working with an Dim arr() as Integer type of array?

I try to feed two arrays with y and x values into the Excel trend function to get a new y value for a new x value. However, the resulting values are wrong. It has something to do with passing a variant/integer array into the excel function.

I retrieve a matrix via sql that holds day values in one column and number values in another column (there are multiple columns with number values, but this is not the problem here so I used only one column of number values as an example).

FMV_Day FMV_10000
365      65
548      63
730      61
913      59
1095     57
1278     55
1460     53
1643     51
1825     49
365      65
548      63

I use VBA to store the values from the sql query in an array. One array for the days, one array for the number values. The arrays are declared as "Dim percentages() As Integer Dim days() As Integer". This produces results, but they are way off. When I declare the arrays as "Dim values As Variant" etc. the script wont run and result the error message: "Unable to get Trend property of Worksheet function class." The key part of the script looks as follows...

For Each element In columns
    If element = "Day" Then

        If Not lrs1.EOF Then

            lrs1.MoveFirst   'Ensure we begin on the first row

            iCounter = 0
            ReDim days(intArraySize) 'Need to size the array

            Do Until lrs1.EOF
                days(iCounter) = lrs1.Fields("FMV_" & mileage_element)
                'Debug.Print "Item: "; iCounter & " " & days(iCounter)
                iCounter = iCounter + 1
                lrs1.MoveNext
            Loop

        End If

    lrs1.MoveFirst

    ElseIf element = element_given Then
        If Not lrs1.EOF Then

            lrs1.MoveFirst   'Ensure we begin on the first row

            iCounter = 0
            ReDim values(intArraySize) 'Need to size the array

            Do Until lrs1.EOF
                values(iCounter) = lrs1.Fields("FMV_" & element)
                'Debug.Print "Item: "; iCounter & " " & values(iCounter)
                iCounter = iCounter + 1
                lrs1.MoveNext
            Loop

        End If
    End If
Next element
fnc_FMV = Excel.Application.WorksheetFunction.Trend(values, days, Array(liAge))(1)

Here is one example for the erroneous results that I get when working with an array that was defines as an Integer array:

Days-Array:            365, 548, 730, 913, 1095, 1278, 1460, 1643, 1825
Values-Array:          65, 63, 61, 58, 56, 54, 52, 50, 48
Age                    126 
Result from Trend_fnc  41.7662243865297

The correct result for the above example would be 67.75165. This is derived by me through simply using the same values from the days and values array in an Excel spread sheet and applying the trend function.

EDITEDITEDIT

I am not using Excel. I am exclusively using access to solve this problem, since everything else of this rather large script is in access and I am querying a database.

Ok I tried your suggestion and changed the following within the both Do loops from above:

ReDim days(1 To intArraySize, 1 To 1) 

and

ReDim value(1 To intArraySize, 1 To 1)

I added another dimension for the array. Don't know what this should help... anyway, now I am getting a sub script out of range error for line

days(iCounter) = lrs1.Fields("FMV_" & mileage_element)

EDIT2 I used

Debug.Print days(0) & ", " & days(1) & ", " & days(2) & ", " & days(3) & ", " & days(4) & ", " & days(5) & ", " & days(6) & ", " & days(7) & ", " & days(8) 
Debug.Print values(0) & ", " & values(1) & ", " & values(2) & ", " & values(3) & ", " & values(4) & ", " & values(5) & ", " & values(6) & ", " & values(7) & ", " & values(8) 

to make sure the values I retrieve via sql are correct and correctly put into the array. All the inputs are correct, but the trend function messes up the result. I believe it has something to do with the datatype of the arrays that go into the trend function. I believe that if I would use a Variant datatype this error would not happen, but I get another error, when I try to enter a Variant array into the trend function. "Unable to get Trend property of Worksheet function class."

Answer 1

So, with some external help I found the solution. The arrays I Redim'ed were one slot too large. I needed nine slots and dim'ed for 9, but since arrays start at 0 it actually had 10 slots. The empty slot was automatically filled with a zero which subsequently altered the calculation. In the above code the following needs to be changed:

            ReDim days(intArraySize-1) 'Need to size the array

            ReDim percentages(intArraySize-1) 'Need to size the array

The minus one is the solution. This took me 2 and a half days. WTF. Thanks everyone!

Answer 2

As mentioned, the Trend result for me as well is 67.75, and I get the same result with integer arrays and variant ones.

The thing is, you have to provide arrays for the first three parameters (Known_ys, Known_xs, New_xs). The result is also a Variant array with the same items count as the New_xs array (or if not provided the Known_xs array).

This shows both Integer and Variant provided arrays results:

Sub useTrend()
    Dim x As Variant
    Dim y As Variant
    Dim r As Variant
    Dim xi(8) As Integer
    Dim yi(8) As Integer
    Dim ri As Variant
    Dim i As Integer
    x = Array(365, 548, 730, 913, 1095, 1278, 1460, 1643, 1825)
    For i = 0 To UBound(x)
        xi(i) = x(i)
    Next
    y = Array(65, 63, 61, 58, 56, 54, 52, 50, 48)
    For i = 0 To UBound(y)
        yi(i) = y(i)
    Next
    r = WorksheetFunction.Trend(y, x, Array(126))
    ri = WorksheetFunction.Trend(yi, xi, Array(126))
End Sub

Both results r and ri are Variant arrays of (1 to 1), with a Double item r(1) = 67.7516453919795

Answer 3

Based on your data, I found the trendline to be y = -0.01178x + 69.23604

This means that at x=126, y = 67.75164...

Option Explicit
Sub TrendAnalysis()

  Dim vDays As Variant
  Dim vValues As Variant
  Dim newX As Variant

  newX = Array(126)

  vDays = Array(365, 548, 730, 913, 1095, 1278, 1460, 1643, 1825)
  vValues = Array(65, 63, 61, 58, 56, 54, 52, 50, 48)

  Dim Test As Variant
  Test = WorksheetFunction.Trend(vValues, vDays, newX, True)

End Sub