Line intersection - with a difference

T

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
I have been working on a problem and through all of my research I have been unable to find an existing resolution. I do not have the required math knowledge to produce an elegant solution.

There are several examples of working (and very elegant) line - line intersections with either complete lines or line segment (see links) :

https://dl.dropboxusercontent.com/u/99937114/Forums/Eileens Lounge - LinearIntersect_Corrected.xls

https://dl.dropboxusercontent.com/u/99937114/Forums/Eileens Lounge - ymxc Intersect.xls

https://dl.dropboxusercontent.com/u/99937114/Forums/Listen Data - Intersection.xls

https://dl.dropboxusercontent.com/u/99937114/Forums/AJP - ComplexIntersection.xls

The problem I have is illustrated using the attached image. There is a defined line, starting at L1 (x,y) and finishing at L2 (x,y). There is no second line - instead there are the coordinates for another point (effectively L3) and an axis or angle of travel. I need to calculate the distance to and coordinates of the intersect if the point L3 was continued along the axis / angle of travel.

Line%20-%20Point%20intersection%20at%20an%20angle.jpg
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
It would also be useful to get the coordinates of a position when provided with an origin, a bearing and a distance e.g. x, y with a distance of 10m on a bearing of 195 degrees arrives you at x, y?
 
Upvote 0
Each line can be expressed parametrically as an origin and some variable distance along a direction vector:

Line1 = point1 + s * (point2 - point1)

Line2 = point3 + t * {sin(angle), cos(angle)}

Some value of s and t is where the lines intersect.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][/tr]
[tr][td]
1​
[/td][td]
​
[/td][td]
x​
[/td][td]
y​
[/td][td]
angle​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
2​
[/td][td]Point1[/td][td]
3​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]Point2[/td][td]
13​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]Dir[/td][td]
10.000​
[/td][td]
1.000​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B4:C4: {=B3:C3 - B2:C2}[/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]Point3[/td][td]
23​
[/td][td]
16​
[/td][td]
195​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]Dir[/td][td]
-0.259​
[/td][td]
-0.966​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B8: =SIN(RADIANS(D7))[/td][td]C8: =COS(RADIANS(D7))[/td][td][/td][/tr]

[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td][/td][td]
10.000​
[/td][td]
0.259​
[/td][td]
*​
[/td][td]
s​
[/td][td]
=​
[/td][td]
20​
[/td][td][/td][td]B11: =B4[/td][td]C11: =-B8[/td][td]G11: =B7-B2[/td][/tr]

[tr][td]
12​
[/td][td][/td][td]
1.000​
[/td][td]
0.966​
[/td][td]
​
[/td][td]
t​
[/td][td]
​
[/td][td]
13​
[/td][td][/td][td]B12: =C4[/td][td]C12: =-C8[/td][td]G12: =C7-C2[/td][/tr]

[tr][td]
13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td]
s​
[/td][td]
1.70​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B14:B15: {=MMULT(MINVERSE(B11:C12), G11:G12)}[/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td]
t​
[/td][td]
11.70​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td]Check 1[/td][td]
19.971​
[/td][td]
4.697​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B17:C17: {=B2:C2 + B14 * B4:C4}[/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td]Check 2[/td][td]
19.971​
[/td][td]
4.697​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B18:C18: {=B7:C7 + B15 * B8:C8}[/td][td][/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
Or more compactly,

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td]
​
[/td][td]
x​
[/td][td]
y​
[/td][td]
angle​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
2​
[/td][td]Point1[/td][td]
3​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]Point2[/td][td]
13​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]Dir[/td][td]
10.000​
[/td][td]
1.000​
[/td][td][/td][td][/td][td]B4:C4: {=B3:C3 - B2:C2}[/td][td][/td][/tr]

[tr][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]Point3[/td][td]
23​
[/td][td]
16​
[/td][td]
195​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]Dir[/td][td]
-0.259​
[/td][td]
-0.966​
[/td][td][/td][td][/td][td]B7: =SIN(RADIANS(D6))[/td][td]C7: =COS(RADIANS(D6))[/td][/tr]

[tr][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
s​
[/td][td]
1.70​
[/td][td][/td][td][/td][td][/td][td]B9:B10: {=MMULT(MINVERSE(CHOOSE({1,2;3,4}, B4, -B7, C4, -C7)), CHOOSE({1;2}, B6-B2, C6-C2))}[/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
t​
[/td][td]
11.70​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]Check1[/td][td]
19.971​
[/td][td]
4.697​
[/td][td][/td][td][/td][td]B12:C12: {=B2:C2 + B9 * B4:C4}[/td][td][/td][/tr]

[tr][td]
13​
[/td][td]Check2[/td][td]
19.971​
[/td][td]
4.697​
[/td][td][/td][td][/td][td]B13:C13: {=B6:C6 + B10 * B7:C7}[/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
Wow this is fantastic. Is there a VBA function version of this, so that I can call it on command - I have some large data sets where I have the information in the format provided.

If not I may be able to produce a template method, but the VBA would give me more freedom :)

I have been looking at a website called moveable typeface which covers alot of this, but it is all web orientated unfortunately
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td][/td][td]
x​
[/td][td]
y​
[/td][td]
angle​
[/td][td][/td][/tr]

[tr][td]
2​
[/td][td]Point1[/td][td]
3​
[/td][td]
3​
[/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]Point2[/td][td]
13​
[/td][td]
4​
[/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td]Point3[/td][td]
23​
[/td][td]
16​
[/td][td]
195​
[/td][td][/td][/tr]

[tr][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]Isect[/td][td]
19.971​
[/td][td]
4.697​
[/td][td][/td][td]B7:C7: {=Isect(B2, C2, B3, C3, FALSE, B5, C5, SIN(RADIANS(D5)), COS(RADIANS(D5)), TRUE)}[/td][/tr]
[/table]

Code: 
Function Isect(x1 As Double, y1 As Double, ByVal x2 As Double, ByVal y2 As Double, Pt2AsDir As Boolean, _
               x3 As Double, y3 As Double, ByVal x4 As Double, ByVal y4 As Double, Pt4AsDir As Boolean) As Variant

  ' shg 2014

  ' Returns the point of intersection of two lines
  ' Pt2AsDir = True  =>  means {x2, y2} is a direction vector
  ' Pt2AsDir = False =>  means {x2, y2} is a second point on the line

  Dim s             As Double
  Dim v             As Variant

  If Pt2AsDir Then
    x2 = x2 + x1
    y2 = y2 + y1
  End If

  If Pt4AsDir Then
    x4 = x4 + x3
    y4 = y4 + y3
  End If

  v = IsectPrams(x1, y1, x2, y2, x3, y3, x4, y4)
  If VarType(v) = vbBoolean Then
    Isect = CVErr(xlErrValue)
  Else
    s = v(0)
    Isect = VBA.Array(x1 + s * (x2 - x1), y1 + s * (y2 - y1))
  End If
End Function

Function IsectPrams(x1 As Double, y1 As Double, _
                    x2 As Double, y2 As Double, _
                    x3 As Double, y3 As Double, _
                    x4 As Double, y4 As Double) As Variant

' shg 2013

  Dim d             As Double   ' denominator of s and t

  d = (x4 - x3) * (y2 - y1) - (x2 - x1) * (y4 - y3)

  If d = 0 Then
    IsectPrams = False
  Else
    IsectPrams = VBA.Array(((x4 - x3) * (y3 - y1) - (x3 - x1) * (y4 - y3)) / d, _
                           ((x2 - x1) * (y3 - y1) - (x3 - x1) * (y2 - y1)) / d)
  End If
End Function
 
Upvote 0
shg said:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[TD]
y​
[/TD]
[TD]
angle​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Point1[/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Point2[/TD]
[TD]
13​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Point3[/TD]
[TD]
23​
[/TD]
[TD]
16​
[/TD]
[TD]
195​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Isect[/TD]
[TD]
19.971​
[/TD]
[TD]
4.697​
[/TD]
[TD][/TD]
[TD]B7:C7: {=Isect(B2, C2, B3, C3, FALSE, B5, C5, SIN(RADIANS(D5)), COS(RADIANS(D5)), TRUE)}[/TD]
[/TR]
</tbody>[/TABLE]


Code: 
Function Isect(x1 As Double, y1 As Double, ByVal x2 As Double, ByVal y2 As Double, Pt2AsDir As Boolean, _
               x3 As Double, y3 As Double, ByVal x4 As Double, ByVal y4 As Double, Pt4AsDir As Boolean) As Variant

  ' shg 2014

  ' Returns the point of intersection of two lines
  ' Pt2AsDir = True  =>  means {x2, y2} is a direction vector
  ' Pt2AsDir = False =>  means {x2, y2} is a second point on the line

  Dim s             As Double
  Dim v             As Variant

  If Pt2AsDir Then
    x2 = x2 + x1
    y2 = y2 + y1
  End If

  If Pt4AsDir Then
    x4 = x4 + x3
    y4 = y4 + y3
  End If

  v = IsectPrams(x1, y1, x2, y2, x3, y3, x4, y4)
  If VarType(v) = vbBoolean Then
    Isect = CVErr(xlErrValue)
  Else
    s = v(0)
    Isect = VBA.Array(x1 + s * (x2 - x1), y1 + s * (y2 - y1))
  End If
End Function

Function IsectPrams(x1 As Double, y1 As Double, _
                    x2 As Double, y2 As Double, _
                    x3 As Double, y3 As Double, _
                    x4 As Double, y4 As Double) As Variant

' shg 2013

  Dim d             As Double   ' denominator of s and t

  d = (x4 - x3) * (y2 - y1) - (x2 - x1) * (y4 - y3)

  If d = 0 Then
    IsectPrams = False
  Else
    IsectPrams = VBA.Array(((x4 - x3) * (y3 - y1) - (x3 - x1) * (y4 - y3)) / d, _
                           ((x2 - x1) * (y3 - y1) - (x3 - x1) * (y2 - y1)) / d)
  End If
End Function
Click to expand...

Amazing! I will look through this tonight and have a play - producing a mockup of what the solution should hopefully do :)

Once this is solved, I can hopefully finish the addin!
 
Upvote 0
shg said:
You're welcome, good luck.
Click to expand...

It works perfectly. I will upload a working example of the workbook (rough) with some direction.

I like how the line is created using a single point and an angle

I would like to create two infinite lines / calculate the intersect using either [two coordinates] or [a single point and a slope / bearing] in different combinations as input parameters - how would one change the VBA? (basically adjusting what you have already created) e.g.

[line 1 = x and y coordinates] and [line 2 = single point and bearing]

or

[line 1 = single point and bearing] and [line 2 = single point and bearing]

or

[line 1 = x and y coordinates] and [line 2 = x and y coordinates]

This would give me fantastic flexibility. And then I could expand to calculate the distance between the single point and the intersect :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Find intersection of two independent ranges and sum values at every coordinate
Replies
9
Views
662
KRice
K
A
  • Question Question
VBA to generate and draw vertical and horizontal lines following some rules
Replies
1
Views
2K
diddi
diddi
M
  • Question Question
Trig Calculation Error in VBA Excel Macro
Replies
3
Views
2K
Machinist R1
M
J
  • Question Question
Import 2 Different Text File Formats with Error Check
Replies
0
Views
977
JRNelson
J
T
  • Question Question
Calculate Intersect of 2 lines
Replies
5
Views
3K
shg
shg

Forum statistics

Threads
1,226,504
Messages
6,191,424
Members
453,657
Latest member
DukeJester

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top