hello everyone

how to overcome this limitaion in excel vba

WARNING: This custom function fails if the Degree_Deg argument is not
in the following format
<degrees° <minutes' <seconds"
even if the seconds value is 0.

http://support.microsoft.com/kb/213449

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

i hope sum of you find time to help me out
all sugestions are welcomed

many thanx in advance

If you want help, I think you need to specify what format(s) you want
to handle.

Merjet

well i want the function which can convert any format we get from GPS
Like 12deg03min00sec
aur any
excel vba procedure which can convert all formats or maximum formats
"in a single cell"

Try this. Maybe there is a slicker way, but this worked for me.

Function Convert_Decimal(Degree_Deg As String) As Double
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double

degrees = GetNum(Degree_Deg)
Degree_Deg = LopNum(Degree_Deg)
Degree_Deg = LopText(Degree_Deg)
minutes = GetNum(Degree_Deg) / 60
Degree_Deg = LopNum(Degree_Deg)
Degree_Deg = LopText(Degree_Deg)
seconds = GetNum(Degree_Deg) / 3600
Convert_Decimal = degrees + minutes + seconds
End Function

Private Function LopText(pStr As String) As String
Do
pStr = Right(pStr, Len(pStr) - 1)
Loop Until Asc(Left(pStr, 1)) 47 And Asc(Left(pStr, 1)) < 58
LopText = pStr
End Function

Private Function GetNum(pStr As String) As Double
Do
iCt = iCt + 1
Loop Until Asc(Mid(pStr, iCt, 1)) < 47 Or Asc(Mid(pStr, iCt, 1))
58
GetNum = Left(pStr, iCt - 1)
End Function

Private Function LopNum(pStr As String) As String
Do
pStr = Right(pStr, Len(pStr) - 1)
Loop Until Asc(Left(pStr, 1)) < 48 Or Asc(Left(pStr, 1)) 57
LopNum = pStr
End Function

Hth,
Merjet

In the function GetNum the conditions should be "< 48" and "
57" (like in LopNum).

Merjet

thanx mr. merjet

really glad to see ur cooperation n suggestions

as i m novice ti excel vba so m not able to understand ur code wel. if
u find time wil plz xplain this code n how do i call other function
along with main function [convert_decimal..i hop u wil find time to
elabotrate on it


many thanx in advance .......