How to make a function change other cells in worksheet

[James4U2enjoy]

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 nothing 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()
'This shows that the code below works from within a sub initiated by a
command
' button
Application.Worksheets("Sheet1").Cells(1, 1).Value = 100
End Sub

Sub Test2()
MsgBox (Test(123))
End Sub

 

[Leith Ross]

Hello James4U2enjoy,

You need to move the function into a VBA module. Add a VBA module to
your project using the VB editor. Under Insert choose Module, the
default name is Module1. You can rename this to something more
appropriate. The function will now be seen by Excel as a worksheet
macro.

Sincerely,
Leith Ross

 

[MrShorty]

Another reason it won't work is that Function procedures aren't allowe
to alter other cells in the way you're trying to do. The statemen
"Application.Worksheets("Sheet1").Cells(1, 1).Value = 100" is no
allowed in Function procedure, though it is a perfectly valid statemen
in a Sub procedure. A Function procedure is allowed to
1) read values. These values can either be passed to the function fro
the argument list or a statement such a
"myvar=Application.Worksheets("Sheet1").Cells(1, 1).Value"
2) perform calculations, make decisions, and otherwise process th
information. It cannot alter cells in the worksheet or otherwise alte
the operating environment (like setting cell format properties).
3) Then return a value to the calling cell via the function name, a
you've done.

I don't know specifically what you are trying to do, but it looks lik
you either need to use a Sub procedure or use a different approach i
your Function procedure