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

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

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:
```

degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))

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

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

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

http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

Link to how to attach workbooks

http://www.excelguru.ca/forums/faq.p...b3_attachments

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.

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

7. Originally Posted by pike
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?
Dear pike,

The sample data that I enter is as follow:

 5˚20’38.83”N 100˚76’38.04”E

8. some strange charactors in the string .. can you attach an example workbook

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

10. Originally Posted by pike
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```

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.

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•