Polynomial regression in VBA

[datanewb]

Hey there,

I have two arrays of data on which I would like to be able to run a polynomial regression in VBA.

However, when write the following in VBA:

Dim poly_3 as Variant
Dim arr1 as Variant
Dim arr2 as Variant
 
poly_3 = WorksheetFunction.LinEst(arr1, my_arr2 ^ {1, 2, 3})

I get a compile error, as {} are not valid characters. I've tried substituting parenthesis and leaving the brackets out completely, but that does not work either.

Anyone know how to perform a polynomial regression in VBA?

Working with two ranges of cells in a worksheet, the Linest function works just fine. ie, =LINEST(H18:H34, G18:G34^{1,2,3}), so I don't know why it would not be the same in VBA.



Office 2007

 

[baitmaster]

you might try declaring the size of your two arrays arr1 and arr2, and also breaking out {1,2,3} as a separate array, and including the array name in your formula?

 

[datanewb]

Right, sorry for posting incomplete code. I should have left the declarations out completely for the sake of simplicity. It was careless of me to quickly include them with the errors you mentioned. Please forget about the array declarations.

The problem is figuring out how to do a polynomial regression in VBA.

The following line of code does not work.

poly_3 = WorksheetFunction.LinEst(new_array, my_array ^ {1, 2, 3})

I think you are on to the answer. The {1,2,3} needs to be a separate array.