ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Setting up and Configuration of Excel (https://www.excelbanter.com/setting-up-configuration-excel/)
-   -   Changing worksheet cells from within a function (https://www.excelbanter.com/setting-up-configuration-excel/50394-changing-worksheet-cells-within-function.html)

James4U2enjoy October 14th 05 05:22 AM

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


Dave Peterson October 14th 05 02:16 PM

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 05:13 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com