-1

In continuation of this question by Bluesector:

He was looking for a way to basicly do a VLookup() to place an image in a cell below. I was working on an answer using a user defined function (UDF).

I wrote the following function that:

  • Takes a range as input variable. That Range should contain the value that is to be looked up.
  • Finds a match on the worksheet "PIC" and copies that cell.
  • Pastespecial a linked picture to the active cell (the cell it has been called from) 


Function insertIMG(ByVal rng As Range)
    Dim row As Integer
    row = Application.WorksheetFunction.Match(rng, Sheets("PIC").Range("A1:A5"), 0)
    Sheets("PIC").Range("B" & row).Copy
    With Worksheets("Blad1")
       'adapt worksheet name as appropriate
       .Pictures.Paste(Link:=True).Select
    End With
    insertIMG = ""
End Function

Which results in a #Value error when inserted in a cell on a workbook.

When I run this code from within a sub like so:

Sub test()
    Application.Workbooks("Map2").Worksheets("Blad1").Range("D5").Select
    insertIMG (Application.Workbooks("Map2").Worksheets("Blad1").Range("D4"))
    'adapt workbook and worksheet name as appropriate
End Sub

it works as expected. In this I explicitly select cell D5, as that would be where the function is called from, and would thus have the active selection when used in an UDF.

Is there any limitation to the UDF's that I'm missing here?

Luuklag
  • 3,897
  • 11
  • 38
  • 57

1 Answers1

0

You are missing some limitations to UDF's. They cannot change any other range in Excel, only the cell from which they are called. Most methods also aren't available in UDF's.

See the link below for the full Microsoft answer:

https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel

What you could do is use your "test" sub, and use it in a Worksheet_Change event on the worksheet from which you wanted to call the function.

Beek
  • 376
  • 1
  • 10
  • But in this case no other range is changed. If I rework the formula to simply paste, instead of pastespecial it will paste the content of the matched cell, so that part works fine. – Luuklag Aug 07 '19 at 07:50
  • I'm not sure i understand what you mean, but i'm fairly certain what you're trying to do isn't possible in an UDF. All it's meant to do is calculate some value for "insertIMG" and place that into the cell from which the UDF is called.In the article I shared Microsoft puts it like this: " Functions can perform a calculation that returns either a value or text to the cell that they are entered in". – Beek Aug 07 '19 at 08:00