Calling of simple function in Excel 97

[TomCee]

Hello:

I am interested in doing some simple programming in Excel 97. The
Help facility in Excel has plenty of examples of writing functions,
but does little to describe how to call a user-written function from a
spreadsheet.

For example:
I have defined a function such as:

Function CSR(NumberArg As Double) As Double
If NumberArg < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
CSR = Sqr(NumberArg) ' Return square root.
End If
End Function

How do I call this from the worksheet? How do I refer to the Active
Cell?
(I've tried to call it by entering =CSRR() in a worksheet, but get a
#NAME error. I am not certain how to use the ActiveCell property to
refer to the passed value properly. I've tried it a number of ways
here to no avail.)

Where do I define this function in the the Visual Basic Editor?
(I've entered it in the General Declarations part of the workbook, and
it seems to 'compile' fine, but does not work!)

Thanks for your help,
Tom

 

[Vasant Nanavati]

The function should be placed in a standard module in the workbook that you
want to use it in. If it is in another (open) workbook, you will have to
prefix the function name with the workbook name(e.g.,
=OtherBook.xls!CSR(NumberArg)).

You can't use the active cell as the argument because worksheet functions
cannot change cells; in addition, you would have a circular reference.

 

[TroyW]

Tom,

You're really close. In your example the function is defined as:
Function CSR(NumberArg As Double) As Double


The "NumberArg As Double" means that the function is expecting a value (of
type: Double) to be passed into the function.

In the worksheet use:
=CSR(5) evaluate the value 5
or
=CSR(B3) evaluate the value in cell B3

Hope that helps.

Troy

P.S. The function is named "CSR" and in your message you indicate that you
are trying to use "CSRR" (you have one too many R's), that is likely why the
#NAME error is occurring. Secondly, using the "Exit Function" in the first
conditional clause may not give you a desired result. The function will
return 0 (zero) in the cell when it evaluates a negative number. The return
value of the function defaults to zero if you don't assign it some other
value, which happens when you simply exit the function. I would probably
return an error value.

If you use the "Insert | Function" command from the worksheet menubar you
should see a listing of "User Defined" functions, with CSR listed.

 

[TomCee]

Thanks all!! The problem I was having was that the fcn was not
defined in a 'module'. (none of the documentation I've looked at
makes that clear!!!)
I've done that and all is fine!!! Thanks again!!!

Tom