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: Excel Place Local Pictures In Cell Using Formula and a VBA Hack - Episode 2607a Sample Files - MrExcel Publishing
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:33) 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:57) 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) Wrap-up
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: Excel Place Local Pictures In Cell Using Formula and a VBA Hack - Episode 2607a Sample Files - MrExcel Publishing
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:33) 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:57) 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) Wrap-up
Transcript of the video:
Hey, it's Bill Jelen from MrExcel. This is one of those.
"Boy, Microsoft did this feature, not the way that I would've wanted it, and I'm pretty disappointed".
But one day later, I figured out a great hack using VBA to allow us to use essentially, a formula to insert an image in a cell when that image is on the local hard drive.
Let's go.
All right.
In this episode we're going to put a picture in a cell using a formula and that picture is stored locally instead of on the web.
See, back in video 2606, there were a couple of problems that cropped up along the way.
The first thing I did was, I found a folder of images and I use Power Query, to pull those into Excel.
You don't need to see those steps. We'll just do it real fast.
And then the next thing I did was insert pictures, place in cell, from this device.
And I selected all of the pictures in the folder.
Here's the problem. Okay?
For whatever reason, the order that these came in from Windows Explorer doesn't match the order that Power Query gave me the list.
Once it got to a point where they were sort of lined up, like from row 12 on down, everything was fine.
But these items here that start with numbers are being sorted differently and it was just a mess.
I was also frustrated that the thing that shows in the formula bar for each item is just the word picture.
Also, in that video I alluded to the fact that you could use a V lookup table or an X lookup table with the pictures and sure enough, that works.
So here we check and see if sales are greater than quota, and if so, lookup, yes, otherwise, no.
And it returns a green yes photo if we met the quota, a red, no photo if we didn't.
That's great.
Now I have a few here that are the photos, and then here I just have the old formula that was yes and no.
I was really hopeful that what I could do, is right click here and say, filter to selected cells value and get all of the no pictures.
But instead, what I get is everything with a picture.
Here we go. And why is that?
Well, because the filter is seeing this as the word picture, that was a little frustrating.
In fact, here, this is kind of crazy, I'm going to right click on a picture and go to smart lookup.
And what do they do?
They show me the definition of picture, it's a painting or a drawing.
So back in 2606, I was saying it'd really be nice if we could have a formula to bring this in.
And that's what I've solved today using some VBA.
We all know when the image function came out, it allowed us to take an image from a website and bring it into Excel.
And a lot of people said, "Boy, this would be really great if we could do it with images on our local hard drive". There's a lot of times where we might want to just build a path to an image.
So for example, I have a folder up there and then I want to join in the name of the file.
But let's see, there's a few things we have to do.
Around the A-4, I want to substitute and change area occurrence of a space to a dash or a hyphen. And then at the end I need to add &.ping like that. And let's see, does it matter?
I don't know, it probably doesn't matter. I probably don't need this lowercase here.
All right.
But now I have a great formula that will generate the path to the image for these items.
And when I heard that Microsoft was going to allow the images to be from the local hard drive, I had hoped that what they were going to do was change the image function to allow us to do this, but they didn't.
So what I had today is two macros for you written in VBA.
To get these macros, you're just going to click that I on the top right hand corner, go out to my website, where you can copy this code and paste it into your personal macro workbook.
The first one is called replace path with image.
And we're going to use this brand new method called, insert picture in cell, where we give it a path.
Now this of course, looks like recorded code, where we have to select the cell and do selection.insertpictureincell.
Wouldn't it be nice if we could just do cell.insertpicturincell, but for whatever reason, that's not working so it has to be two lines.
So we just select this, press alt, F8, and we're going to say, replace path with image, click run.
And just like that, bam, all of those images are in.
But even better than this, remember the problem that when we do place in cell.
The thing that shows up in the formula bar in each case, is the word picture.
But this selection.insertpictureincell actually puts a value in the formula bar that shows the path and file name, where it came from.
So that would allow me to do something crazy like search for all of the images that have VBA in the name.
How much better is that?
I suppose, if your images were stored on one drive and the path was revealing something secret, that might be a problem for you.
But for me, it's all just in this images folder and I don't have a problem embedding the full path and file name in the cell.
Now, there might be some times where these values are coming from a pivot table and you need your formulas to be live.
Of course, the great thing about the image function is that the formula is live.
If you would somehow point to a different path, it's going to go get that other path.
So I have a second macro out there, called add image to right.
This does the same thing, but it adds the image to the right of the URL.
So you select all of these, alt, F8, add image to, right click, run, and now it adds it in the cell to the right.
Of course, if anything changes, you're going to have to rerun the macro, it's not as cool as the image function is.
But these two macros get me like to 94% of where I wanted the image function to be.
Hey, just as a side, these inserted images are rich data type like you would get here on the data types and of course, control, shift, F5, opens a card.
Now we don't have the icon here, but if you press control, shift, F5, you'll see the larger version of the image, it's essentially a card.
You could also right click and say, show me details, to get the same card.
Also a note, if you would ask for selection.value here, that's an error but if you ask for selection.formula, it will return what's up in the formula bar.
So there is a way in VBA to get the path back out, provided you used VBA to put it in.
If you just use the user interface, it's going to give you picture.
So we have to wonder, with this new feature, where is it going to end up? I mean right now, it's just in the insider's beta.
When I go and look at the help topic, it's funny.
They've written a lot of things and some of them are currently working.
Like insert multiple pictures at once and you can paste a picture, but not the way that they suggest here.
And switching from a picture over cells to picture in cell, none of this is working yet.
Switch from picture in cell to picture over cells, that's not working.
And the whole thing with alt text, it says it's going to work, but it doesn't work today.
It almost feels like this feature was released a bit too early or maybe they accidentally flipped the switch and gave it to some insiders.
The great news is, they're still working on it apparently, and will eventually end up with a fully polished feature.
All right, well hey, thanks to Microsoft for giving us this great new pictures in cell functionality and really thanks for allowing us to use VBA to kind of tweak it a little bit and make it a little bit better in the long run. I want to thank you for stopping by.
We'll see you next time for another Net Cast from MrExcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
"Boy, Microsoft did this feature, not the way that I would've wanted it, and I'm pretty disappointed".
But one day later, I figured out a great hack using VBA to allow us to use essentially, a formula to insert an image in a cell when that image is on the local hard drive.
Let's go.
All right.
In this episode we're going to put a picture in a cell using a formula and that picture is stored locally instead of on the web.
See, back in video 2606, there were a couple of problems that cropped up along the way.
The first thing I did was, I found a folder of images and I use Power Query, to pull those into Excel.
You don't need to see those steps. We'll just do it real fast.
And then the next thing I did was insert pictures, place in cell, from this device.
And I selected all of the pictures in the folder.
Here's the problem. Okay?
For whatever reason, the order that these came in from Windows Explorer doesn't match the order that Power Query gave me the list.
Once it got to a point where they were sort of lined up, like from row 12 on down, everything was fine.
But these items here that start with numbers are being sorted differently and it was just a mess.
I was also frustrated that the thing that shows in the formula bar for each item is just the word picture.
Also, in that video I alluded to the fact that you could use a V lookup table or an X lookup table with the pictures and sure enough, that works.
So here we check and see if sales are greater than quota, and if so, lookup, yes, otherwise, no.
And it returns a green yes photo if we met the quota, a red, no photo if we didn't.
That's great.
Now I have a few here that are the photos, and then here I just have the old formula that was yes and no.
I was really hopeful that what I could do, is right click here and say, filter to selected cells value and get all of the no pictures.
But instead, what I get is everything with a picture.
Here we go. And why is that?
Well, because the filter is seeing this as the word picture, that was a little frustrating.
In fact, here, this is kind of crazy, I'm going to right click on a picture and go to smart lookup.
And what do they do?
They show me the definition of picture, it's a painting or a drawing.
So back in 2606, I was saying it'd really be nice if we could have a formula to bring this in.
And that's what I've solved today using some VBA.
We all know when the image function came out, it allowed us to take an image from a website and bring it into Excel.
And a lot of people said, "Boy, this would be really great if we could do it with images on our local hard drive". There's a lot of times where we might want to just build a path to an image.
So for example, I have a folder up there and then I want to join in the name of the file.
But let's see, there's a few things we have to do.
Around the A-4, I want to substitute and change area occurrence of a space to a dash or a hyphen. And then at the end I need to add &.ping like that. And let's see, does it matter?
I don't know, it probably doesn't matter. I probably don't need this lowercase here.
All right.
But now I have a great formula that will generate the path to the image for these items.
And when I heard that Microsoft was going to allow the images to be from the local hard drive, I had hoped that what they were going to do was change the image function to allow us to do this, but they didn't.
So what I had today is two macros for you written in VBA.
To get these macros, you're just going to click that I on the top right hand corner, go out to my website, where you can copy this code and paste it into your personal macro workbook.
The first one is called replace path with image.
And we're going to use this brand new method called, insert picture in cell, where we give it a path.
Now this of course, looks like recorded code, where we have to select the cell and do selection.insertpictureincell.
Wouldn't it be nice if we could just do cell.insertpicturincell, but for whatever reason, that's not working so it has to be two lines.
So we just select this, press alt, F8, and we're going to say, replace path with image, click run.
And just like that, bam, all of those images are in.
But even better than this, remember the problem that when we do place in cell.
The thing that shows up in the formula bar in each case, is the word picture.
But this selection.insertpictureincell actually puts a value in the formula bar that shows the path and file name, where it came from.
So that would allow me to do something crazy like search for all of the images that have VBA in the name.
How much better is that?
I suppose, if your images were stored on one drive and the path was revealing something secret, that might be a problem for you.
But for me, it's all just in this images folder and I don't have a problem embedding the full path and file name in the cell.
Now, there might be some times where these values are coming from a pivot table and you need your formulas to be live.
Of course, the great thing about the image function is that the formula is live.
If you would somehow point to a different path, it's going to go get that other path.
So I have a second macro out there, called add image to right.
This does the same thing, but it adds the image to the right of the URL.
So you select all of these, alt, F8, add image to, right click, run, and now it adds it in the cell to the right.
Of course, if anything changes, you're going to have to rerun the macro, it's not as cool as the image function is.
But these two macros get me like to 94% of where I wanted the image function to be.
Hey, just as a side, these inserted images are rich data type like you would get here on the data types and of course, control, shift, F5, opens a card.
Now we don't have the icon here, but if you press control, shift, F5, you'll see the larger version of the image, it's essentially a card.
You could also right click and say, show me details, to get the same card.
Also a note, if you would ask for selection.value here, that's an error but if you ask for selection.formula, it will return what's up in the formula bar.
So there is a way in VBA to get the path back out, provided you used VBA to put it in.
If you just use the user interface, it's going to give you picture.
So we have to wonder, with this new feature, where is it going to end up? I mean right now, it's just in the insider's beta.
When I go and look at the help topic, it's funny.
They've written a lot of things and some of them are currently working.
Like insert multiple pictures at once and you can paste a picture, but not the way that they suggest here.
And switching from a picture over cells to picture in cell, none of this is working yet.
Switch from picture in cell to picture over cells, that's not working.
And the whole thing with alt text, it says it's going to work, but it doesn't work today.
It almost feels like this feature was released a bit too early or maybe they accidentally flipped the switch and gave it to some insiders.
The great news is, they're still working on it apparently, and will eventually end up with a fully polished feature.
All right, well hey, thanks to Microsoft for giving us this great new pictures in cell functionality and really thanks for allowing us to use VBA to kind of tweak it a little bit and make it a little bit better in the long run. I want to thank you for stopping by.
We'll see you next time for another Net Cast from MrExcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
