argument not optional

toukans

New Member
Joined
Oct 12, 2010
Messages
6
Hello, can anyone help me fix my program I keep getting an argument not optional compile message when i call my function.

Code:
Sub station()
Dim dn As Double
Dim Holder As Double
Dim numb As Integer
Dim namehold As String

Dim shifter As Integer
Dim counter As Integer
Dim subber As Integer
Dim shifetr2 As Integer

 Dim Latitude1 As Double, Longitude1 As Double, _
            Latitude2 As Double, Longitude2 As Double, _
            ValuesAsDecimalDegrees As Boolean, _
            ResultAsMiles As Boolean
ValuesAsDecimalDegrees = True
ResultAsMiles = False
' station (a,B)
' weatehr ( Xn, Yn)

counter = Range("ao1").Value

For shifter = 1 To counter
Holder = 100

Latitude1 = Range("AL" & shifter).Value
Longitude1 = Range("AN" & shifter).Value


    For shifter2 = 52 To 74
    Longitude2 = Range("d" & shifter2).Value
    Latitude2 = Range("i" & shifter2).Value
   
   dn = GreatCircleDistance * 1
  
   
    If dn < Holder Then
    Holder = dn
    Range("ap" & shifter).Value = Range("E" & shifter2).Value
    End If
    Next



Next



End Sub
Private Const C_RADIUS_EARTH_KM As Double = 6371.1
Private Const C_RADIUS_EARTH_MI As Double = 3958.82
Private Const C_PI As Double = 3.14159265358979

Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
            Latitude2 As Double, Longitude2 As Double, _
            ValuesAsDecimalDegrees As Boolean, _
            ResultAsMiles As Boolean) As Double

Dim Lat1 As Double
Dim Lat2 As Double
Dim Long1 As Double
Dim Long2 As Double
Dim X As Long
Dim Delta As Double

If ValuesAsDecimalDegrees = True Then
    X = 1
Else
    X = 24
End If

' convert to decimal degrees
Lat1 = Latitude1 * X
Long1 = Longitude1 * X
Lat2 = Latitude2 * X
Long2 = Longitude2 * X

' convert to radians: radians = (degrees/180) * PI
Lat1 = (Lat1 / 180) * C_PI
Lat2 = (Lat2 / 180) * C_PI
Long1 = (Long1 / 180) * C_PI
Long2 = (Long2 / 180) * C_PI

' get the central spherical angle
Delta = ((2 * ArcSin(Sqr((Sin((Lat1 - Lat2) / 2) ^ 2) + _
    Cos(Lat1) * Cos(Lat2) * (Sin((Long1 - Long2) / 2) ^ 2)))))
    
If ResultAsMiles = True Then
    GreatCircleDistance = Delta * C_RADIUS_EARTH_MI
Else
    GreatCircleDistance = Delta * C_RADIUS_EARTH_KM
End If

End Function

Function ArcSin(X As Double) As Double
    ' VBA doesn't have an ArcSin function. Improvise.
    ArcSin = Atn(X / Sqr(-X * X + 1))
End Function
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
You declare your function as:

Code:
Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
            Latitude2 As Double, Longitude2 As Double, _
            ValuesAsDecimalDegrees As Boolean, _
            ResultAsMiles As Boolean) As Double

which indicates it will be looking for 6 parameters each time it is used [called].

In your code, you use [call] your function, but you don't pass it any parameters:

Code:
  dn = GreatCircleDistance * 1
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,789
Code:
Dim shifter As Integer
Dim counter As Integer
Dim subber As Integer
Dim [COLOR=red][B]shifetr2[/B][/COLOR] As Integer
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,824
Office Version
  1. 2010
Platform
  1. Windows
Here's a variation on that. Multiply the result by the earth radius in whatever units you want (e.g., 3959 for statute miles, 6371 for km):
Code:
Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
 
    ' shg 2008-1111
    ' Returns central angle between two point in RADIANS
    ' using Vincenty formula
 
    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#
    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double
 
    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lat2 = D2R * lat2
    dLon = D2R * (lon2 - lon1) ' delta lon
 
    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function
 
Last edited:

Forum statistics

Threads
1,147,960
Messages
5,744,046
Members
423,843
Latest member
alex2022

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
Top