Copy a Range using a function and report errors

Question

Here is my code

Function copyToNewRange(a As Range, b As Range)
' a is input, b is output
    If a.Cells.Count <> b.Cells.Count Then
        copyToNewRange = "ERROR"
        Exit Function
    End If
    For i = 1 To a.Cells.Count
        b.Cells(i, 1) = a.Cells(i, 1)
    Next i
    copyToNewRange = "COPIED"
End Function

I would use it thus:

=copyToNewRange(A11:A30,C11:C30)

in a cell not in the input or output range!
Why do I get #VALUE!? I note that commenting out b.Cells(i, 1) = a.Cells(i, 1) allows it to run, but what is the error in this line?

I don't think this is possible as is. A Function routine is used to return values and not execute object properties and method. But somehow, [this post](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) shows a way to alter properties of cells. I'm not sure if it can be used the way you want though but I think it is a start. — L42, Jun 20 '14 at 08:25

score 2 · Answer 1 · edited May 23 '17 at 12:27

A UDF (a user defined function, called from a worksheet) cannot directly modify other cells, it can only return a value. There are, however, workarounds.

One such workaround is to construct a call to a Sub as a string, and use Evaluate to execute it.

Something like this:

Function copyToNewRange(rSrc As Range, rDst As Range)
    Dim sSub As String
    If rSrc.Columns.Count > 1 Or rDst.Columns.Count > 1 Then
        copyToNewRange = CVErr(xlErrValue)
    ElseIf rSrc.Rows.Count <> rDst.Rows.Count Then
        copyToNewRange = CVErr(xlErrValue)
    Else
        sSub = "copyToNewRangeSub(" & _
               rSrc.Address(True, True, xlA1, True) & "," & _
               rDst.Address(True, True, xlA1, True) & ")"
        rSrc.Worksheet.Evaluate sSub
        copyToNewRange = vbNullString
    End If
End Function


Sub copyToNewRangeSub(rSrc As Range, rDst As Range)
    rDst.Value = rSrc.Value
End Sub

Note, there are several other issues in your code I have addressed

When you want your function to return an err, return an ... Error
To ensure the ranges are shaped correctly, counting cells alone is not enough
Don't loop over a range, copy it in one go.
Your function should return something
You should Dim all your variables (use Option Explicit to force this)
Use meaningfull parameter names

Thanks to Tim Willaims for the concept

Many thanks for illuminating response. Have to say I don't see why VBA function should be so anal. BTW reason for not doing copy in one go is that I want to do some other things on the way, just got tripped up at first stage! — user3759302, Jun 21 '14 at 07:47
Glad to help. Re copy in one go, just be aware that looping a range is slow. If you find performance to be a problem, consider using a Variant Array approach ( there are lots of examples on SO) — chris neilsen, Jun 21 '14 at 07:54

score 0 · Answer 2 · answered Jun 20 '14 at 08:31

0

In a worksheet, you can't use a function that DOES things. Only one that returns data. That is quite logical, since the function is evaluated every time the sheet changes.
If you really want that behaviour, use the Worksheet_Change event.
Also note that, in your example, it is very uneffective to copy cells 1 by 1, compared to copy a range at once.

answered Jun 20 '14 at 08:31

iDevlop

24,841
11
90
149

Many thanks for helpful response. See other comments. Will look at Worksheet_Change event (but surely purpose of any function is actually to *do* something!) – user3759302 Jun 21 '14 at 07:48

Copy a Range using a function and report errors

2 Answers2