1

I have a cell in A1 with HYPERLINK formula calling an UDF:

=HYPERLINK("#funcCopy()";"macro")

The UDF simply copy and paste 3 cells into another location:

Public Function funcCopy()
    Set funcCopy = Selection

    Dim plan As Worksheet
    Dim copyrow As Range

    Set plan = ThisWorkbook.Sheets("Plan1")

    Set copyrow = plan.Range("A3:E3")
    copyrow.Copy plan.Range("A5")
    MsgBox "I'm actually working!"
End Function

But when I click on the hyperlink, it just doesn't copy/paste. I add the MsgBox to verify if the function is running (and it is!). Running through VBE works fine.

I'm out of solution to this. Is there something I can do?

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Andre Lenz Barbosa
  • 13
  • 2
  • This should be a `Sub`, not a `Function` IMO – urdearboy May 03 '19 at 02:57
  • AFAIK, you cannot use a UDF to update a worksheet. There are some cases though like what is posted by Tim [here](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet), that works. – L42 May 03 '19 at 03:03
  • You can debug your function by running it from a Sub. – Tim Williams May 03 '19 at 03:09
  • 1
    I have to admit this is a rather clever use of a `Function`. Not a UDF though - a UDF would be invoked from a worksheet formula. If you did `=funcCopy()` in a cell, that cell's value would be `#VALUE!`, because as others have mentioned, a UDF can't modify other cells. – Mathieu Guindon May 03 '19 at 03:48
  • 1
    @urdearboy the function returns a `Variant/Range` (return type is implicit, should be `As Range`), that the `HYPERLINK` formula links to. Change it to `Set funcCopy = Selection.OffSet(1,1)` for extra fun =) – Mathieu Guindon May 03 '19 at 03:53

1 Answers1

2

Copy doesn't seem to work, but a direct value transfer does, if that suits your requirements.

Public Function funcCopy() As Range
    Set funcCopy = Selection

    Dim plan As Worksheet
    Dim copyrow As Range

    Set plan = ThisWorkbook.Sheets("Plan1")

    Set copyrow = plan.Range("A3:E3")   
    plan.Range("A5:E5").Value = copyrow.Value

    MsgBox "I'm actually working!"
End Function

enter image description here

(Edited to make the return type explicit, credit goes to @Mathieu Guindon for pointing that out.)

BigBen
  • 46,229
  • 7
  • 24
  • 40