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 .......

You don't have to call the other functions. The main one
Convert_Decimal calls the other ones -- GetNum, LopText, and LopNum.
The names are descriptive. Take your example "12deg03min00sec". GetNum
grabs the "12". LopNum lops it off, leaving "deg03min00sec". LopText
lops "deg", leaving "03min00sec". GetNum grabs the "03". And so forth.
Identifying a character as a number or not utilizes the function
Asc( ). Asc(48)-Asc(57) are the digits 0-9.

Merjet