CODEWITHSUNDEEP
excelvba
BreezyChick89
BreezyChick89

Reputation: 1479

How do I pass a Range object for use in another function?

I have a button someone can click. This button will create a range and pass it to another function that changes the value of that range.

Sub CommandButton21_Click()
   Dim example As Range
   Set example = Range("A1")
   test (example)
End Sub

This function does not work. For some reason the range cannot be used by the other function.

Function test(x As Range)
    x.Value = "changed"
End Function

Any help? The error says "Object required". I have tried to pass stuff like [A1] or making it a variant with no luck.

Upvotes: 0

Views: 70

Answers (2)

SkyMaster
SkyMaster

Reputation: 1323

Use Call:

Call test(example)

If you will not return any value, use Sub rather than Function.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You can't "Call" a function, you Call a sub.....try this:

Sub CommandButton21_Click()
   Dim example As Range
   Set example = Range("A1")
   MsgBox test(example)
End Sub

Function test(r As Range) As String
    r.Value = "Changed"
    test = "O.K."
End Function

Upvotes: 1

Related Questions