Problem with UDF formula

tombaas

New Member
Joined
Oct 21, 2011
Messages
3
Hi guys,

I just added a UDF which i got from Microsoft Help. The UDF helps me to convert DMS into decimal degrees. The UDF looked like this:

<code></code>
Function Convert_Decimal(Degree_Deg As String) As Double ' Declare the variables to be double precision floating-point. Dim degrees As Double Dim minutes As Double Dim seconds As Double ' Set degree to value before "°" of Argument Passed. degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1)) ' Set minutes to the value between the "°" and the "'" ' of the text string for the variable Degree_Deg divided by ' 60. The Val function converts the text string to a number. minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _ InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _ "°") - 2)) / 60 ' Set seconds to the number to the right of "'" that is ' converted to a value and then divided by 3600. 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

Now they ask me to put
= Convert_Decimal("10° 27' 36""")
in a A1. I have to do this for about a 1000 numbers so it's impossible to typ de formula in every single cell. SO that is why i tried to typ
= Convert_Decimal("A1""")
into cell A2. That didn't work. Does any of you guys have any idea how i can solve this problem?

Thanks you so much in advance!
Tom
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Thanks for the fast answers. I tried to put the
= Convert_Decimal("10° 27' 36""")
without the """ and it the whole formula didn't work.

There was a note at Microsoft Help
NOTE: You are required to type three quotation marks (""") at the end of the argument of this formula to balance the quotation mark for the seconds and the quotation mark for the text string. A cell reference will not require a quotation mark.
 
Upvote 0
You said:
I have to do this for about a 1000 numbers so it's impossible to typ de formula in every single cell.
... do you have the 1000 numbers in a range in the sheet? Do you need to reference a cell containing the number when typing the UDF formula?
 
Upvote 0
do you have the 1000 numbers in a range in the sheet? Do you need to reference a cell containing the number when typing the UDF formula?

Yes, they are all placed in a sheet. I didn't make the UDF myself, i just copied and pasted it so i haven't tried to change something in the UDF yet.
 
Upvote 0
Did you try the suggestions as posted, with your data in A1 (without the additional quotes)? In other words:
A1: 10° 27' 36"
A2: = Convert_Decimal(A1)
 
Upvote 0

Forum statistics

Threads
1,203,069
Messages
6,053,352
Members
444,655
Latest member
didr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top