Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

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 

-m

 

Thanks in advance 

Mark

excel
MS Office Version: 2010
posted by: bmark0225 (rep: 2) on Thu Jul 22, 2021 at 7:55 pm
Answer
Discuss

Discussion

Mark

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: 6142) Jul 23, '21 at 7:00 pm
Thanks John,
How do i translate linest array to vba code ? 

thanks
mark 
bmark0225 (rep: 2) Jul 24, '21 at 6:45 am
Mark
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: 6142) 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 
Thanks
-M
bmark0225 (rep: 2) Jul 24, '21 at 8:46 am
Mark

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: 6142) 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 ? 

thanks
-m
bmark0225 (rep: 2) Jul 25, '21 at 12:56 pm
Mark

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: 6142) 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