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

[fazna ali]

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 style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"> 
degrees = Val(Left(Degree_Deg, InStr([COLOR=#800000]1[/COLOR], Degree_Deg, [COLOR=#800000]"°"[/COLOR]) - [COLOR=#800000]1[/COLOR]))
</code>

For your information, the full function is as below:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Function Convert_Decimal(Degree_Deg As String) As Double
'source: http://support.microsoft.com/kb/213449
   
   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
</code>

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.

 

[Jon von der Heyden]

Cross-posted here: Error in running a function to convert coordinates in degrees to decimal for EXCEL VB

For reference, we do not forbid cross-posting at our forum. However we do require that you post references to cross-posts so that members here can keep tabs on other solutions offered. Please remember this in future.

 

[fazna ali]

Cross-posted here: Error in running a function to convert coordinates in degrees to decimal for EXCEL VB

For reference, we do not forbid cross-posting at our forum. However we do require that you post references to cross-posts so that members here can keep tabs on other solutions offered. Please remember this in future.

It is noted Jon von der Heyden. I'm sorry because I did not read about the cross post earlier. Thank you for helping me post the link here.