Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling of simple function in Excel 97
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling of simple function in Excel 97
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. -- Vasant "TomCee" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling of simple function in Excel 97
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" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling of simple function in Excel 97
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 "TroyW" wrote in message ... 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" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling a new function Excel gives me #NAME? | Excel Worksheet Functions | |||
Calling functions from Excel worksheets and the Find function in XL '97 | Excel Programming | |||
Calling Word function from within Excel | Excel Programming | |||
Calling Excel FUNCTION MACROS Programmatically from VB | Excel Programming | |||
Calling a function from within EXCEL | Excel Programming |