Excel: create image from cell range

Question

Using Excel VBA, I want to create an image showing the content of a cell.

• Manually, I can do this by selecting a cell, clicking on 'copy', selecting another cell, and clicking on 'paste as image'. Then I can select the image and set its formula to =$B$2, for example.

• The Macro recorder tells me that this is equivalent to the following code:

  Selection.Copy  
  ActiveSheet.Pictures.Paste.Select
  ActiveCell.FormulaR1C1 = "=R[1]C[1]"
  

• I want to achieve the same without using the copy-paste-select commands. In other words, I would like to create a Picture object with predefined FormulaR1C1 property.

• I tried ActiveSheet.Pictures.Add(Left, Top, Width, Height) but only got Runtime error '1004': No link to paste. I don't understand what this means.

• I tried ActiveSheet.Pictures.Insert and ActiveSheet.Shapes.AddPicture, but they both require a file name to load an external image file, which is not what I want.

Answer 1

Instead of copying the cell, then telling it you want to paste to another cell as a picture, try using:

Range("A1").CopyPicture Appearance:=xlScreen, Format:=xlPicture

Now A1 is in your clipboard as a picture. You can use boring old paste to stick it somewhere else.

Range("B1").Select
ActiveSheet.Paste

Answer 2

Are you trying to get a "live" picture which is linked back to the source range, like using the Camera tool in Excel?

Sub Macro1()

    Dim s, rng As Range, rngDest As Range

    Set rng = ActiveSheet.Range("B2:C3")
    Set rngDest = ActiveSheet.Range("E10")

    rng.Copy
    With ActiveSheet.Pictures.Paste(link:=True)
        .Left = rngDest.Left
        .Top = rngDest.Top
    End With

End Sub

I don't think you can get around using copy/paste though.