New Contributor

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

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?

4 Replies

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

Hi @Gukeli

A1= degree  B1=Minutes  C1=seconds

you can use a simple formula like this:  =A1+(B1/60)+(C1/3600)

or use sum function:  =SUM(A1+(B1/60)+(C1/3600))

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

Yes, I know how to add things up on an Excel spreadsheet. But if you re-read my post, I hope you will see that I want to create a VBA user function which, when called in any cell of the spreadsheet, will automatically do such a calculation using the values in the three cells to the left of the cell in which the function is entered.
That is a rather more complicated matter.

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

I missed that, try this VBA

Sub Macro1()
ActiveCell.FormulaR1C1 = "=RC[-4]+(RC[-3]/60)+(RC[-2]/3600)"
End Sub

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

Thanks for noting that this is about VBA. But I don't want to have to call a Macro. I am trying to create a VBA user function, which as you know works rather differently.
How would your suggestion look as a user function in a VBA module with the usual Function ... End Function structure?