Inserting Images into Excel Comments Using VBA

I use Excel for basic statistical analysis of manufacturing and field return data as part of daily routine. I also use Excel for department budgeting. During these tasks, I frequently need to add information to my spreadsheets about my information sources (see Wikipedia entry on data provenance). Since much of my information comes to me in the form of email or web pages, a quick and easy way to document this information is to take a screenshot of the information and insert the information into an Excel comment.

For years I have included images in Excel comments using the manual procedure described here. This approach works well but is slow. I finally decided that there has to be a better way and I wrote a Visual Basic for Applications (VBA) macro that handles all the ugly details. Here is a link to this spreadsheet. The spreadsheet does use a VBA macro. If you are concerned about security, just look in the macro and see what it does.

To see a sample image (Saturn), just mouse over the cell with the little red triangle in the upper right corner.

A little discussion on how to use the macro is in order:

  • Put an image into your clipboard (I use PicPick)
  • Select the cell where you want to put the image
  • Click the button labeled "Press"
  • Your selected cell will now have little red triangle in the upper right corner. You will see your image when you mouse over this cell.
  • I used a button for this example, but in my work I assign both a Quick Access toolbar icon and a keyboard shortcut to this macro.

Many thanks to the folks at these two web sites for posting code and clues that helped me put this macro together.

This entry was posted in Management, software. Bookmark the permalink.

27 Responses to Inserting Images into Excel Comments Using VBA

  1. Sam says:

    Im looking for this a long time now, It works but I keep getting an error afterwards..."The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

    • mathscinotes says:

      Hi Sam,

      Thanks for catching this. I will update the code this weekend. Again, appreciate the help.

      mark

  2. Thanks Mark, I was at the point of giving up.
    So what I really need is a macro that put or paste the image which is residing in the clipboard into the comment of the active cell, or a macro that cuts the pre-pasted image which is lying temporarily on a cell, into the comment of any desired cell

    • mathscinotes says:

      Could you tell me what version of Excel you have? It is working on all my versions. I am currently running Office 365 Version 1609 at work and home, however the routine ran with 2007 (32 bit) and 2010 (32 bit). What version are you running? Look under File/Account.

      mark

  3. Hi Mark
    You might be absolutely right on the compatibility. And that will be great news for me, as I need it for my Work computer, not my MacBook Air below. Sorry I should have told you upfront that I encountered this problem at home with my Mac, not at my work which has Office 10. I will let you know by this coming week when I tried it out at Work.

    My version of Excel for my MacBook Air (Mid 2012) ,
    macOS Sierra is .....

    Microsoft Excel for Mac
    version 15.32 (170309)
    Product ID: xxxxxxxxxxxxxxxx
    License: Office 365 Subscription
    2017 Microsoft

  4. Hi Mark,
    Sorry for the lapse, yes it works smooth and flawlessly on my job's pc. Thanks for your masterpiece. i wish it could work same way on my mac too. Hope you fix that someday.

  5. George B. says:

    Hi Mark,

    I’ve been using your wonderful “ImageToComment” utility for the past year or so and it’s proven to be very useful and quite a time-saver. However, since upgrading to a 64-bit version of Excel, it no longer works. I get the same error message that Sam reported. Did you ever get a chance to update the code to support 64-bit Excel? If so, where can I find it?

    I’m running Excel 2016 in Windows 7. Your macro works fine with 32-bit Excel but not 64-bit Excel.

    Thanks!
    George

    • mathscinotes says:

      Hi George,

      I will need to find a 64-bit version to play with. Give me a few days. My work is still on 2010, so no help there.

      Mark

      • George B. says:

        Hi Mark,

        I was wondering whether you had any luck tracking down a 64-bit version of Excel and updating your utility to support it?

        Cheers,
        George

        • mathscinotes says:

          Hi George,

          I have identified changes that were made in the clipboard API between the 32-bit and 64-bit interfaces. This may be the issue. I will continue to try to get a 64-bit Excel copy – hopefully, when I see my son this weekend.

          biegert

          • mathscinotes says:

            I now have a 64-bit copy of Excel and the routine fails. I will be working on it in the next few days. Darn Microsoft can't leave well enough alone.

            mark

        • mathscinotes says:

          I still have not tracked down a 64-bit copy of Excel. On the bright side, I am buying a new computer – it is a custom. It will be here in 3 weeks. I will put 64-bit Excel on that unit. I am reluctant to put it on my current unit because I depend on that unit for my income. I don't want to break anything.

          biegert

  6. Andrés says:

    Hi Biegert,

    Were you able to check the issue in office 64 bits?.

    It is not working on Excel 365 (16.0.12718.20004) 64-bit 🙁

    Thanks.

    Kind regards,

    Andrés

  7. Andres says:

    Hi Biegert,

    Any news about your great macro running on Excel 64 bits?..

    Thanks.

    Andres

    • mathscinotes says:

      Hi Andres,

      I wasn't sure exactly what changed in the Microsoft system routine being called. I am closer to figuring it out. Kind of a nasty change.

      biegert

  8. tp says:

    I've been googling "excel vba photo comment" and variations of those keywords for two days now and very luckily found your post. Thank you so much for creating this and posting why you built the tool. I'm also looking for a way to quickly capture data sources or "data provenance" via a screen snippet into a comment. This saves so much time. Thanks!!
    FYI - I'm running an x64 system and your code is working fine.

  9. Terry Hansen says:

    Tried this and still not updated for 64 bit systems. Throws compile error.

    • mathscinotes says:

      I have not found a way to access images on the 64-bit systems. I have a question into an expert on the subject. I want it to work myself.

  10. Terry Hansen says:

    Does this imbed the image into the file or does it use a link? I need to be able to send this spreadsheet to others and not worry about finding and sending linked files/images.

    • mathscinotes says:

      On 32-bit systems, it embeds the image. On 64-bit systems, there is a pointer issue that I am trying to work through. It has been thorny.

  11. Terry Hansen says:

    Thanks for the quick response. Good luck finding the solutions. It will be a huge time saver for me and many others when you do get it working.

  12. Martin Novák says:

    Thanks for excellent code.
    Some pictures are too large, so I needed to zoom or strech to "unified" dimensions.
    I just added this code before the block, where you have determined the cell:
    'Picture zoom/stretch
    ImageRatio = ImageWidth / 640
    If ImageWidth 640 Then
    ImageWidth = 640
    ImageHeight = ImageHeight / ImageRatio
    End If
    Where 640 is a maximum number of pixels of one side of the picture and sides remain in the same ratio.
    Of course definition of ratio must be added.
    Dim ImageRatio As Integer 'Image width/height ratio
    Hope this helps to everyone.
    Regards
    Martin

  13. Andres says:

    Hello Biegert,
    I have found another way to do the image-paste. The code is not mine, I found it somewhere, but unfortunately don't remember where. For sure there is a lot of room for improvements.

    If you want, have a look to it, and see how you can improve it.
    *****
    Option Explicit
    Sub PictureExport()
    On Error GoTo ER
    Dim TempChart As String, Picture2Export As String
    Dim PicWidth As Long, PicHeight As Long
    Dim ActSheet As String

    'RemoveSpaces from sheet name
    'Dim Ws As Worksheet
    ' For Each Ws In Worksheets
    ' Ws.Name = RTrim(Ws.Name)
    ' Next Ws
    'END RemoveSpaces from sheet name

    Selection.ClearComments

    ActSheet = ActiveSheet.Name

    ActiveSheet.Paste
    Selection.Name = "PrintScreen"
    Picture2Export = Selection.Name

    'Store the picture's height and width in a variable
    With Selection
    PicHeight = .ShapeRange.Height
    PicWidth = .ShapeRange.Width
    End With

    'Add a temporary chart in sheet1
    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:=ActSheet
    Selection.Border.LineStyle = 0
    TempChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)

    With ActiveSheet
    'Change the dimensions of the chart to suit your need
    With .Shapes(TempChart)
    .Width = PicWidth
    .Height = PicHeight
    End With

    'Copy the picture
    .Shapes(Picture2Export).Copy

    'Paste the picture in the chart
    With ActiveChart
    .ChartArea.Select
    .Paste
    End With

    'Finally export the chart
    .ChartObjects(1).Chart.Export Filename:=ThisWorkbook.Path & "\tmp.jpg", FilterName:="jpg"
    .Shapes(TempChart).Cut
    End With

    ActiveCell.AddComment
    ActiveCell.Comment.Shape.Fill.UserPicture ThisWorkbook.Path & "\tmp.jpg"
    ActiveCell.Comment.Shape.Width = PicWidth
    ActiveCell.Comment.Shape.Height = PicHeight
    ActiveSheet.Shapes("PrintScreen").Delete
    Kill ThisWorkbook.Path & "\tmp.jpg"
    Application.ScreenUpdating = True
    Exit Sub
    ER:
    MsgBox "An error has occurred. Make sure you have a picture in your clipboard.", vbCritical, "...::: Error :::..."
    Application.ScreenUpdating = True
    End Sub
    ******

    I've created an add-on so the functionality is always available to be used with a shortcut key.

    One of the issues (you will see it in the commented code), is if the sheet name has space, it will fail.

    It work on Excel 365 13827. 20004 (Beta Channel).

    Please let me know your comments, and if you somehow improved it, please share them also.

    Thanks in advance.

    Kind regards,
    Andres

  14. MICHAEL PHILLIPS says:

    Would it be possible to update this script for 64bit machines. It has been a very useful script but now is not functional with newer computers. I get an error that says add Ptrsafe attribute.

    Thanks for your help!

Comments are closed.