Saving A Range As A Graphic File

Category: Charts & Graphics / General VBA | [Item URL]

Quite by accident, I discovered something today that I should have known a long time ago: When you copy an Excel range, a graphic image of that range is also stored on the Windows clipboard. As a result, you can paste the clipboard contents into most graphics programs.

It's pretty simple:

  1. Select a cell or range.
  2. Press Ctrl+C to copy.
  3. Activate a graphics program and press Ctrl+V to paste the image.
  4. Then you can save it to the graphics file format of your choice.

I tried this with IrfanView and Adobe Photoshop Elements, and it works great. There are, of course, many screen capture programs available that are more flexible, but this method is quick and easy.

Here's a simple little VBA macro that automates the first three steps:

Sub CopyAsGraphic()
     Const Viewer As String = "C:\Program Files\IrfanView\i_view32.exe"
     Shell Viewer, 1
     Application.SendKeys "^v"
End Sub

Note: If you don't have IrfanView (or if it's in a different directory), you'll need to modify the Viewer constant. By the way, I highly recommend IrfanView as your default image viewer. It's free, fast, and feature-packed.

Start by selecting a range, a chart, a shape -- or anything else that's selectable in Excel. Then execute the CopyAsGraphic macro. IrfanView (or your other program) will appear with the copied data. Then you can do whatever you want with it.

By the way, copying is a what-you-see-is-what-you-get thing. For example, if the selected range contains a chart, the chart will also appear in the image.

A Quick And Dirty Slideshow Macro

Category: Charts & Graphics / General VBA | [Item URL]

A companion file is available: Click here to download

Here's a VBA macro that displays a full-screen slide show, using all of the embedded charts on the active worksheet.

Sub ChartSlideShow()
    Dim Cht As ChartObject
    Dim UserSheet As Worksheet
    Set UserSheet = ActiveSheet
    Application.DisplayFullScreen = True
    Application.DisplayAlerts = False

    For Each Cht In UserSheet.ChartObjects
        Application.ScreenUpdating = False
        ' Delete old chart sheet if it exists
        On Error Resume Next
        On Error GoTo 0
        ' Copy embedded chart and move it
        ActiveChart.Location Where:=xlLocationAsNewSheet, _
        ' Show the chart sheet and prompt for next one
        Application.ScreenUpdating = True
        If MsgBox("OK for next chart, Cancel to stop.", _
          vbQuestion + vbOKCancel) = vbCancel Then Exit For
     Next Cht
     ' Clean up
     On Error Resume Next
     On Error GoTo 0
     Application.DisplayFullScreen = False
     Application.DisplayAlerts = True
End Sub

Just copy and paste the code to a VBA module. Then activate a worksheet that contains embedded charts and execute the ChartSlideShow macro. It makes no changes to your original charts.

Note that you can create text-only charts by deleting the chart in a chart object. Then insert a shape and add some text. You can also display pictures by deleting the chart and inserting a picture.

I tested it in both Excel 2007 and Excel 2003. It works in both versions, but it looks a bit better in Excel 2007.

The order of the slides is determined by the z-order of the charts on the worksheet. You can change the z-order by right-clicking the chart object, and using the 'send forward' or 'send backward' command. In Excel 2007, it's much easier to do this by using the Re-order buttons in the Selection and Visibility pane.

Pasting An Image To A UserForm Control

Category: Charts & Graphics / UserForms | [Item URL]

VBA programmers are familiar with Excel's UserForms. The UserForm object, as well as many of the ActiveX controls that are placed on a UserForm, can display an image -- the object's Picture property.

How to set the Picture property

If you believe Excel's online help, you'll think that there are two ways to set the Picture property for an object:

  • At design time: Browse for an image file, by clicking the "..." button in the Properties box
  • At run-time: Use VBA's LoadPicture function to retrieve an image file.

But there's another way. And it doesn't even require that the image is stored in a file. Here's the trick:

  1. Locate the image that you want to use, and copy it to the Clipboard.
  2. Activate the VB Editor and select the object that will contain the image (i.e., a UserForm, or a control that has a Picture property).
  3. Make sure the Properties window is displayed (press F4 it it's not).
  4. Click the Picture item. By default, this displays "(None)" as in the figure above.
  5. Press Ctrl+V to paste the image on the Clipboard to the control.


Importantly, the picture that you paste to a control need not be stored in a file. Any picture that can be copied to the Clipboard can be pasted to an object's Picture property. And this includes charts stored in a worksheet. The trick here is to copy the chart as a picture: Click the chart, press Shift, and choose Copy Picture from Excel's Edit menu. Once copied, you can paste the picture of the chart to your UserForm control.

When pasting a chart, it's important to understand that the picture is not linked to the original chart. So if the chart changes, the picture will not change.

Interactive Chart With No Macros

Category: Charts & Graphics | [Item URL]

A companion file is available: Click here to download

This tip presents a highly interactive workbook, without a single macro. The workbook allows you to select two U.S. cities, and then displays a comparison chart for the following variables.

  • Temperature
  • Precipitation
  • Percent Sunshine
  • Wind

You can download the workbook and examine how it's done. Hint: It's done with named ranges and worksheet controls.

Creating A Splash Screen For An Excel Workbook

Category: Charts & Graphics / General VBA | [Item URL]

This tip describes how to create a splash screen for a Excel workbook. A splash screen appears when the workbook is opened and disappears automatically after a specific time has elapsed.

Setting it up

Follow these instructions to create a splash screen for your project.

  1. Create your workbook as usual.
  2. Activate the Visual Basic Editor and insert a new UserForm into the project. The code here assumes this form is named UserForm1.
  3. Place any controls you like on UserForm1. For example, you may want to insert an Image control that has your company's logo. Also, you may want to set the UserForm's Caption property to an empty string.
  4. Insert the following subroutine into the code module for the ThisWorkbook object:
    Private Sub Workbook_Open()
    End Sub
  5. Insert the following subroutine into the code module for UserForm1:
    Private Sub UserForm_Activate()
        Application.OnTime Now + TimeValue("00:00:05"), "KillTheForm"
    End Sub  
  6. Insert the following subroutine into a normal VBA module:
    Private Sub KillTheForm()
        Unload UserForm1
    End Sub

How it works

When the workbook is opened, the Workbook_Open subroutine is executed. This subroutine displays the UserForm. When the UserForm is displayed, it's Activate event occurs - which triggers the UserForm_Activate subroutine. This subroutine uses the OnTime method of the Application object to execute a subroutine (named KillTheForm) at a particular time. In this case, the time is five seconds from the current time (change this interval by modifying the argument for the TimeValue function). The KillTheForm subroutine simply unloads the UserForm.

A Class Module To Manipulate A Chart Series

Category: Charts & Graphics / General VBA | [Item URL]

A companion file is available: Click here to download

Excel's object model has a serious flaw: There is no direct way to to use VBA to determine the ranges used in a chart. This tip contains a useful class module that can simplify the task of manipulating chart's using VBA.

Background Information

The Series object is contained in a Chart object. The SeriesCollection is a collection of Series objects for a particular Chart object. If a chart plots two data series it will have two Series objects. You can refer to a particular Series object by its index number. The expression below, for example, creates an object variable that represents the first Series object in the active chart:

Set MySeries = ActiveChart.SeriesCollection(1)

A Series object has many properties, but I'll list three that seem relevant to this discussion:

  • Formula property: Returns or sets the SERIES formula for the Series. When you select a series in a chart, its SERIES formula is displayed in the formula bar. The Formula property returns this formula as a string.
  • Values property: Returns or sets a collection of all the values in the series. This can be a range on a worksheet or an array of constant values, but not a combination of both.
  • XValues property: Returns or sets an array of x values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values -- but it can't be a combination of both.

If your VBA code needs to determine the data range used by a particular chart series, it's obvious that the Values property of the Series object is just the ticket. And, you can use the XValues property to get the range that contains the x values (or category labels). In theory, that certainly seems correct -- but in practice, it doesn't work. You'll find that he XValues and Values properties return a variant array. Unfortunately, there is no direct way to get a Range object for a Series object.

Note: When you set the Values property for a Series object, you can specify a Range object or an array. But when you read this property, it is always an array. Go figure.

About a Chart's SERIES formula

Every data series in a chart has a SERIES formula that determines the data used in the series. Here's an example of a SERIES formula for a chart series:


A SERIES formula is comprised of four arguments:

  • Series Name. Can be a cell reference or a literal string. Optional.
  • XValues. Can be a range reference (including a non-contiguous range reference) or an array. Optional.
  • Values. Can be a range reference (including a non-contiguous range reference) or an array.
  • Plot Order. Must be an integer.

The ChartSeries calls module, described below, essentially parses and analyzes a chart's SERIES formula.

The ChartSeries Class Module

I created a class module named ChartSeries. When this class module is included in a workbook, your VBA code can create a new "ChartSeries" object and manipulate the following properties of this object:

  • Chart (read/write)
  • ChartSeries (read/write)
  • SeriesName (read/write)
  • XValues (read/write)
  • Values (read/write)
  • PlotOrder (read/write)
  • SeriesNameType (read-only)
  • XValuesType (read-only)
  • ValuesType (read-only)
  • PlotOrderType (read-only)

Example Usage

The simple procedure below demonstrate how to use the ChartSeries class. It starts by creating a new object called ChartSeries. It sets the Chart property to an embedded chart, and sets the ChartSeries property to 1. It then uses the XValuesType property to determine the "type" of the XValues in the chart. If the chart uses a range, it displays the address of the range. If the chart uses something other than a range (that is, a literal array), it displays the array.

Sub ExampleUsage()
    Dim MySeries As New ChartSeries
    With MySeries
        Chart = Sheets(1).ChartObjects(1).Chart
        ChartSeries = 1
         If .XValuesType = "Range" Then
            MsgBox .XValues.Address
          MsgBox .XValues
         End If
    End With
End Sub

Chart Trendline Formulas

Category: Formulas / Charts & Graphics | [Item URL]

When you add a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This tip describes how to create formulas that generate the trendline coefficients. You can then use these formulas to calculate predicted y values for give values of x.

These equations assume that your sheet has two named ranges: x and y.

Linear Trendline

Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic Trendline

Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline

Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)

Exponential Trendline

Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)

2nd Order Polynomial Trendline

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline

Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Higher Order Polynomial Trendline

Notice the pattern in the two preceding sets of formulas.

Removing Lines From A Surface Chart

Category: Charts & Graphics / General VBA | [Item URL]

By default, Excel's surface charts display black lines between each color gradient. It's possible to remove those lines, but Excel makes you jump through quite a few hoops to do so.

Each line must be removed separately! Here's how to do it:

  1. Click the chart legend to select it
  2. Click the first legend key (that is the small colored square)
  3. Right-click and choose Format Legend Key
  4. In the Format Legend Key dialog box, click the Patterns tab
  5. Select None for the Border option, and click OK

Repeat those steps for every series in your chart.

As with most tedious operations in Excel, you can automate this with a VBA macro:

Sub RemoveSurfaceChartLines()
    Dim LE As LegendEntry
    If ActiveChart Is Nothing Then Exit Sub
    If ActiveChart.ChartType <> xlSurface Then Exit Sub
    If MsgBox("Remove lines from surface chart?", vbYesNo) = vbYes Then
        Application.ScreenUpdating = False
        ActiveChart.HasLegend = True
        For Each LE In ActiveChart.Legend.LegendEntries
            LE.LegendKey.Border.LineStyle = xlNone
        Next LE
        ActiveChart.ChartGroups(1).Has3DShading = True
    End If
End Sub

To use this macro, copy it and paste it to a VBA module. Then, select a surface chart and press Alt+F8 to display the Macro dialog box. Execute the RemoveSurfaceChartLines macro, and your chart will be transformed, as shown below. Note that the final statement in this procedure turns on the chart's 3D shading option (which gives the chart a much nicer appearance.

The number of colored gradients in a surface chart is determined by the Major Unit setting for the vertical axis. Double-click the vertical axis to display the Format Axis dialog box. Select the Scale tab and change the value for the Major Unit. The larger the number, the fewer the colors.

Creating A Non-Graphic Chart Directly In A Range

Category: Charts & Graphics | [Item URL]

This tip describes how to create a non-graphic chart. It uses formulas to display crude "bars" directly in a range of cells.

The figure below shows an example of what you can produce with his technique.

The formulas in columns E and G graphically depict monthly budget variances by displaying a series of characters in the Wingdings font. The number of characters displayed is determined by an IF function.

To re-create this chart in Excel, enter the data shown in columns A through D, and then enter the following formulas:

E2: =IF(D2<0,REPT("n",-ROUND(D2*100,0)),"") 
F2: =A2
G2: =IF(D2>0,REPT("n",-ROUND(D2*-100,0)),"")

Assign the Wingdings font to cells E2 and G2, and then copy the formulas down the columns to accommodate all the data. Right-align the text in column E, and adjust any other formatting as you like.

Depending on the numerical range of your data, you may need to change the scaling. Experiment by replacing the '100' value in the formulas. You can, of course, substitute any character you like for the "n" in the formulas to produce a different character in the chart.

Creating A Linked Picture Of A Range

Category: Charts & Graphics | [Item URL]

Many users overlook a useful Excel feature that can create a live "snapshot" of a range and placing it anywhere you like.

To use this feature:

  1. Select a range of cells

  2. Choose Edit, Copy

  3. Hold down the Shift key, and then choose Edit, Paste Picture Link.

The result is an image of the selected range that will reflect any subsequent changes to the source.

This technique is great for printing noncontiguous ranges on a single page. After creating a series of linked pictures of ranges, set them to print on one page.

Note: The Paste Picture Link command is a hidden command, and it appears on the Edit menu only if you press the Shift key.

Page 1 of 2 pages
[Next page]

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy