VBA function with 3 arguments in 3 cells to the left of entered cell

[Gukeli]

I want to make a VBA function that will return a value in decimal degrees, using the degree, minutes and seconds values in the three cells to the left of any cell in which I choose enter the function. Essentially. it will perform the calculation

Decimal degrees = degrees + minutes/60 + seconds/ 3600

As for example here:

D	M	S	Decimal degrees
3	33	56	3.56555556

I have made a number of relevant searches, but the answers seem considerably more complicated than I would have expected. Surely there must be a simple VBA method to retrieve the values one, two, and three spaces to the left of the cell in which the function is entered?

[Paul_Hossler]

Capture.JPG

Option Explicit

Public Function DecDegrees() As Variant
    Dim D As Double, M As Double, S As Double
    Dim R As Range
    
    DecDegrees = CVErr(xlErrNum)

    Application.Volatile    '   look in Help

    On Error GoTo NiceExit
    
    Set R = Application.Caller  '   cell with this function in
    D = R.Offset(0, -3).Value   '   3 to the left, etc.
    M = R.Offset(0, -2).Value
    S = R.Offset(0, -1).Value


    'Decimal degrees = degrees + minutes/60 + seconds/ 3600
    DecDegrees = D + M / 60# + S / 3600#    '   # = double i.e. 3600.0 --> 3600#
    
    Exit Function
    
NiceExit:
End Function

[Gukeli]

Thanks!

I pasted that in my VBA module, and it worked beautifully.

However, if I included the 'Option Explicit' line, the debug complained. I deleted it, and everything worked smoothly.

Thanks again!

[Gukeli]

Whoops! I needed to put 'Option Explicit' at the top of the module, above the Dim statements, reveal of which needed to be made, um .... explicit for things to work!

Thanks again.

[Gukeli]

"reveal-" -> "several"

[Paul_Hossler]

I like to use Option Explicit (I need all the help I can get)

1. I wasn't going to lecture my opinion, but the version below works without Option Explicit, but all the variables (all un-Dim-ed) default to Variant type, and I prefer to have them strongly typed so that if I accidently try to assign a String to a valiable that should only be Long, it'll tell me

The other reason is that I might make a typo with a variable that takes forever to track down

sMsg = "This is a bad thing to do"

...
...
...
...
...

If x = 0 then
     Msgbox sMgs

...

..
..

Public Function DecDegrees() As Variant
    DecDegrees = CVErr(xlErrNum)

    Application.Volatile    '   look in Help

    On Error GoTo NiceExit
    
    Set R = Application.Caller  '   cell with this function in
    D = R.Offset(0, -3).Value   '   3 to the left, etc.
    M = R.Offset(0, -2).Value
    S = R.Offset(0, -1).Value


    'Decimal degrees = degrees + minutes/60 + seconds/ 3600
    DecDegrees = D + M / 60# + S / 3600#    '   # = double i.e. 3600.0 --> 3600#
    
    Exit Function
    
NiceExit: End Function

2. You should be able to edit a post to correct a typo (I have to do it all the time since my keyboard insists on making dumb mistakes)

There should be an [Edit Post] buttom below right

[Gukeli]

Thanks again Paul - as a sometimes intensive but amateur VBA user (I rely on VBA for Dummies), I really do need advice like yours for those moments where nobody seems to have addressed my issue on the web!