I am new to VBA and created the following function on i want "Nn" to output into the active cell and "En" and in the next column over. i just don't know how to get the function to do it. Please help.
Public Function online(N, E, N_range, E_range) As Variant
'This Function takes a point off centerline and finds the perpendicular coordinate on the alignment
'Intermediate Eastings values
E1 = Application.VLookup(E, E_range, 1)
E2 = Application.Index(E_range, Application.Match(E1, E_range) + 1)
'Intermediate Northings values
N1 = Application.Index(N_range, Application.Match(E1, E_range))
N2 = Application.Index(N_range, Application.Match(E1, E_range) + 1)
'Calculates components of the equation of the line
a = ((N1 - N2) / (E1 - E2))
B = -1
c = N1 - a * E1
'calculates perpendicular distance between point to the line
d = Abs(a * E + B * N + c) / ((a ^ 2 + B ^ 2) ^ (0.5))
'calculates distance between point and point (N1, E1)
h = Sqr((N - N1) ^ 2 + (E - E1) ^ 2)
'calculates the distance to the new point on the line
l = Sqr(h ^ 2 - d ^ 2)
'calculates new Easting for E1
If E2 > E1 Then
En = E1 + l * Sqr(1 + a ^ 2)
Else
En = E1 - l * Sqr(1 + a ^ 2)
End If
'calculates new Northing
Nn = a * En - B + c
online=Nn
End Function
Bookmarks