converting coordinates from degree to decimal 1

[JoeAtRevolutionUnltd]

I am trying to convert coordinates in excel from degree to decimal. I have this code to do it:
Function Convert_Decimal(Degree_Deg As String) As Double

' Declare the variables to be double precision floating-point.
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double

' Set degree to value before "~" of Argument Passed.
degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, ",") - 1))

' Set minutes to the value between the "~" and the "'"
' of the text string for the variable Degree_Deg divided by
' 60. The Val function converts the text string to a number.
minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, ",") + 2, _
InStr(1, Degree_Deg, ",") - InStr(1, Degree_Deg, _
"~") - 2)) / 60

' Set seconds to the number to the right of "'" that is
' converted to a value and then divided by 3600.
seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, ",") + _
2, Len(Degree_Deg) - InStr(1, Degree_Deg, ",") - 2)) _
/ 3600

Convert_Decimal = degrees + minutes + seconds

End Function

The problem I am having is this code takes the values as 40~12'25 and my coordinates are stored in an acces database as 40,12,25. I tried to change the code but am unsure if I am getting the correct values. Any ideas?

Thanks

 

[gmmastros]

You don't need Excel for this. You could take the original value (from your Access database) and perform the math yourself.

Public Sub ConvertDegrees(byval Degrees As String) As Single

  Dim arDegrees() as String

  arDegrees = Split(Degrees, ",")

  ConvertDegrees = arDegrees(0) + arDegrees(1) / 60 + arDegrees(2) / 3600

End Sub

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JoeAtRevolutionUnltd]

Thanks for the help. But I do not know too much about access. How waould I actually apply this?

thanks

 

[gmmastros]

The code I posted has nothing to do with Access. In your original post, you mention that you have the data stored in an access database with the format '40,12,25'

The function I posted (should have been a function, anyway) takes a string formatted like '40,12,25' and returns a number converted to decimal. So, you should be able to take your string representation of degrees and convert it to decimal without having to do anything after retrieving it from the database.

By the way... if these degrees represent latitude/longitude coordinates, you can probably use a single to represent the number. The 5th digit of precision is somewhere around 3 feet (depending on your latitude). A single will represent this very well.

For more specifics on using the code, please post some of your own. Specifically, where you have the degrees and want to convert it to decimal.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JoeAtRevolutionUnltd]

Thanks George. Heres my whole situation.

I need to send out a spreadsheet with some information on it including the latitude and longitude. The problem, like I said befor is they are in DMS and not decimal. I get the information from the access database with a very simple query:

select id, state, location, latitude, longitude, g, agl, amsl, tb
from [site roster]
where state='nj' OR state='ny';

Thats all I have. I am now stuck at converting the latitude and longitude. Theres about 900 of them. If you need anything else just let me know.

Thanks

 

[gmmastros]

So, your data is in access. I assume you are copy/pasting the data in to excel, or perhaps some other method of getting the data in to Excel.

Once it is in excel, you have the vba function that you posted originally.

If this is true, then I can at least explain my confusion. You see, this is a VB forum. There is another forum for VBA (the language used within Excel). Many of the functions and features are the same. Like in this case. If you are using Excel 2000 (or greater), then you should be able to replace your original function with this one.

Function Convert_Decimal(Degree_Deg As String) As Double

  Dim arDegrees() as String

  arDegrees = Split(Degrees, ",")

  Convert_Decimal = arDegrees(0) + arDegrees(1) / 60 + arDegrees(2) / 3600

End Function

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JoeAtRevolutionUnltd]

Thanks for all the help George. The first time I ran your code it did not work. But I changed one line to
arDegrees = Split(Degrees_Deg, ",") and it worked fine. Again thanks for the help.

 

[gmmastros]

Glad to help, and sorry about the variable naming problem.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom