Hello, Excellers. Time for some more #MacroMonday #Excel fun. I want to share another useful Excel macro today that will convert a range of cells to an image. So, if you need to send an image of some of your Excel workbook to a colleague to put as an image into a presentation, this small bit of code will do all of the hard work for you.
Starting The VBA Macro.
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 Is The Difference In These Locations?.
Well, if you save the code in your Personal Macro workbook it will be available in any Excel workbooks. 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 I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very useful macro which can be used over and over again. I therefore still want to save it in my personal macro workbook.
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
First, you will need to 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.
Preparing To Write The Code.
Next, I need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.
In this example, I have called it RangeToImage. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines of code.
[stextbox id=’info’]
Sub RangeToImage()
End Sub [/stextbox]
The Application.CutCopy Mode.
The next line of code uses the Application.CutCopy mode. This is an application level property that indicates whether a Microsoft Office program (in this case Excel) is in Cut or Copy Mode. While in copy (or cut) mode, Excel has something stored in it’s clipboard which can be pasted.
By setting this to False we essentially clear the clipboard. If you’ve copied an Excel range, running this line of code will remove the animation around the copied cell, which we do not want.
[stextbox id=’info’]
Application.CutCopyMode = False
[/stextbox]
Copy The Range And Paste.
The next two lines of code simply copy our selected range and past it back into the worksheet as a picture.
[stextbox id=’info’]
Selection.Copy
ActiveSheet.Pictures.Paste.Select
[/stextbox]
Ending The Macro.
Once the Excel has completed process of converting a range to an image, the the code ends. The End Sub bit of code signifies this. This was already input by default by Excel when we began writing the macro.
[stextbox id=’info’]
End Sub
[/stextbox]
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.