Error in running a function to convert coordinates in degrees to decimal for EXCEL VB

F

faznali

New member
Joined
Dec 29, 2014
Messages
5
Reaction score
0
Points
0
Dear all,

Currently I am working on VBA excel to create a widget to verify coordinates whether it lies under the radius of ANOTHER predefined and pre-specified sets of coordinates.

In the module, I want to convert the coordinates from degrees to decimal before doing the calculation - as the formula of the calculation only allow the decimal form of coordinates.

However, each and every time I want to run the macros this error (Run-time error '5', invalid procedure call or argument) will appear. Then, the debug button will bring me to below line of coding:

Code: 
[COLOR=#333333]

 degrees = Val(Left(Degree_Deg, InStr([COLOR=#800000]1[/COLOR], Degree_Deg, [COLOR=#800000]"°"[/COLOR]) - [COLOR=#800000]1[/COLOR]))

[/COLOR]

For your information, the full function is as below:

Code: 
Function Convert_Decimal(Degree_Deg As String) As Double


   Dim degrees As Double
   Dim minutes As Double
   Dim seconds As Double
   
   'modification by JLatham
   
   Degree_Deg = Replace(Degree_Deg, "~", "°")


   
   degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
   
   minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
             InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, "°") - 2)) / 60
  
   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

However, for your info, it's not only code that I use in the widget.

Besides, I'm not really sure whether the error is coming from the coding or from the data in the worksheet.

I hope I can share the workbook here so you can have overall idea on how the widget looks like.

Thank you.

Your kind assistance and attention in this matter are highly appreciated.

Regards,
Nina.
 
Hi faznali,
what is the value you have an error with?
I have corrected the calx for the corrent conversion

Code: 
Function Convert_Decimal(Degree_Deg As String) As Double
   Dim degrees As Double
   Dim minutes As Double
   Dim seconds As Double
   
   'modification by JLatham
   
   Degree_Deg = Replace(Degree_Deg, "~", "°")
   degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
   
   minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 1, _
             InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, "°"))) / 60
   
   seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
              1, InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, " &  Chr(34)& ") - 1)) / 3600
   Convert_Decimal = degrees + minutes + seconds
End Function

there are probably betters ways to convert and inter the data..
you can consider using conditional formating like in the example or use worksheet functions
 

Attachments

  • DMS to Decimal to DMS.xlsx
    24.7 KB · Views: 16
Dear pike,

I think the problem is actually lies in the workbook as a whole not on the function only.

The workbook is use to calculate whether the distance of the inserted data (in coordinates) is within the radius of specified coordinates.

Can you share with me on how to attach the file here.

Thank you.
 
Link to cross-post

Dear pike and everyone,

Thank you pike reminding me and sorry for any inconvenience that happened.

However, I am still unable to post the cross-post here as I only have 2 posts instead of 5 posts that can enable me to post links or images.

For your information, you can find the cross post at mrexcel forum with the same title post - and for now I can said that there is still no solution for the error.

Regards,

Nina.
 
No problem..
what are you entering in the cell that is causing the User Defined Function (UDF) to error on the "degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))"
line?
 
pike said:
No problem..
what are you entering in the cell that is causing the User Defined Function (UDF) to error on the "degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))"
line?
Click to expand...

Dear pike,

The sample data that I enter is as follow:

5˚20’38.83”N 100˚76’38.04”E
 
some strange charactors in the string .. can you attach an example workbook
 
Hi faznali,
How can you have 76 minutes?? in 100˚76’38.04”E

I asume that the data is not in one cell so another approach
Code: 
Function Convert_Decimal(Degree_Deg) As Double
    Dim myMatches As MatchCollection
    Dim myMatch
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "(\d+)"
        Set myMatches = .Execute(Degree_Deg)
        Convert_Decimal = myMatches(0) + myMatches(1) / 60 + myMatches(2) / 3600 + myMatches(3) / 360000
    End With
End Function
 
pike said:
Hi faznali,
How can you have 76 minutes?? in 100˚76’38.04”E

I asume that the data is not in one cell so another approach
Code: 
Function Convert_Decimal(Degree_Deg) As Double
    Dim myMatches As MatchCollection
    Dim myMatch
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "(\d+)"
        Set myMatches = .Execute(Degree_Deg)
        Convert_Decimal = myMatches(0) + myMatches(1) / 60 + myMatches(2) / 3600 + myMatches(3) / 360000
    End With
End Function
Click to expand...


Hi pike,

actually I'm not giving the actual value of the data.

It is just a dummy data - but the real data would has the same format as the one that I gave you.

And I have attach the workbook for your further reference.

Thank you.
 

Attachments

  • widget.xlsm
    128.3 KB · Views: 12
Hey faznali,
this covers the two options when there is a decimal second DMS.S or just DMS
Code: 
Function Convert_Decimal(Degree_Deg As String) As Double
    Dim myMatch
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "(\d+)"
        Set myMatch = .Execute(Degree_Deg)
    If myMatch.Count > 4 Then
        Convert_Decimal = myMatch(0) + myMatch(1) / 60 + myMatch(2) / 3600 + myMatch(3) / 360000
    Else
        Convert_Decimal = myMatch(0) + myMatch(1) / 60 + myMatch(2) / 3600
    End If
    End With
End Function
 
The data in the table are not consistent; you can clean them with:

Code: 
Sub M_snb()
  With Sheets("nasmoc table").Columns(3).resize(,2)
    .Replace " ", ""
    .Replace Chr(176), "°"
    .Replace Chr(146), "'"
    .Replace Chr(148), Chr(34)
  End With
End Sub

After cleaning the data you can use :

Code: 
Function F_snb(c00)
   F_snb = Val(c00) + Val(Split(c00, "°")(1)) / 60 + Val(Split(c00, "'")(1)) / 60 ^ 2
End Function
 
You must log in or register to reply here.
Back
Top