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.
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."
Sub procedure or if you want to stick to your function, set it to memory as Range like in @MGonet 's post.If you absolutely must use the function for this, then here's how:
Public Function cellv(ByVal row As Variant, col As Integer) As Range
Set cellv = ThisWorkbook.Sheets("Tree").Cells(row, col)
End Function
A more natural way is:
Public Sub testz2()
Dim cellv As Range
Set cellv = ThisWorkbook.Sheets("Tree").Range("A1:A3")
cellv.Value = "aa"
End Sub
