Google Sheets IMAGE Function

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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").
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
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-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
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(!?):-

VBA Code:
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

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
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")

1642091322795.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

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm thinking to replace it with nothing.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
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?
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
=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.

VBA Code:
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
VBA Code:
 
Solution
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,466
Messages
5,764,488
Members
425,219
Latest member
datdanigg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top