Excel VBA: UDF Not Working; Please Explain?

I'm trying to learn how to perform User Defined Functions in Excel:wave:, and I thought this code (below) would be a winner on first pass; but it was not. :(

Code:

---

Public Function change2F() As Variant
Dim result As Variant
Dim sht As Worksheet
    change2F = (sht.Range("a:a") - 32) * 5 / 9
    change2F = result
       
End Function

---

Basically, I have in column A, a set of numbers from 0 to 100 (by 5's; representing Fahrenheit values) and I'm trying to get the Celsius equivalent to those numbers.
Attachment 120879

I have tried to toy with the code and keep getting "#VALUE!" errors:confused:

Can someone explain why this code isn't working? I've tried several different data types and combinations to no avail: Integer, Variance, Long ... nothing seems to be working.

What's going wrong with this code?:afrog:

you finish by assigning an empty variant to your function
your sht variable is never set to a worksheet object
neither of which are required

try like

Code:

---

Public Function change2F(r as range) As Variant

    change2F = (r - 32) * 9/5

       
End Function

---

though from your description the function should be change2C

for this application, you do not really require a UDF
=(a1 - 32) *9/5
directly in the cell would work

WestConn1,

You are correct, I needed the change the desciption to "Change2C". I did try your code, but I received answers which were different from the In Cell references.

Here's a screenshot:
Attachment 120883

Also, just so I'm learning correctly:

Quote:

---

your sht variable is never set to a worksheet object

---

If I changed the line in my original code from:

Code:

---

Dim sht As Worksheet ...
'changed to

Dim sht as ActiveWorksheet ...

---

would that be setting the variable to a worksheet object?

Code:

---

change2C = (r - 32) * 9/5

---

should be

Code:

---

change2C = (r - 32) * 5/9

---

Quote:

---

would that be setting the variable to a worksheet object?

---

that is the declaration (or dimension of the variable), not assigning an object to the variable, which you were not doing

Code:

---

set sht = activesheet

---