VBS macro to convert DMS to decimal time

[wasyoungonce]

Hi folks, I'm a novice with VBS but am trying.I'm trying to write an excel macro that converts Degrees Minutes and Seconds (DMS) into Decimal Degrees (DD) for Astronomy spreadsheets. I have written other macros for DD to DMS but this one is causing me grief.So far I have been playing with the MS knowledge base article, KB213449 (there is not alot out there for this doing this):MS article KB213449But this does not convert negative DMS degrees as in negative Declination angles.I have fiddled with this MS script and got it to convert negative DMS angles to negative DD angles....but...it returns a positive angle for DMS angles less than 1 degree:ie: -00° 30' 00" is returned as .5 degrees. It should be -.5 degrees. Can anyone pls lend some help as I'm been playing with the script and cannot get it to show the correct negative sign for angles less than one degree negative, as described above. Here is my script:Function DMStoDecimal(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)) If degrees < 0 Then sign = -1 degrees = -degrees minutes = -minutes Else If Left(s, 1) = "-" Then sign = -1 Else sign = 1 End If End If ' 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 DMStoDecimal = sign * (degrees + minutes + seconds)End FunctionAnyone want to take a shot at it? Thanks in advance.Brendan

[wasyoungonce]

Function DMStoDecimal(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)) If degrees < 0 Then sign = -1 degrees = -degrees minutes = -minutes Else If Left(s, 1) = "-" Then sign = -1 Else sign = 1 End If End If ' 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 DMStoDecimal = sign * (degrees + minutes + seconds)End Function

Ahhh cured my own problem Have I. Macro function should be:Function DMStoDecimal(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)) If degrees < 0 Then sign = -1 degrees = -degrees: minutes = -minutes Else If Left(Degree_Deg, 1) = "-" Then sign = -1 Else sign = 1 End If End If ' 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 DMStoDecimal = sign * (degrees + minutes + seconds)End FunctionChange made in bold see line here If Left(Degree_Deg, 1) = "-" ThenAll working now, now shows -00° 30' 00" as -.5 degrees.This place give me inspiration.Brendan