Can we change worksheet in function?

Question

I am trying to write a vba function that takes a range as an argument and returns some coefficients to the worksheet. But when I try to write anything to my worksheet from the function, I get an undefined value (#VALUE!). When I change function to macro(sub) and hardcode the arguments in the function, it allows me to set values in worksheet.

' these don't work
Sheets("Sheet1").Cells(4, 1) = x
Sheets("Sheet1").Range(4, 1) = x

Can you please give me a simple example of a function that takes a range of values as arguments, calculates something, and writes answers back to worksheet.

Answer 1

this function can be used like many of the inbuilt functions that are used within individual cells

Public Function TestMe(rangeX As Range) As String
    TestMe = "This is a test - " & rangeX.Value
End Function

but it will error if you try to affect other cells

This next method will need to be run as a macro either from a button or from running the macro manually. This method assumes that it will be passed two single cell ranges.

Public Sub RunTest()
    'A1 and A2'
    Test2 Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(2, 1)
End Sub

Public Sub Test2(rangeX as Range, rangeY as Range)
    'write values out to A4 and B4'
    Sheets("Sheet1").Cells(4, 1) = rangeX
    Sheets("Sheet1").Cells(4, 2) = rangeY
End Function

Answer 2

If what you want is a user defined function that returns more than one value, try using an array function.
An array function is entered by selecting a range of cells in a sheet, typing the function and pressing Ctrl-Shift-Enter. The result is the function you typed is in each of the cells you selected, wrapped in {}

Your user defined function then needs to return an array of values For example

Function MyArrayFunction(r As Range) As Variant
Dim cl As Range
Dim i As Long, j As Long
Dim v() As Variant

ReDim v(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
For i = 1 To UBound(v, 1)
    For j = 1 To UBound(v, 2)
        v(i, j) = i * j + r.Value
    Next
Next

MyArrayFunction = v

End Function

To use, select a range, say A1:B4, type =MyArrayFunction(D1), press Ctrl-Shift-Enter if D1 contains 0 result will be A1=1, B1=2, A2=2, B2=4, A3=3, B3=6, A4=4, B4=8