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.

11 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 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

           

Leave a Reply

Your email address will not be published. Required fields are marked *