VBA user-defined worksheet function

I wrote a VBA function that I want to use as a worksheet
function. The function supposes to convert degrees of
Fahrenheit into Celsius and, additionally, write in cell
A1 "Hello". As long as there is writing to A1 the function
gives #VALUE error. This is my code:

Function Celsius(fDegrees)
ActiveWorkbook.Sheets("Scheet1").Select
Sheets("Scheet1").Range("A1:A1").Text = "Hello"
Celsius = (fDegrees - 32) * 5 / 9
End Function

How to write it to make it work from the worksheet?

Val,

what is it you are trying to do? There is a function that can convert
already albeit
being a part of the Analysis ToolPak

=CONVERT(32,"F","C")

will convert 32 degrees Fahrenheit to Celsius..
Also note that neither built in functions nor add-ins can
populate another cell. If you explain what it is your are
doing I am sure someone could help you..

--

Regards,

Peo Sjoblom

"Val" wrote in message
...
I wrote a VBA function that I want to use as a worksheet
function. The function supposes to convert degrees of
Fahrenheit into Celsius and, additionally, write in cell
A1 "Hello". As long as there is writing to A1 the function
gives #VALUE error. This is my code:

Function Celsius(fDegrees)
ActiveWorkbook.Sheets("Scheet1").Select
Sheets("Scheet1").Range("A1:A1").Text = "Hello"
Celsius = (fDegrees - 32) * 5 / 9
End Function

How to write it to make it work from the worksheet?

Hi

Of course it's returning an error. You are changing contents of a cell in
function code - such an operation isn't allowed by definition. Even
selecting another cell isn't allowed. Any function mustn't change anything
on worksheet - not any values, or cursor position or whatever.


--
(When sending e-mail, use address )
Arvi Laanemets


"Val" wrote in message
...
I wrote a VBA function that I want to use as a worksheet
function. The function supposes to convert degrees of
Fahrenheit into Celsius and, additionally, write in cell
A1 "Hello". As long as there is writing to A1 the function
gives #VALUE error. This is my code:

Function Celsius(fDegrees)
ActiveWorkbook.Sheets("Scheet1").Select
Sheets("Scheet1").Range("A1:A1").Text = "Hello"
Celsius = (fDegrees - 32) * 5 / 9
End Function

How to write it to make it work from the worksheet?