Function as variant in for loop return "Object required" Error

Question

I try to make cell A1, A2, A3 = aa
using code below but getting error "Object required".

Public Function cellv(ByVal row As Variant, col As Integer) As Variant
    cellv = ThisWorkbook.Sheets("Tree").Cells(row, col).Value
End Function

Public Sub testz()
    Dim j
    For j = 1 To 3
        cellv(j, 1) = "aa"
    Next j
End Sub

If I use Debug.Print cellv(j,1), it work without error.

Answer 1

1) A function is designed to return a function result; furthermore a function "can take arguments, perform a series of statements, and even change the values of its arguments" - c.f.Function statement.

By using Debug.Print cellv(j, 1) your function (as it is coded) returns a cell value (predefined by passed row/col arguments) as function result, whereas coding Debug.Print cellv(j, 1) = "aa" would check if the function result equals "aa" and write a True or False value.

2) A function result consisting of a cell value is not designed to receive itself an outer value assignment like e.g. via cellv(j, 1) = "aa". In your case you get a run time error 424 "Object needed."

• If, however you want to assign values to a range you might prefer a Sub procedure or if you want to stick to your function, set it to memory as Range like in @MGonet 's post.