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