VBA, User-defined formula argument referring to cell value calculated by Excel formula

Hi all,

I'm using this VBA code to show some QR codes in my spreadsheet.

Public Function getImage4(ByVal name As String) As String

  Dim imgURL As String
  Dim x&, y&, wdth&, hght& ' using "&" is the same as "As Long"
  Dim cellFunctionRunsOn As Range
  Dim img
  Dim oShape As Shape

  Set cellFunctionRunsOn = Columns(1).Find(what:=name)

  Dim XMLhttp: Set XMLhttp = CreateObject("MSXML2.ServerXMLHTTP")

  For Each oShape In ActiveSheet.Shapes
        If oShape.Type = msoPicture Then
            If Not Intersect(Range("B3"), Range(oShape.TopLeftCell, oShape.BottomRightCell)) Is Nothing Then
                oShape.Delete
                Exit For
            End If
        End If
  Next oShape

  XMLhttp.setTimeouts 1000, 1000, 1000, 1000

  imgURL = "https://chart.googleapis.com/chart?chs=100x100&cht=qr&chl=" + name


  XMLhttp.Open "GET", imgURL, False

  XMLhttp.send

  If XMLhttp.Status = 200 Then

   'It exists so get the image
    x = Range(cellFunctionRunsOn.Offset(0, 1).Address).Left
    y = Range(cellFunctionRunsOn.Offset(0, 1).Address).Top

    Set img = ActiveSheet.Shapes.AddPicture(Filename:=imgURL, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=x, Top:=y, _
        Width:=47, Height:=47)
    img.Placement = xlMoveAndSize

  Else

  End If

End Function

The problem I have is that the argument of the user-defined formula "name" has to refer to a cell "G8" that contains a formula in it (please refer to the picture below). If in "G8" I write "8816" everything is working perfectly but if I keep the formula in "G8", the user-defined formula "getImage4" is not working. Any hint?

BfBMtw7.png

Thanks,
Stefano

The problem is this line:

  Set cellFunctionRunsOn = Columns(1).Find(what:=name)

Try replacing that line with:

  Set cellFunctionRunsOn = Application.Caller

No idea. Really hard to diagnose, with no error code.