orbit

Using the Watch Window to Discover Object Settings in Excel 2007:-

It is frustrating that the macro recorder does not record certain actions when you’re working with charts. Actually, there are two levels of frustration. First, the macro recorder does not record the action of creating SmartArt graphics because Microsoft made a conscious decision not to allow you to create SmartArt using VBA. Second, when you are using the Format ribbon, the macro recorder does nothing; however, you can control all the actions with the Format ribbon by using VBA.

Without the macro recorder, it becomes very difficult to learn aspects of objects, properties, and methods, those responded to various actions in the Excel interface.

In case you need to use a property that is not available, there is a way to be able to explore the properties for certain chart elements. The following is an example in which the macro defines a Chart object variable and a ChartGroup object variable and then stops:

Sub ExploreChartElements()

Dim cht As Chart

Dim chtg As ChartGroup

Dim ser As Series

Set cht = ActiveChart

Set chtg = cht.ChartGroups(1)

Set ser = cht.SeriesCollection(1)

Stop

End Sub

The Stop command in the macro is key to the success of this technique. Excel enters Break mode when it encounters the Stop code. This allows you to examine the object variables while they are still in scope.

You follow these steps to discover new chart properties:

1. Enter the preceding macro in your workbook.

2. Create a chart.

3. Select the chart.

4. Run the macro. VBA stops and highlights the Stop line in yellow. You are now in Break mode.

5. Right-click the ser object variable and choose Add Watch. Click OK in the Add Watch dialog box. Excel displays a new Watch window at the bottom of the VBA editor. This window displays a single line with a pair of eyeglasses, a plus sign, and the name of the variable, as shown in the Figure (given below).



(Initially, the watched variable shows a single, useless line.)

6. Click the plus sign next to the watch. A list of many properties for the series opens. One property is the Format property. This is where all of the Format ribbon settings are stored.

7. Click the plus sign next to the Format entry. It expands to show the settings for Fill, Glow, Line, and so on.

8. Click the plus sign next to the Fill entry. You see many settings that define the fill used in Series 1. The Gradient Degree setting is highlighted in the Figure (given below). You can see that Gradient Degree is a property of the Fill property, and Fill is a property of the Format property. From this, you can ascertain that the proper code would be this:

ser.Format.Fill.GradientDegree = 0.8825



(After browsing through the Watch window, you can locate a property without the macro recorder.)

This isn’t exactly as easy as using the macro recorder to examine objects, properties, and methods, but it makes it possible to figure out how to write code.

The Watch window is “somewhat” live. With a few steps, you can change the chart formatting in the Excel user interface and then return to the VBA Editor to discover the new settings:

1. While in Break mode, switch back to Excel using Alt+Tab or by clicking on Excel in the taskbar.

2. Make some changes to the active chart in Excel. Make sure not to deactivate the chart.

3. Switch back to the VBA Editor.

4. There is a yellow arrow to the left of the Stop line in your code window. Grab this arrow and drag upward to point to the line that set up your watched variable. In the current example, you are watching the ser variable, so you just have to move up one line, to the Set ser line.

5. Press the F8 key to rerun the line highlighted in yellow. The Watch window updates to show the settings you made in step 2.

When you are done exploring, click the Reset button in the VBA toolbar (the square dot located near the Run menu).