How to Insert a Picture Into a Cell Using VBA in Excel
Visual Basic for Application, VBA for short, is a form of Visual Basic 6 integrated into Microsoft Office programs. Through coding, VBA allows you to automate tasks in Office programs, including Excel. In some cases, you can even add new features to Excel using VBA.
Although you need to work with code to use VBA, that doesn’t mean that VBA is all letters and numbers. With VBA in Excel, you can create a macro that allows you to insert pictures into a cell or a range of cells. Read on to find out all about it!
In order to create a macro to insert pictures into Excel cells using VBA, you actually don’t need any advanced Visual Basic knowledge. All you need to do is enable Developer tools, create the macro and paste the right code.
However, if you’re interested in learning VBA and writing your own code someday, we’ve broken down the VBA code in the third section. Of course, you can alsoinsert pictures in Excel without using VBA. But this article is about getting it done through VBA. Let’s get down to business!
1. Enabling Developer Tools
In order to use VBA in Excel, you need to enable Developer tools in Excel. This will enable the Developer tab in the ribbon, which is disabled by default.
Now Developer tools, including access to VBA, are enabled for you. You don’t need to do this every time you want to use VBA in Excel. Developer tools will stay enabled until you disable them.
2. Creating the Macro and Inserting the Code
Now it’s time to get down to creating the macro. Alternatively, you could alsocreate a button using VBAfor this task, but we’re going to stick with macros.
Once you click Create, the VBA window will open up and display the code for your macro. Right now, the code will consist of two lines: ASubto initiate the macro, and anEnd Subto end it.
Let’s add some code to this macro. Add the following code between the two lines:
Your final code should be something like the below:
You don’t need to worry about saving your progress. Every change you make in VBA is instantly saved.
Now it’s time to see the code at work.
Now a prompt will pop open asking you to locate the image file you want to insert. Select your image and then clickOpen. You should now see your photo in the A1 cell!
Notice that the inserted picture is shrunk to fit cell A1. You can alter this, and also change the code to insert the picture in other cells or even a range of cells. In the next section, we’re going to break down the code and explain the parameters at work.
3. Breaking Down the Code
To make this VBA code work for you the way you want, you need to understand it. Once you do, you can alter the code to insert photos in any cell in any size. We’re going to go through the code bit by bit to make it easier to understand.
Once the code starts, we use theDimstatement to determine the variable’s type. We’ve got two variables here:photoNameAndPathandphotoitself. We’ve determined the former as aVariantand the latter as aPhoto.
From there, the photoNameAndPath variable runs and it opens an application to get the whereabouts of the picture file. This is done throughApplication.GetOpenFileName. TheTitleparameter is optional, and the content in it is displayed as the window name.
UsingIf photoNameAndPath = False Then Exit Sub, we specify that if an invalid or blank address is given, the process should be terminated. However, if a proper file is fed, thenSet photo = ActiveSheet.Pictures.Insert(photoNameAndPath)indicates that the picture should be set as the photo variable we defined earlier, and it should be inserted into the active spreadsheet.
Finally usingWith photoand the five lines following it, we elaborate on the image’s positioning..Leftand.Topindicate the start locations, while.Widthand.Heightindicate the end locations. If you’re looking to insert the image in other cells, or in a range, then these are the lines you should alter.
.Placementindicates whether the picture should be sized with the cells or inserted in free form. Setting it to1will size it with the cells.
Ultimately, we useEnd Withand thenEnd Subto close the macro. Note that you can change thephotoNameAndPathandphotovariables to whatever other name you like. Just remember to keep the names consistent throughout the code.
Get More Done in Excel With VBA
Excel is indeed a wonderful tool for organizing and analyzing data, but that doesn’t mean that Excel is inept when it comes to graphics. Though photos and bitmaps aren’t Excel’s strongest suit, Excel is still perfectly capable of handling them.
Although you can insert pictures in Excel using the interface as you would in other Office apps, you can also do it using VBA. With VBA, you can automate this task and even couple it with other tasks to run simultaneously. The possibilities with VBA in Excel are endless.
Follow this tutorial to create a custom VBA-automated data entry form.
When your rival has to bail out your assistant.
You can’t call this offline, Notion.
Revolutionize your driving experience with these game-changing CarPlay additions.
Don’t let aging hardware force you into buying expensive upgrades.
The best features aren’t the ones being advertised.
