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

Find The Z distance between one point to adjacent points

0

Hi all

Would be very appreciated if anybody can help me with the code  to the Z distance between one point ( X0,Y0) to adjacent  points around it ,

Basically , in the set of Z ( height) data of about 10000 point (X,Y)  ,  I would like to know the Z different between each to neighbor point , add 2 column at the end , one is record the max Z different abd the other is which point ID belong to have that max Z delta (different) 

In the attached file , on the Column B ( Z Measurement ) , Just ignore all the text such as Nottest  and treat as that point ID doesn't exist, If see Fail then give the msg box :" There is atleast one fail ", otherwise continue to perform calculation the max Z delta for each point ID to all neighbor points as long as the distance (X,Y) between them are less than 100 ( center to center )

I am truely appreciate if you can help me with this 

Answer
Discuss

Discussion

It seems to me that this is a task for worksheet functions, not VBA. Therefore there couldn't be a MsgBox. You "would like to know the Z different between each to neighbor point". You should provide detail how to calculate this difference and where to write it. The "max Z delta" (or "Z Delta Max") sounds like a maximum. If it is the maximum of all 10,000 measurements it would be the same number repeated in all 10,000 rows. If it isn't that, then what is it and how is it calculated? Consider making a manual calculation in a couple of rows of your sample sheet to demonstrate the result you want. If the solution is indeed achieved with worksheet functions only the message could appear in column G. However, so far the required calculation is far from clear. There as at least one FAIL in your sample data and therefore the message should appear in all rows.
Variatus (rep: 4889) Jul 18, '18 at 6:03 am
Thanks for a quick reply ,
First , let me change the condition that i mentioned above , which is distance from point to point from 100 to 400  ,
Second , let take the point ID 2 ( Column Point ID ) which is located at X.Y  ( 6613.492,-2518.836)
Third , Find all neighbor probes which has distance to this point  less than 400 , In this case point ID 1 and point ID 3 and point ID 91  has distance to point ID 2 are less than 400  
Fourth , take the Z measurement of point ID1 (1.628) , Point ID 3(50), Point ID 91 (-5.488) then find the max abs distance to Z of point ID 2 (-1.177):
abs (Z(ID1) - Z(ID2))=abs(1.628 -(1.177) )= 2.805
abs(Z(ID3) - Z(ID2))=abs(50-(-1.177))=51.177
abs(Z(ID91)-Z(ID2))=abs((-5.488)-(-1.177))=4.311
Fifth, Take the max value of 3 results above ( 51.177) and paste to cell E3 which belong to point ID2 and take the point ID which is associate with max value above and paste to next cell (F3) , in this case is 3 ( point ID 3) 
For now , before we do caculation , just remove all row which has nonnumber in the Z measurement column 
We do it for all point ID ,

Thanks,
Mark 
bmark0225 (rep: 2) Jul 18, '18 at 9:05 am
Hi Mark, How to calculate the distance between points? How are points 1, 3 and 94 within a range of 400 from each other?
Variatus (rep: 4889) Jul 18, '18 at 9:24 pm
Thank you ,
The distance from point 1 to point 2 is:  D = SQRT (( X1-X2)*(X1-X2) +(Y1-Y2)*(Y1-Y2))

Many Thanks,
Mark 
bmark0225 (rep: 2) Jul 18, '18 at 9:41 pm
Add to Discussion

Answers

0
Selected Answer

I have taken this as far as I can. Now it's your turn to check. The problem is that the code in the attached workbook doesn't produce the result you have indicated, to wit, Point 1 to match with points 3 and 93. My program gets 2, 83 and 88. But first the basics.

The procedure to run is called Sub Calculate_ZDistance. It is located in the code module Z_Distance which also has some supporting procedures. There is no code anywhere else and you can transfer the entire module by dragging it into your own project in the Project Explorer window while both workbooks, the attached and your own, are open. I presume that you know how to find the code module and run the procedure. If not, please ask.

There are a lot of comments in the code which I have added to enable you to adapt it to another workbook where rows and columns might be different. It is very easy to modify because I designed it to be easy. Please read the instructions in the code.

While doing so, please look for the part of the procedure Calculate_ZDistance which I have copied here.

                ' Disable one of the next two lines of code by prefixing it with an apostrophe:
'                If Tmp <= NdcMax And Tmp >= NdcMin Then
                If Tmp <= NdcMax Then
                    Tmp = Abs(ZData(NzdZ, R) - ZData(NzdZ, i))
                    Debug.Print "Row "; R; " - Row "; i; " = "; ZData(NzdZ, R); " - "; ZData(NzdZ, i), "Result = "; Tmp
                    If Tmp > Max(NmxMax) Then
                        Max(NmxMax) = Tmp
                        Max(NmxId) = i
                    End If
                End If
            End If
        Next i
        Stop

At the top of this excerpt it says to disable one of the first two lines of code. The first line demands a minimum AND a maximum value, the second one a maximum only. The two named variables NdxMin and NdcMax are defined at the top of the code sheet as 100 and 400 respectively. You can modify the values there. Currently the first line is disabled, meaning the code looks for a value < 400 only, without minimum.

The code line Debug.Print "Row "; R; " - Row "; i; " = "; ZData(NzdZ, R); " - "; ZData(NzdZ, i), "Result = "; Tmp prints a result to the Immediate Window as shown below.

Row  1  - Row  2  =  1.628  - -1.177      Result =  2.805 
Row  1  - Row  83  =  1.628  -  5.625     Result =  3.997 
Row  1  - Row  88  =  1.628  - -5.488     Result =  7.116 

Each line represents one comparison with a value <400 found when comparing the first item ("Row 1") with rows 2, 83 and 88. The largest of these was selected and printed to E2, in this case 7.116 from row 88 which is written to F2.

At the bottom of the above excerpt there is a Stop command. Code execution will be halted to allow you to examine the result. Press F5 to continue with the next item. There are 95 rows in the sample. I don't know what to look for.

When you are done checking please disable the Debug.Print line and the Stop command, or delete the lines completely so that you can see the entire functionality. Let me know what errors you find and I'll fix them.

======================= Edit Jul 22, 2018

In order to question your data you need absolute confidence in my program - confidence I myself don't have. Therefore I have attached a second workbook (today's date in its name). All is identical with the first except that it contains extra code to output interim results. But first, please understand what you should be looking for.

My macro first extracts the relevant data from your Sheet1 and writes them into memory. This accounts for the speed of the program. Irrelevant data are counted and removed. Now you have three row IDs. There is the worksheet row, the Point ID and the array vector number. The code first works with the row number and later with the vector number. The Point ID is carried as as means of checking but the program doesn't (can't) check. Therefore this is what you should do before you doubt your data. Make sure that the program actually processes the data it thinks it processes. Of coruse I have checked this carefully. You should do it metitiously. The new additions to the code assist.

When you run the code a new worksheet will be appended called "Data". It contains a dump of the data first extracted for processing. Observe that the columns X, Y and (Z + ID) are extracted separately and later joined using their vector number. Its foolproof - but neither one of us is a fool; we find intelligent ways to go wrong. I have added a permanent copy of this dump to to the workbook in Sheet3. There I have added worksheet formulas which you might want to use for checking for discrepancies. But, as always, consider the possibility that the mistake isn't in the program but in the checking.

The program then goes on to check each of the items in the "Data" sheet against each other item in the "Data" sheet. (The worksheet isn't consulted anymore.) The new additions generate a worksheet for each item, giving it the name of each item's Point ID. Note that these sheets show the ID numbers of the items compared but the actual list was created using the underlying array's (ZData) vector numbers.

In both types of worksheets the columns have captions helping you to identify the data. The second type of sheet has the columns "in range" and "New Max". In range = True if the distance is <400 (with current macro settings). If the column shows False, the calculated distance isn't considered for the next check. The next check just determines the highest of the not eliminated measurements. At the start the highest is zero. Therefore the first result is a new maximum and the column shows True. Thereafter that value is compared with each subsequent measurement, top to bottom, and updated if a larger one is found. Therefore the last TRUE in the column is the final result.

I suggest that you check all the work the program does. If it works correctly - guaranteed by your scrutiny - the doubt your data. If you need more interim results let me know and I shall add them.

Discuss

Discussion

First of All , really really appreciate for you help ,
I will test it and let you know ,

Thank you so much !
Mark 
bmark0225 (rep: 2) Jul 19, '18 at 7:38 am
You are right , some how the "Max Delta point" are incorrect ,
Still trying to see where is a miss ,

Thank you so much !
Will share with you once i find it ,

Thanks again and much appriciate for your time/ your knowledge to help 

Mark
bmark0225 (rep: 2) Jul 21, '18 at 9:27 am
Wow ,
Your help is more than my expectation 

Thanks So Much !!
Will Test the code ,

Thanl\k you again 
bmark0225 (rep: 2) Jul 22, '18 at 11:11 pm
Happy BirthDay Variatus !!!
bmark0225 (rep: 2) Jul 26, '18 at 3:56 pm
Hi Mark,
Thank you for your wishes. Very observant! I do appreciate that - unfortunately not enough to take on your request for coding the charts :-) I shall see this question through to its, hopefully sweet, end though.
Variatus (rep: 4889) Jul 26, '18 at 9:05 pm
Thanks Variatus !!!
I got the chart by using pivot table and a little code for updating pivot data ,
So far so good ,

Thanks for your help ,

MM
bmark0225 (rep: 2) Jul 30, '18 at 9:22 pm
Add to Discussion


Answer the Question

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