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 |
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 |
All times are GMT +1. The time now is 07:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com