I'm trying to make a VBA function that accepts a cell as argument and works from there using a variety of Range.Offset. This function will be called in the worksheet cells. For testing I'm using this simple script:
Public Function testPublic(targetCell As Range) As Boolean
targetCell.Offset(0, 3).Value2 = "Test is successful!"
testPublic = True
End Function
To see if I can get the cell reference to work, I pass simple references such as C5, but I only get #VALUE! error. Not sure what's wrong with this.
Tried to change Range as Variant, still doesn't work
You cannot use a function called as a UDF from a worksheet to update another cell in the worksheet: the function can only return a value to the cell containing the function (an array formula can return multiple values, but again only to the cells where the formula was entered).
For more information see: https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1