Microsoft Excel Tutorial: Use Excel VBA to replicate the IMAGE function for images stored locally.
In episode 2606, I showed the new Insert Pictures in Cells and lamented that they did not change the IMAGE function to allow images stored on the local hard drive. Using the VBA Macro in this workbook, you can do it. The VBA is actually better because it remembers the image path and displays it in the formula bar.
To download the workbook or copy the VBA: [ Ссылка ]
Welcome to another episode of MrExcel! In this video, we're going to tackle a frustrating feature in Excel - placing local pictures in a cell using a formula. Microsoft has implemented this feature, but it's not quite what we were hoping for. But fear not, because I have a VBA hack that will allow us to use a formula to insert an image from our local hard drive into a cell. Let's dive in!
In a previous video, I used Power Query to pull images from a folder into Excel. However, when I tried to insert these pictures into cells using the "From this device" option, I ran into some issues. The order of the pictures didn't match the order in which they were pulled in by Power Query, and the formula bar only showed the word "picture" for each image. I also discovered that using a VLOOKUP or XLOOKUP table with the pictures worked, but it wasn't a perfect solution.
So, I set out to find a way to use a formula to insert images from our local hard drive into cells. And I did just that with the help of some VBA code. I've provided the code for you to use in your own personal macro workbook, so be sure to check out the link in the top right corner of the video. With this code, we can now use the "Insert picture in cell" method to insert images from our local hard drive into cells. And the best part? The formula bar now shows the full path and file name of the image, making it easier to search for specific images.
But wait, there's more! I also have a second macro that allows you to add the image to the right of the URL, giving you even more flexibility. And while these macros may not be as cool as the image function, they get us pretty close to where we want to be. Plus, the inserted images are considered a rich data type, so you can use the "Control + Shift + F5" shortcut to view a larger version of the image in a card format.
Now, you may be wondering where this new feature from Microsoft will end up. Currently, it's only available in the insider's beta and there are still some kinks to work out. But I have high hopes that it will eventually become a fully polished feature. In the meantime, I want to thank Microsoft for giving us this functionality and for allowing us to use VBA to enhance it. And as always, thank you for watching and be sure to like, subscribe, and ring the bell for more helpful Excel tips and tricks. Don't forget to leave any questions or comments down below. See you next time on MrExcel!
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
Table of Contents
(0:00) Enhancing Picture In Cells to use a formula
(0:30) Behind Scenes of 2606
(1:00) Sort Order is different
(1:30) Formula Bar shows only "Picture"
(1:40) Using XLOOKUP with Pictures In Cell
(2:08) Filtering Excel Picture in Cell
(2:33) Excel smart lookup on Picture in Cell
(2:56) Formula to point to picture
(3:55) IMAGE() function fails with local image
(4:05) Using VBA to solve problem
(4:51) VBA adds image path to formula bar!
(5:13) Filtering Pictures added by VBA works in Excel
(5:39) Add Image to Right
(6:28) Image is Rich Data Type - Show Card
(6:50) In VBA, use Selection.Value to get image path
(7:10) Inaccuracies in Help Topic
(7:43) Premature release?
(7:57) Clicking Like really helps the algorithm
This video answers these common search terms:
Adding image to the right of URL in Excel using VBA
Comparing inserted images and image functions in Excel
Filtering pictures in Excel using selected cell values
Future updates and improvements for pictures in cell feature
Generating image path with formula in Excel
Insert image in cell using formula
Power Query to import images into Excel
Rich data types and cards for inserted images in Excel
Sorting images in Excel from Windows Explorer
Using VBA macros to replace path with image in Excel
Using VLOOKUP or XLOOKUP with pictures in Excel
VBA hack for inserting local image in Excel cell
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
