VBA Archives

How To Save An Excel Chart As An Image

October 25, 2021 by Barbara —Leave a Comment

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.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

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.

Option Explicit

Sub ExportMyChart()


'Create variables to hold the path and name of image

Dim myimagePath As String
Dim cht As Chart

'Change this location as required
myimagePath = "C:\Users\UserName\Documents\myImage.png"
Set cht = ActiveChart

'Export the chart, save Excel chart as an image
cht.Export (imagePath)

End Sub

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.

Sub ExportMyChart()
End Sub

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.

Dim myimagePath As String
Dim cht As 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.

'Change this location as required
myimagePath = "C:\Users\UserName\Documents\myImage.png"
Set cht = ActiveChart

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.

'Export the chart
cht.Export (imagePath)

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.

More Excel Tips.

Convert Merged Cells To Centre Across Selection.

October 18, 2021 by Barbara —Leave a Comment

Hello Excellers, welcome back to another #MacroMonday in my 2021 series. This tutorial will show you how to write a simple Excel VBA macro that converts merged cells to centre across selection in your Excel worksheet.

Using Merged Cells.

Many Excel users like to merge cells on their worksheets, whereas some never use them at all. I rarely use merged cells myself. Sometimes for an odd Excel template, they can be helpful aesthetically to improve the look and feel and contribute to the flow of the Excel solution. However, when it comes to functionality or developing serious spreadsheets, I do not use them. There are a few reasons I choose not to use them, which I will explain below.

Reasons Not To Use Merged Cells.

I have three main reasons I avoid using merged cells. These reasons have not changed for the years I have worked with Excel.

The risk I find with merging cells are

Unable to sort data.
You lose the ability to copy and paste.
Running VBA programming code does not handle the merged cells well at all. Usually, more code is needed to deal with the merged cells. So, I like to avoid them.

So, here is a solution that looks like merged cells with all the usual functionality and avoids my three issues above?. Great!.

Centre Across Selection. The Alternative To Merged Cells.

Anytime you need to think that merged cells might be a good idea, the alternative will be better. Yes, there is an excellent alternative to merged cells, having all the benefits but none of the drawbacks. Yes!. Follow the steps below to use CENTRE ACROSS SELECTION. Normally a merged cell looks like the screen shot below. The cells B2 to E2 are merged.

So, to get the same effect all we need to do is firstly UNMERGE the cells.

Select the cells.
Home Tab | Alignment Group.
Unmerge cells.

You now have normal cell formatting.

Select the cells that you want to appear ‘merged’. I will select B2 to E2 again.
Home Tab | Alignment Group | Expand Alignment Settings.
Select the Alignment Tab | Text Alignment.
Horizontal | Center Across Selection.
Hit ok.

Now, see that the text ‘appears’ to be centred. You can still click into the individual cells, however. This means we retain all of the worksheet functionality. Result!. Job is done.

So, if you already have merged cells in your Excel workbook, I promised at the start of this tutorial to show you how to convert them to Centre Across Selection. So, let’s do it with a simple Excel VBA macro. For single row merged cells in Excel, this Macro works well. An example of how I use this is for unmerging Excel headers and titles.

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.

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

So, back to fining those merged cells in Excel and converting them to centre across selection.

First, open the Visual Basic Editor. There are two ways to do this.

Either by hitting ALT +F11 or
Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

Here is the full code for the Macro.

Option Explicit
'Step 1. Name your macro
Sub ConvertMergedCellsToCenterAcrossSelection()

'Macro by HowToExcelAtexcel

'Step 2. Declare Variables
Dim rng As Range
Dim mergedrng As Range



'Step 3.Loop through all cells in Used range
For Each rng In ActiveSheet.UsedRange


'Step 4.If the range contains merged cells and single row
       If rng.MergeCells = True And rng.MergeArea.Rows.Count = 1 Then


'Step 5. Set variable for merged range
       Set mergedrng = rng.MergeArea


'Step 6. Unmerge the cells and apply Centre Across Selection
        mergedrng.UnMerge
        mergedrng.HorizontalAlignment = xlCenterAcrossSelection

    End If

Next
'Step 7.End the macro
End Sub

Step 1. Name the Macro.

The first step after inserting a new module is to name the Macro. Type Sub then the macro name. There are a few guidelines on macro names. If you need to review these guidelines, read my blog post here. In this example, I have named the macro ConvertMergedToCentreAcrossSelection. Now, this may seem like a long name for a macro. But, I know exactly what this macro does when I need it again. Note that Excel automatically inserts the End Sub part of the code once I name the macro.

Sub ConvertMergedCellsToCenterAcrossSelection()
End Sub

Step 2. Declare Variables.

The next step in writing this Excel Function is to declare any variables. Declaring variables creates a type of memory container in Excel to store these values. In this macro, I have created two variables. The range of cells in the use range of the worksheet as well as the range of merged cells.

Dim rng As Range
Dim mergedrng As Range

Step 3. Loop Through All Cells In The Used Range Of Cells.

This line of code begins the looping for all cells in the used range. I am using the For Each Loop. The syntax of this looping is

For Each object-name In Range

For Each rng In ActiveSheet.UsedRange

As you can see in the line of code Excel will loop all of the cells in the active worksheets used range of cells. The ojbect is the declared variable rng.

Step 4. Using The IF Then Statement To Choose Merged Cells

Next, this line of code makes use of the IF Then statement . The VBA IF statement is used to allow your code to make choices when it is running. In this example to test if the cells in the range in fact contain merged cells and that that the range is a single row.

If rng.MergeCells = True And rng.MergeArea.Rows.Count = 1 Then

Step 5. Setting The Variable For the Merged Range.

If the If Then statement is true then the variable is set for the merged range.

Set mergedrng = rng.MergeArea

Step 6. Unmerge the cells and apply Centre Across Selection

Step 6 is where the cells are unmerged and the Centre Across Selection is applied. The looping continues until all of the merged cells in the active range are convered to centre across selection.

mergedrng.UnMerge
        mergedrng.HorizontalAlignment = xlCenterAcrossSelection

    End If

Next

Step 7. The Macro Ends.

Once all of the merged cells in the active worksheet used range are converted to centre across selection then the code ends. Excel already has this line of code from the beginning of the macro.

So, finally that is it. The only part left is to test the Excel Macro. Does it work?. Let me know in the comments below if you use this code to unmerged your cells in Excel.

Macro Mondays – Remove Personal Information From Excel Using Some Simple VBA

October 11, 2021 by Barbara —2 Comments

Hi Excellers, welcome again to some more #macromonday fun. Do you need to share your Excel workbooks?. Do you need to hide the personal information attached to those workbooks.? Well, it is easy to remove personal information from Excel workbooks with this simple Excel macro.

Excel automatically stores some personal information by default when you create a new workbook. Excel stores this information within the workbook itself or the document properties (metadata). So, if you plan to share your Excel workbook, it is probably a good idea to remove this personal information from it.

So, in this week’s Macro Monday blog post, let’s look at how to write and run a simple Excel VBA macro. This will to remove any personal information from our workbooks.

Starting The Macro.

When you decide to write an Excel macro, you first need to choose where to store it. You have two choices.

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 remove personal information. I, therefore, will save it in my Personal Macro workbook.

First, open the Visual Basic Editor. There are two ways to do this.

Either by hitting ALT +F11 or
Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

How To Remove Personal Information From Workbooks.

We need to start the process by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Next, type Sub, then the name of your macro. In this example, I have called the macro Remove_Information. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines. This macro is so easy it only really contains one line of code. The full code is below.

Remove Personal Information from Excel Using VBA

Sub Remove_Information
ActiveWorkbook.RemovePersonalInformation = True
End Sub

You can now run the code – Developer Tab – Macros – Select Remove-Information – Hit Ok
OR you can attach a command button to the Macro if you want to
OR you can run the macro from the Immediate Window

Next time your workbook is opened, your personal information will be gone.

I like to remove any personal information before I share or make public any spreadsheets, but it is your choice. It really doesn’t take much to remove this data. I find once it becomes part of your routine, you won’t even think about it.

Don’t forget to sign up for the Excel at Excel Newsletter for three free tips every month.

Macro Mondays – Let’s Compare Absolute And Relative Macro Recording

October 4, 2021 by Barbara —Leave a Comment

Hello, Excellers. Welcome to another #MacroMonday for more Macro and coding fun. Today, let’s look at a handy topic. First, I will show you the difference between recording an Excel macro with an absolute versus a relative reference.

Generally, Excel will record the EXACT references to the selected cells if you use the Macro Recorder in Excel. Here is an example. I have recorded a Macro the turn cells A1:A10 yellow on my Excel worksheet.

The code that has been generated from the Macro Recorder can be seen below.

If I run the Macro, then the cells in A1:A10 will turn yellow as expected.

Adter that, if I then try to re use this Macro on another range of cells, say D1:D5 does it work?. No it does not work.

Only the absolute cells referenced in the macro are affected, as Excel carries out the EXACT instructions. But, by instructing Excel to record the macro with relative referencing, we get a different and way more helpful outcome.

Record A Macro Using Relative Referencing.

Before you begin to record a macro with relative referencing, we need to tell Excel to use relative referencing.

Developer Tab
Select Use Relative References
Record your Macro
Click Use Relative References again to turn off this function

I have recorded the same Macro as in the above example. But, I have used Relative Referencing. Here my code this time.

You can see that the code is slightly different and Excel will keep track of the Relative Reference. This means the macro will affect those cells relative to where your cursor is positioned when you invoke the macro.

Let’s test it then!!!!! It works! wherever I put my cursor the cells 1 to 10 are changed to yellow.

MACRO MONDAYS - ABSOLUTE VS RELATIVE MACRO RECORDING2

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel With Our Excel Online Courses

Concatenate A Range Of Cells With VBA

September 27, 2021 by Barbara

Hi Excellers, and welcome back to another #MacroMonday #Excel blog post in my 2020 Excel and Macro series. This Excel VBA macro walks you through how to use VBA to concatenate a range of cells. This macro will build a large one row string. In this example data set, a list of customer account references are joined, each one separated by a comma.

After the macro runs, the result is a long string which I can query my database with like below. How cool is that?

Let’s work through the coding together!.

What Does The Macro Do?

This procedure will concatenate a range of cells with a comma separator using VBA. Yes, VBA concatenate.

Starting The Macro.

First, open the Visual Basic Editor. There are tow ways to do this.

Either by hitting ALT +F11 or
selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

Before we begin to write any code you need to decide where to store the code. You have a choice.

To store your code either in your Personal Macro Workbook or
Store it in your current workbook.

What’s the difference?. If you save the macro in your Personal Macro workbook, it will be available in any Excel workbooks. However, if you store it in the current workbook, then use is restricted to that workbook. In this instance, I may want to reuse the code, so in this instance, I store it in my Personal Macro Workbook. When you select this option, Excel creates (if it is not already created) the Personal Macro Workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb.

If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.

Creating and Updating Your Personal Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Create A Shortcut To Your Personal Excel Macro Workbook

Preparing To Write The Macro.

We need to start the process by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Next, type Sub, then the name of your macro. In this example, I have called the macro Combine. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines.

Sub Combine()
End Sub

Declaring Variables.

We need to declare two variables for this macro. By doing this, Excel creates memory containers for these values.

Dim rng As Range

Dim i As String

The user-selected range and the string data type are set. By declaring these variables, Excel will save processing time and memory.

Using the For Each Looping Process.

Looping through a collection of cells in a specified range is possibly one of the most common VBA methods. In this macro, I use the For Each process to loop through all cells in the range. First, Excel joins the cell contents with the “&” as well as “,”. Once Excel joins all the cells, the looping ends.

For Each rng In Selection

i = i & rng & ","

Next rng

Ending TheVBA Concatenate Macro.

The macro ends once all looping of cells is complete. The ending is the End Sub code. This last piece of code already exists from the first stage of naming the VBA Maco.

End Sub

That is how to use VBA to concatenate cell contents.

Copy The Code Here.

Sub Combine()
'macro by How To Excel At Excel
Dim rng As Range

Dim i As String

For Each rng In Selection

i = i & rng & ","

Next rng

End Sub

Finally, do you want even more Excel and VBA tips?.

Sign to my Excel Newlstter. I share three FREE Excel tips every month. No spam.

Alternatively bookmark my Macro Monday and Formula Friday pages. I update them every week.

Starting the Macro.

Copy The Code. How To Save An Excel Chart As An Image.

Step 1. Name The Macro.

Step 2. Declare Variables.

Step 3. Set The Location To Export The Chart.

Step 4. Export The Active Chart And Save As An Image.

Step 5. Ending The Macro.

More Excel Tips.

Using Merged Cells.

Reasons Not To Use Merged Cells.

Centre Across Selection. The Alternative To Merged Cells.

Starting The Macro.

Here is the full code for the Macro.

Step 1. Name the Macro.

Step 2. Declare Variables.

Step 3. Loop Through All Cells In The Used Range Of Cells.

Step 4. Using The IF Then Statement To Choose Merged Cells

Step 5. Setting The Variable For the Merged Range.

Step 6. Unmerge the cells and apply Centre Across Selection

Step 7. The Macro Ends.

Starting The Macro.

How To Remove Personal Information From Workbooks.

Record A Macro Using Relative Referencing.

How To Excel At Excel – Macro Mondays Blog Posts.

What Does The Macro Do?

Starting The Macro.

Preparing To Write The Macro.

Declaring Variables.

Using the For Each Looping Process.

Ending TheVBA Concatenate Macro.

Finally, do you want even more Excel and VBA tips?.

Tags

Recommended Excel Resources

SHARE

Additional Info