VBA to find the distance from one points to surface (Best fit plane)


Hi forum ,

I have an excel sheet (got from someone in the past) to calculate the distance from one points to the best-fit plane defined by a cloud of points

All the excel fomulars are discribed in the excel file (see attached) 

I would want to remove the formular and replace them by VBA code 

Can anyone help to code it by using VBA  ? 




Thanks in advance 





Unless you can describe the mathematics of this for us, you may not get an answer (in my case, it's many years since I did linear regression etc.).

Guess you're aware that you can use a LINEST array formula to get the parameters of a best-fit plane for xyz co-ordinates but why do you need VBA to do that?
John_Ru (rep: 1917) Jul 23, '21 at 7:00 pm
Thanks John,
How do i translate linest array to vba code ? 

bmark0225 (rep: 2) Jul 24, '21 at 6:45 am
Please edit your original question and use Attach Files... button to upload an Excel file with your 8 coordinates. I'll look later today 
John_Ru (rep: 1917) Jul 24, '21 at 6:54 am
Hi John,
Fiile is attached  If you open the file , one measure file will have : 
1 center point (point9) xyz measurement 
8 points outer ring xyz measurement 
8 points inner ring xyz measurement 
what i need are Z distance from center points to 8 points outer ring and Z distance from center points to 8 points inner ring
Hope it is clear enough 
I am old , not good about it at all 
bmark0225 (rep: 2) Jul 24, '21 at 8:46 am

Don't think I'll be able to help you with this one, sorry. I'm no expert on LINEST and your  data is peaky so doesn't produce a good fit (from the r-squared coefficient of determination).

Is this exercise for work or study BTW? Good luck with it either way.

Next time, please ask a full question to begin with and include a representative file.
John_Ru (rep: 1917) Jul 24, '21 at 11:46 am
 Thanks John
I found an excel sheet which works well ,
Just if possible ,only if not take much your time , would your help to convert it to vba ? 

bmark0225 (rep: 2) Jul 25, '21 at 12:56 pm

I have no more time today but I don't understand what you mean by "convert it to VBA".

I assume you realise that VBA is Excel's built-in programming language so can do many things. Trouble is I don't know what you are expecting to see,.

Please EDIT your original question to explain (then I or other contributors might respond appropriately).
John_Ru (rep: 1917) Jul 25, '21 at 1:39 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login