I am relatively new to VBA and want to do the following:
A function should be called from directly from the Excel sheet, do some calculations, and write the calculated values into one or more specified cells (not necessarily being identical to the cell, from which the function has been called).
Also, preferably, the function itself should have no return value (i. e., it should act like a sub, but it seems that a sub cannot be called directly from a sheet).
I wonder if this is possible in VBA / Excel?
At least the function below does not work but leads to an error message in the cell it has been called from.
Public Function abc()
Dim x As Double
x = 1.234567
Cells(1, 1).Value = x
End Function
Interestingly, when calling the above function from the direct window, the cell is filled correctly. Thus, Excel seems to refuse the function only when called from the sheet.
I also tried to more precisely specify the cell playing around with things like:
Public Function abc()
Dim x As Double
Dim ws As New Worksheet
x = 1.23456
Set ws = Application.Worksheets("Table1")
ws.Cells(1, 1).Value = x
End Function
However, the problem persists.
