Hello Excellers, and welcome back to another blog post in my #MacroMonday Excel tip series. Today I will show you how to write an Excel VBA macro that will save your active Excel chart as an image. This macro is handy if you regularly have to export Excel charts from your Excel workbook. Then, Excel exports the image and saves it in the exact location as your Excel workbook with one click. Or, you can specify another location.
Starting the Macro.
When you decide to write an Excel macro, you have two places to save it.
- Store it in the current Excel workbook you are working on creating.
- Save the Macro in your Personal Macro workbook.
So, what is the difference? It is simple. If the code you are writing is specific to that workbook, then store it within the workbook. If you can reuse a type of Macro, keep it in your Personal Macro Workbook. In this example, I could reuse this Macro repeatedly to convert merged cells to centre across selection. I, therefore save it in my Personal Macro workbook.
If you want to read more about your Excel PMW then check out my blog posts below.
At this stage go ahead and insert a new module. First open the Visual Basic Editor.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
- Insert Module.
Copy The Code. How To Save An Excel Chart As An Image.
You can copy the complete VBA code below to save your Excel chart as an image. Always back up your work before running any VBA code.
Step 1. Name The Macro.
The first step is to name your macro once you have inserted a new module. I have named the macro ExportMyChart. Notice that Excel will insert the End Sub line of code once you have named the macro. The rest of the code should be inserted between these two lines.
Step 2. Declare Variables.
Next, any variables for the macro need to be declared. This simply creates a space in Excel’s memory to store the values. In this example, there are two variables. The two variables are the name of the image once exported and the file path location of the exported chart.
Step 3. Set The Location To Export The Chart.
The third step is to set the location of the exported chart. Just go ahead and change this to suit your own requirements.
Step 4. Export The Active Chart And Save As An Image.
This line of code exports the active chart to the location and name specified in Step 3.
Step 5. Ending The Macro.
Once the chart has been exported then the macro ends. Excel already has this line of code from the beginning of the macro. It is now time to test the Excel Macro. Does it work?. Let me know in the comments below if you have used this code to save your Excel chart as an image.
If you want to read all of the Macro Monday blog posts in the series then bookmark the #MacroMonday page. If you want to also read all of the #FormulaFriday blog posts feel free to read and book mark that too.