Copy image from Excel form to image object in VBA

Question

I would like to copy the image that was pasted into an Excel spreadsheet into an Image using VBA. Here is the code I tried:

Dim logo As Image Set logo = New Image logo.Picture = ThisWorkbook.Sheets("Sheet1").Pictures("Picture1") 

The last line fails with a type mismatch error. When I look at the logo. Figure in the clock window, it is indicated as the type Picture; when I assign the variable Object to the expression on the right of the equal sign, it is displayed as the type Picture / Picture. I do not know enough about the hierarchy of VBA objects to find out if these types are related, and how to convert them from one to another, and could not find anything about it, despite the diligent Google searches.

If I replace the last line with the following:

 logo.Picture = LoadPicture(ThisWorkbook.Path & "\Logo.bmp") 

the file loads and the rest of my program works. I searched a lot of posts here and elsewhere and found nothing useful except suggestions for exporting an image to a file, and then importing it into an Image object using LoadPicture. Any suggestions on how to get an image from a worksheet instead of a file?

In case it matters, the rest of the code uses the .Picture.Handle logo as a GDI HANDLE bitmap and transfers it to an external library to display the image on an external device. If there is a way to get the HANDLE GID bitmap from another object, this will work too.

Answer 1

Have you tried pasting it as an OLEObject ?

 Me.logo.Picture = ThisWorkbook.Worksheets("Sheet1").OLEObjects("Picture1").Object.Picture 

The images you want to copy must be pasted into the sheet as an image (ActiveX control).

Answer 2

Instead, define it as Object and assign it your image using Set :

 Dim Logo As Object Set Logo = ThisWorkbook.ActiveSheet.Pictures("Picture 1")