Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #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
    Attached Files Attached Files

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

  4. #4
    sure but first can you add your cross post links

    Message Link about cross posting

    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

  5. #5

    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.

  6. #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. #7
    Quote Originally Posted by pike View Post
    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˚2038.83N 100˚7638.04E

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

  9. #9
    Hi faznali,
    How can you have 76 minutes?? in 100˚7638.04E

    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. #10
    Quote Originally Posted by pike View Post
    Hi faznali,
    How can you have 76 minutes?? in 100˚7638.04E

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

Page 1 of 2 1 2 LastLast

Posting Permissions

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