Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Changing worksheet cells from within a function
I have placed "=Test(123)" in cell B2. The function is supposed to return
the value 200 in cell B2 and put the value of 100 in cell A1, but instead returns the "#VALUE!" error and does noting to cell A1. However, when the same function is called from within a sub (Test2), it works properly. How can I get the function to work properly when called from a worksheet? The function and subs appear below: Function Test(X As Double) Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 Test = 200 End Function Sub Test1() Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 End Sub Sub Test2() MsgBox (Test(123)) End Sub |
#2
|
|||
|
|||
A UDF called from a worksheet can't change a different cell. It can return a
value to the cell with the formula--and that's about all. A function called not originated from a UDF in a cell can modify other cells. James4U2enjoy wrote: I have placed "=Test(123)" in cell B2. The function is supposed to return the value 200 in cell B2 and put the value of 100 in cell A1, but instead returns the "#VALUE!" error and does noting to cell A1. However, when the same function is called from within a sub (Test2), it works properly. How can I get the function to work properly when called from a worksheet? The function and subs appear below: Function Test(X As Double) Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 Test = 200 End Function Sub Test1() Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 End Sub Sub Test2() MsgBox (Test(123)) End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset function referencing worksheet | Excel Discussion (Misc queries) | |||
excel contents cells on different worksheet like a comment | Excel Discussion (Misc queries) | |||
Worksheet VBA function - not recognized | Excel Worksheet Functions | |||
Excel 2003 worksheet name function? | Excel Worksheet Functions | |||
Date & Time | New Users to Excel |