Escape " sign in UDF parameter (1 Viewer)

[MrHans]

Hi guys,

I am creating a function to convert a position from DMS (Degree, Minute, Second) format to DD (Decimal Degree) format.
I couldn't find anything good on the web, so I'm now making my own version.

The function below is working fine, except for the fact that the DMS parameter normally contains an " sign as the Second indicator.
For example: 5° 40' 23" E

I am now removing this sign manually from the parameter, but is it possible to adjust the function so that it threats the " sign literally?

Function FormatDD(DMS As String) As String

    Dim Part() As String
    Dim strPosNeg As String
    Dim strDecimal As String

    DMS = Replace(DMS, Chr(176), "")  'Remove degree sign (°)
    DMS = Replace(DMS, Chr(39), "")   'Remove minute sign (')
    DMS = Replace(DMS, Chr(34), "")   'Remove second sign (")
    Part = Split(DMS, Chr(32))

    'Determine leading positive or negative sign
    strPosNeg = IIf(Left(Part(0), 1) = "-", "-", "+")

    'Convert DMS to DD
    strDecimal = IIf(InStr("SW", Part(3)), -1, 1) * (Val(Part(0)) + Val(Part(1)) / 60 + Val(Part(2)) / 3600)
    
    'Remove any leading or trailing spaces
    strDecimal = Trim(strDecimal)
    
    'Limit the result to a maximum of 7 decimals
    strDecimal = Round(strDecimal, 7)
    
    'Replace the comma to a period in the result
    strDecimal = Replace(strDecimal, Chr(44), Chr(46))
    
    'Add a leading zero in
    strDecimal = IIf(Len(Split(strDecimal, Chr(46))(0)) = 1, "0" & strDecimal, strDecimal)

    'Return result
    FormatDD = strPosNeg & strDecimal

End Function

So currently:
?FormatDD("5° 40' 23 E") works and returns +05.6730556
?FormatDD("5° 40' 23" E") Doesnt work, since the function see's a second parameter...

Any idea's?

 

[jdraw]

This article by Chip Pearson may offer some insight.

Since you are reading the data as a string, you could parse the value and remove characters. You can assign the numeric components to Deg, Mins, Secs or whatever named variables you prefer.
Good luck.

 

[MrHans]

Yes, I am aware of that page.
The conversion is not the problem, it's handling the 'Seconds' indicator when calling the function that's giving me problems

 

[jdraw]

Why exactly do you need the seconds character?

Here are some characters to consider.

Sub testAscii()
    Dim i As Integer
    Dim z As String
10    For i = 127 To 255
20      If i = 168 Or i = 145 Or i = 146 Or i = 147 Or i = 148 Then
30          Debug.Print i; Chr(i)
40      End If
50    Next i
60   End Sub

Also, re: your function, I'm getting double negative signs on negative values.

eg: FormatDD("-75° 40' 23 E") gives --74.3269444

 

[Frothingslosh]

When I run your function, it works as expected - it's stripping out the double quote correctly.

Here's the test function I ran:

Dim TestString As String
     TestString = "5" & Chr(176) & " 40' 23" & Chr(34) & " E"
    
    Debug.Print TestString
    
    Debug.Print FormatDD(TestString)
    
End Sub

The FormatDD was just cut and pasted.

Output is:

5° 40' 23" E
+05.6730556

As with jdraw, I'm getting double negative signs from negative inputs.

Edit:

In the original post, it looks like you are trying to submit a string as "5° 40' 23" E" directly. That won't work, but that's not the fault of the function. If you want to enter the values directly, you'll need to use either the method I used in the test function, or a double-double quote ("5° 40' 23"" E"). Access reads regular double-quotes as string starts and stops, and the double is needed to tell Access that the double quote is part of the string instead.

 

[MrHans]

Ok, thank you for the responses.
I was indeed hoping I could somehow pass the double quote directly in the paramater, but if that's not possible, I will just keep removing it from the call. It doesn't make sense to double double quote, then I can just as well remove it.

Thanks for catching the double negative indeed.
Fixed that in the code below.
For reference, I will also provide his little brother here to convert back.
Maybe it will help someone looking for a similar solution.

'---------------------------------------------------------------------------------------
' Procedure  : FormatDD
' Purpose    : Converts a position from 'Degree Minute Second' format to 'Decimal Degree'
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' DMS: 5° 40' 23" E
' Pay attention, the 'Second' indicator (") should be removed from the call.
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' Command: FormatDD("5° 40' 23 E")
' Returns: +05.6730556
'
' Revision History:
' Rev   Date(yyyy/mm/dd)    Description
' **************************************************************************************
' 1     2016-10-19          Initial Release
'---------------------------------------------------------------------------------------

Function FormatDD(DMS As String) As String

    Dim Part() As String
    Dim strPosNeg As String
    Dim strDecimal As String

    DMS = Replace(DMS, Chr(176), "")  'Remove degree sign (°)
    DMS = Replace(DMS, Chr(39), "")   'Remove minute sign (')
    DMS = Replace(DMS, Chr(34), "")   'Remove second sign (")
    Part = Split(DMS, Chr(32))

    'Determine leading positive or negative sign
    strPosNeg = IIf(Left(Part(0), 1) = "-", "", "+")

    'Convert DMS to DD
    strDecimal = IIf(InStr("SW", Part(3)), -1, 1) * (Val(Part(0)) + Val(Part(1)) / 60 + Val(Part(2)) / 3600)
    
    'Remove any leading or trailing spaces
    strDecimal = Trim(strDecimal)
    
    'Limit the result to a maximum of 7 decimals
    strDecimal = Round(strDecimal, 7)
    
    'Replace the comma to a period in the result
    strDecimal = Replace(strDecimal, Chr(44), Chr(46))
    
    'Add a leading zero in
    strDecimal = IIf(Len(Split(strDecimal, Chr(46))(0)) = 1, "0" & strDecimal, strDecimal)

    'Return result
    FormatDD = strPosNeg & strDecimal

End Function

And to convert from Decimal to DMS:

'---------------------------------------------------------------------------------------
' Procedure  : FormatDMS
' Purpose    : Converts a position from 'Decimal Degree' format to 'Degree Minute Second'
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' DD: +05.6730556
' Lat: True if Latitude is supplied, False if Longtitude is supplied
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' Command: FormatDMS(+05.6730556,True)
' Returns: 5° 40' 23" N
'
' Revision History:
' Rev   Date(yyyy/mm/dd)    Description
' **************************************************************************************
' 1     2016-10-19          Initial Release
'---------------------------------------------------------------------------------------

Function FormatDMS(ByVal DD As Double, Optional Lat As Boolean = True) As String

    Dim NSEW As String

    If Lat Then
        NSEW = IIf(DD < 0, " S", " N")
    Else
        NSEW = IIf(DD < 0, " W", " E")
    End If

    DD = Int(DD * 3600 + 0.5) / 3600 '-- rounding to second
    FormatDMS = Int(DD) & Format(DD / 24, "° nn' ss\""") & NSEW

End Function

Not all my code, I've borrowed several bits and pieces from other people and adjusted it to fit my need.

 

[static]

I was indeed hoping I could somehow pass the double quote directly in the paramater, but if that's not possible

I don't know if it's been made clear here, so..

You can pass a " to a parameter as a string but entering this

s = "5° 40' 23" E"

in the VBA editor is not valid VBA and wont compile.

The value you are trying to pass to the function is data which you would normally get from a field or control.

If you type the value into a textbox and pass it to the function it will work, e.g.

textbox2 = FormatDD(textbox1)

 

[MrHans]

Oh right, that's a good point.

I didnt actually implement it yet, but it will indeed be displayed in textbox.
So if it does work when you reference the textbox it's great.

Thx!