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"
     Selection.Copy
     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
        Charts("ChartTemp").Delete
        On Error GoTo 0
        ' Copy embedded chart and move it
        UserSheet.Activate
        Cht.Chart.ChartArea.Copy
        ActiveSheet.Paste
        ActiveChart.Location Where:=xlLocationAsNewSheet, _
           Name:="ChartTemp"
        ' 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
     Charts("ChartTemp").Delete
     On Error GoTo 0
     Application.DisplayFullScreen = False
     Application.DisplayAlerts = True
     UserSheet.Activate
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.

Excel 2007 Upgrade FAQ: Charts And Graphics

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

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q: Double-clicking on a chart element doesn't display the Format dialog box.

A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.

Q. I find that it's very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?

A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it's always visible.

Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?

A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.

Q: What's a dialog box launcher?

A: It's the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.

Q: I'm working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title 'Format Shape,' and it has the focus. So the arrow keys move within the dialog box, not the worksheet.

A: Annoying, isn't it? When you're finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.

Q: In a chart, how do I control plotting empty cells and plotting hidden cells?

A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells. 

Q. What happened to the chart fill patterns?

A. The fill patterns are no longer available in the user interface. However, they can be applied using a VBA macro.

Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don't work in Excel 2007.

A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.

Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.

A. Right-click your QAT and choose Customize. Choose 'All Commands' and then select 'Change Shape'. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.

Q. A cell comment is a shape. Why can't I use the ribbon commands to format it?

A. Comment formatting is done via the Format Comment dialog box. Right-click the comment's border and choose Format Comment. Oddly, the color options available are not from the document theme.

Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?

A: You don't. You can, however specify the Top and Left properties by using VBA.

Q: When I right-click a Shape, I see an option to "Set as Default Shape." This command seems to have no effect.

A: When you choose that command, Excel uses the *formatting* that you've applied to the shape as the default (fill, outline, effects). The command should probably read "Set as Default Shape Formatting."

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.

More

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()
       UserForm1.Show
   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.

Creating A Clickable Image Map

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

A companion file is available: Click here to download

You might have noticed that some Web sites feature an image map to help navigate the site. An image map is a single image, but it's sensitive to the location at which you click it. This tip demonstrates how to create an image map.

To create an image map, you must be familiar with the MouseOver event. This event is triggered whenever the mouse pointer moves over a particular control. In this case, the control is an Image control. If you're not familiar with the MouseOver event, consult the online help for details. Basically, this event lets you determine the horizontal and vertical coordinates of the mouse pointer, relative to the control. The image I use in the example is shown below.

Setting up the image map simply involves monitoring the mouse position, and taking the appropriate action if the control is clicked. The VBA code is shown below. Every mouse movement over the Image control triggers the MouseMove event and executes the Image1_MouseMove subroutine. The mouse coordinates are passed to the subroutine via the X and Y arguments. A series of Select Case statements determines which flag is under the mouse pointer, and stores the country name in the Country variable. I also display the country in the status bar. Obviously, it will take some work to figure out the coordinates, and you'll probably want to use an image in which the "subimages" are rectangular.

When the image is clicked, the Image1_Click subroutine is executed. This subroutine simply activates a worksheet, the name of which is stored in the Country variable. The worksheets are named for the countries represented by the flags.

VBA Code

In this example, the Image control is placed on a worksheet named Menu. Therefore, this code resides in the code module for the Menu sheet.

Public Country As String

Private Sub Image1_MouseMove(ByVal Button As Integer, _
  ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Select Case Y 'Vertical coordinate
        Case Is < 2 'Off the map
            Country = ""
        Case Is < 47
            Select Case X 'Horizontal coordinate
                Case Is < 2: Country = ""
                Case Is < 78: Country = "United States"

                Case Is < 152: Country = "Canada"
                Case Else: Country = "" 'Off the map
            End Select
        Case Is < 87
            Select Case X 'Horizontal coordinate
                Case Is < 2: Country = ""
                Case Is < 40: Country = ""

                Case Is < 112: Country = "Cuba"
                Case Else: Country = "" 'Off the map
            End Select
        Case Is < 133
            Select Case X 'Horizontal coordinate
                Case Is < 2: Country = ""
                Case Is < 78: Country = "Mexico"

                Case Is < 152: Country = "Puerto Rico"
                Case Else: Country = "" 'Off the map
            End Select
        Case Else 'Off the map
            Country = ""
    End Select
'   The statement below was used while figuring out the coordinates
'   Application.StatusBar = Country & " " & X & " " & Y
    Application.StatusBar = Country
End Sub

Private Sub Image1_Click()
    If Country <> "" Then Sheets(Country).Activate
'   Reset the status bar
    Application.StatusBar = False
End Sub

NOTE: Another way to create an image map is to insert transparent objects over the image, and create event-handler subroutines for the objects.

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:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)

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
        Else
          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.

SURFACE CHART TIP:
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.

Page 1 of 3 pages
[Next page]