Finding the Coefficients of a Quadratic Equation using VBA

[kyleg222]

Hello Everyone,

I am plotting a graph with a quadratic fit and require the coefficients of the equation to use for further calculations. These calculations are done in VBA.

I have come across the forum thread at the following link
http://www.excelforum.com/excel-gene...fficients.html
It says to use

Please Login or Register  to view this content.

However that is inside a cell. Now trying to adapt that to vba code I receive an error highlighting the curly brackets ({) stating that it cannot be used in that location.

My code is as follows:

Please Login or Register  to view this content.

That does not work, however if the ^{1,2} is removed it works perfectly for a linear fit. It is imperative that I can extract the coefficients of the quadratic fit.

Thank you.

[MrShorty]

the brackets {} are how Excel indicates an array constant in the spreadsheet. The effect in the spreadsheet is to create a 2D array that would look like this if expanded in the spreadsheet:

Please Login or Register  to view this content.

So, adapting this to VBA is going to require figuring out how to get a similar effect in VBA. I haven't tested all possible ways of doing this, but I expect something like this should set up the known_x array effectively:

Please Login or Register  to view this content.

[kyleg222]

Hello Mr. Shorty,

Thank you for your reply. I have adapted my code to make a 2D array of the X values to be used in the LinEst function, however I get an error on the LinEst line of code.

The error is the following:

Run time error '1004' : Unable to get LinEst property of the Worksheet Function class.

I have 2 arrays one for the y values called K1L and a 2D array for the x values and x² values called xL.

my xL array is as follows:

Please Login or Register  to view this content.

and my K1L array is:

Please Login or Register  to view this content.

I understand that this looks linear (and probably is very close), however everytime I run the code the arrays will change and a quadratic fit will be ncessary.

It seems like it should work with this line of code, but it gives the error above.

Please Login or Register  to view this content.

Thank you for your help.

[MrShorty]

Just like in a spreadsheet, the dimensions on the known_y and known_x arrays have to match up. The quick code I posted created known_x as a nx2 array. I'm assuming your known_y is a 1xn array?? I think it should work as long as you adapt the dimensions on the arrays so they "match." Either make your known y array an nx1 array, or adapt my known_x array so it is 2xn.

[kyleg222]

Hello MrShorty,

From what I shown in my previous post I have a nx2 array for my X and an nx1 array for my y values. Therefore by your response what I have now should work. I don't know why you would assume that I have a 1xn array of y values. My y values are in one column and my x values in another and the x^2 in yet another column. The values of y, x and x^2 are in the rows, therefore giving nx2 and nx1 for x and y respectively. Here is the small section of code that performs the loop. From my limited work in VBA it seems that the indexing for rows and columns begins at 0 and not 1. The arrays used for the LinEst function is xL(xvalues) and K1L(yvalues), the xVal array is the xvalues that are not squared such that I can create a plot and show the best fit. I am than trying to use LinEst to extract the coefficients for further calculations.

Please Login or Register  to view this content.

[MrShorty]

I don't know why you would assume that I have a 1xn array of y values.

Because it is the same mistake I made??

I don't know why this is so, but when you have only one dimension to an array (like your K1L array), VBA/LINEST sees it as a 1xn array, not an nx1 array. Try switching your indices. Either:

xL(0,i-1)=...
or
K1L(i-1,1)=...

[kyleg222]

Hello MrShorty,

It was a mistake that I had in dimensioning my arrays. I originally had

Please Login or Register  to view this content.

Assuming that if you only put one index that would represent the rows, however by explicitly declaring it to have one column it worked as follows

Please Login or Register  to view this content.

Thank you for your help and I am sorry if I offended you by questioning your assumption, you were very helpful.