[SOLVED] How do I add multiple VBA WorksheetFunctions in the same line?

How do I add multiple VBA WorksheetFunctions in the same line?

I have long formulas to calculate which launch point is closest to destination.

Run-Time Error 438
Object doesn't support this property or method.

Sub GetClosestLocation(dest_lat, dest_long)
Dim sp As Variant
Dim expr, a, b, c, launch_name, launch_lat, launch_long As Range

'Get Names on Launch Points sheet
a = Sheets("Launch Points").Range("F" & Rows.Count).End(xlUp).Row
expr = "F10" + Trim(Str(a))

Set launch_name = Sheets("Launch Points").Range(expr)

'Get Lattitudes on Launch Points sheet
b = Sheets("Launch Points").Range("B" & Rows.Count).End(xlUp).Row
expr = "B10" + Trim(Str(b))

Set launch_lat = Sheets("Launch Points").Range(expr)

'Get Longitudes on Launch Points sheet
c = Sheets("Launch Points").Range("C" & Rows.Count).End(xlUp).Row
expr = "C10" + Trim(Str(c))

Set launch_long = Sheets("Launch Points").Range(expr)

With Application
    sp = .Lookup(1, 1 / .Frequency(0, .Sin((.Radians(launch_lat - dest_lat)) / 2) ^ 2 + .Sin((.Radians(launch_long - dest_long)) / 2) ^ 2 * .Cos(.Radians(launch_lat)) * .Cos(.Radians(dest_lat))), launch_name)
End With

Range("F17") = sp
    
End Sub

The formula works outside of VBA.

I might be using completely the wrong method. What works for me is this formula. I am trying to get it into VBA to then get the information (Launchpoint name, address, city and province):

=LOOKUP(1, 1 / FREQUENCY(0, SIN((RADIANS('Launch Points'!B10:B119 - B14)) / 2) ^ 2 + SIN((RADIANS('Launch Points'!C10:C119 - C14)) / 2) ^ 2 * COS(RADIANS('Launch Points'!B10:B119)) * COS(RADIANS(B14))), 'Launch Points'!F10:F119)

Solution

Untested, since I don't have any test data, but something like this should work:

Function GetClosestLocation(dest_lat, dest_long)
    
    Dim f As String, lr As Long, ws As Worksheet
    
    f = "=LOOKUP(1, 1 / FREQUENCY(0, SIN((RADIANS(B10:B119 - <dlat>)) / 2) ^ 2 + " & _
        "SIN((RADIANS(C10:C<lr> - <dlong>)) / 2) ^ 2 * COS(RADIANS(B10:B<lr>)) * " & _
        "COS(RADIANS(<dlat>))), F10:F<lr>)"
    
    Set ws = ThisWorkbook.Worksheets("Launch Points")
    
    lr = ws.Range("F" & Rows.Count).End(xlUp).Row
    f = Replace(f, "<lr>", lr)
    f = Replace(f, "<dlat>", dest_lat)
    f = Replace(f, "<dlong>", dest_long)
    
    GetClosestLocation = ws.Evaluate(f)
    
End Function