Results 1 to 4 of 4

Help getting function to output into two cells

Threaded View

Hammer500 Help getting function to... 02-16-2012, 11:09 PM
protonLeah Re: Help getting function to... 02-17-2012, 12:11 AM
MrShorty Re: Help getting function to... 02-17-2012, 12:45 AM
Hammer500 Re: Help getting function to... 02-17-2012, 12:50 AM
  1. #1
    Registered User
    Join Date
    02-16-2012
    Location
    AB
    MS-Off Ver
    Excel 2010
    Posts
    2

    Help getting function to output into two cells

    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
    Last edited by Hammer500; 02-17-2012 at 12:30 AM. Reason: added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1