Google Sheets IMAGE Function

[excelbytes]

Is there any way to replicate the IMAGE function from Google Sheets in Excel? Maybe a VBA code? From what I can tell, the function in Google Sheets takes a URL and imbeds that image into a cell, not a hovered object like Excel would. The syntax is =IMAGE(url, [mode], [height], [width]). An example pulling in Google's logo is =IMAGE("https://www.google.com/images/srpr/logo3w.png").

 

[Dan_W]

As far as I'm aware, there isn't an equivalent inbuilt worksheet function that does this in Excel, though its certainly doable with VBA. It is simply a matter of aligning the image with the target cell dimensions, and then setting a property that it moves/sizes with the cell. I'd be surprised if someone somewhere hasn't already written VBA code that does this - let me have a look to see if I can find one.

 

[Dan_W]

Well I didn't find one that worked as a worksheet function, but then I didn't look for very long, thinking I could just write one and save time - we'll see if it's any good(!?):-

Function IMAGE(TargetURL As String, Optional AutoAdjustToCell As Boolean = True)
   
    Dim TargetCell As Range, Img As Object
    Dim CallerCell As Variant
   
    CallerCell = Application.Caller.Address
   
    If VarType(CallerCell) = vbString Then
        Set TargetCell = Range(CallerCell)
        Set Img = ActiveSheet.Pictures.Insert(TargetURL)
        With Img
            .Top = TargetCell.Top
            .Left = TargetCell.Left
            .ShapeRange.LockAspectRatio = msoFalse
            .Placement = xlMoveAndSize
              If AutoAdjustToCell = True Then
                .Width = TargetCell.Width
                .Height = TargetCell.Height
            End If
        End With
    End If
   
    IMAGE = TargetURL
    Set Img = Nothing
End Function

This seems to work like the IMAGE function. It takes a second argument called AutoAdjustToCell - TRUE or FALSE. By default, it is set to true, so it will change its shape to fit the cell it is being called from. If set to false, the picture will be inserted at the cell position, but with the normal size/aspect ratio of the picture.

It is not locked to the aspect ratio, but can be if preferred. I have had it set to return the URL address of the picture - this too can be changed easily enough.

Is that what you were after?

 

[excelbytes]

I'm not sure. I'm not too well versed in VBA. How will this work? With the Google Sheets IMAGE function, you insert the URL into the IMAGE function in the cell you want it to appear. Where is the URL defined in this code?

 

[excelbytes]

Actually, I may have figured it out. I'll play with it and see if it will work for me. Thanks a ton!

 

[Dan_W]

Hi - sorry, I should've explained. This function should be inserted into a Standard Module and it will be available to anywhere in the workbook.
I have written it so that it can be called from the worksheet in the same way as you would use the Google Sheet IMAGE function:

=IMAGE("https://www.google.com/images/srpr/logo3w.png")

I'm thinking it's probably worth removing the part where it also returns the URL of the image - what would like to replace it with? Nothing?

 

[excelbytes]

I'm thinking to replace it with nothing.

 

[Dan_W]

I agree. I had thought it might make sense to put something there in case the images later were moved around, as some kind of marker that there was a formula there, but it just looks a bit awkward here. Anything else you think should be changed while I have the code open?

 

[excelbytes]

Nope, that should do it!

 

[Dan_W]

Ok - well, as I was about to change it, I saw that you had listed the different arguments for the google function, and thought I might be cool to work just like Googles. So here it is. I should point out that this won't work if called from VBA - only from the worksheet. It it still compatible with basic usage (e.g., =IMAGE(URL)) but now has the different image modes. By default, it will be the original size, and if you want to make the image fill the whole cell, use image mode 2.

USAGE:

=IMAGE("https://www.mysite.com/myimage.jpg")   ' This will insert the image at the current cell
=IMAGE("https://www.mysite.com/myimage.jpg", 2)    ' This will fill the current cell with the image
=IMAGE("https://www.mysite.com/myimage.jpg", 4, 200, 100)    ' This will insert the image at the current cell, setting custom height/width properties

The first 6 lines (the enumeration) must be at the very top of the module.

Enum ImageModeEnum
    FitCell_MaintainAspect = 1              'resizes the image to fit inside the cell, maintaining aspect ratio.
    FitCell_IgnoreAspect = 2                'stretches or compresses the image to fit inside the cell, ignoring aspect ratio.
    OriginalSize = 3                        'leaves the image at original size, which may cause cropping.
    CustomSize = 4                          'allows the specification of a custom size.
End Enum

Function IMAGE(TargetURL As String, Optional ImageMode As ImageModeEnum = 3, Optional CustomHeight As Long = -1, Optional CustomWidth As Long = -1)
   
    Dim TargetCell As Range, Img As Object
    Dim CallerCell As Variant
   
    CallerCell = Application.Caller.Address
   
  If VarType(CallerCell) = vbString Then
        Set TargetCell = Range(CallerCell)
        Set Img = ActiveSheet.Pictures.Insert(TargetURL)
        With Img
            .Top = TargetCell.Top
            .Left = TargetCell.Left
            .ShapeRange.LockAspectRatio = IIf(ImageMode = 2, msoFalse, msoTrue)
            .Placement = xlMoveAndSize
            Select Case ImageMode
                Case FitCell_MaintainAspect
                    If .Width > .Height Then
                        .Width = TargetCell.Width
                    Else
                        .Height = TargetCell.Height
                    End If
                Case FitCell_IgnoreAspect
                    .Width = TargetCell.Width
                    .Height = TargetCell.Height
                Case CustomSize
                    .ShapeRange.LockAspectRatio = msoFalse
                    If CustomHeight >= 0 And CustomWidth >= 0 Then
                        .Width = CustomWidth
                        .Height = CustomHeight
                    ElseIf CustomHeight >= 0 Then
                        .Height = CustomHeight
                    Else
                        .Width = CustomWidth
                    End If
            End Select
        End With
    End If
   
    IMAGE = ""
End Function