CODEWITHSUNDEEP
vbafunctionexcel
Crapsy
Crapsy

Reputation: 356

VBA : How to change the cell's value in a function?

I'm a new vba programmer and I have some trouble.

This is my function :

Function CopiePaste(CASEREF As Range, REF)

Dim o As Range

For Each o In CASEREF

   o.Value = REF

Next

End Function

I want define multiple cell's value in a function but my code doesn't work and I don't understand why ?

Thanks in advance

Upvotes: 1

Views: 92

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

In VBA, you would use a Sub rather than a Function

Here is one way that a Sub could use a Function to accomplish this:

Sub MAIN()
    Dim MSG As String
    MSG = CopiePaste(Range("A1:A10"), 123)
    MsgBox MSG
End Sub


Function CopiePaste(CASEREF As Range, REF) As Variant
    Dim o As Range
    For Each o In CASEREF
       o.Value = REF
    Next
    CopiePaste = "Mission Accomplished!"
End Function

Note: the custom function does not actually appear in a cell, but rather is called from the sub.

Upvotes: 1

homecore
homecore

Reputation: 11

I will make some suggestions but I am not an expert either :-) First: a function is supposed to return a value to the place in the code where the function is called from. Here, you want to do something instead of returning a value, so you should use a sub() instead of a function.

Second, I think you should also declare the "REF" variable for it to work, (unless if it's a public variable).

Upvotes: 1

Related Questions