Creating a standard bar chart manually. To create a standard bar chart manually, select Chart on the Insert menu to launch the Excel Chart Wizard. The Chart Wizard first lets you choose the kind of chart you want to create. Because you want to create a column chart for this example, select Column as the Chart type and Clustered Columns as the chart subtype. Clustered columns compare values across categories (more about categories shortly). Click Next to proceed to the next screen, and select the Data Range and Series. The Data Range is the range of cells containing data that will be transformed into a chart. The range for this example is
='Bar Chart'!$A$1:$D$4
which translates to cells A1 through D4 in the Bar Chart worksheet. The wizard is pretty good at auto-selecting the data, but if you want to manually select the range, simply select the entire data range by using your mouse. (Click the first cell of the range you want to select and hold the left mouse button down as you drag to the last cell of the range you want.) For the Series selection, you can choose either rows or columns. In this example, selecting columns for the chart means that the months (January, February, and March) will be the series, which in turn makes the salespeople (John, Mae, and Al) the categories. Selecting rows as the series would do just the opposite: John, Mae, and Al would become the series, and January, February, and March would become the categories.
Because you want to see the months as a series, select the Columns radio button and click Next. Although you can define titles for the chart, categories, and y axis (in this example, the monthly sales values), you don't want titles for this chart, so click Next. The wizard then lets you determine where the Chart object should go. Because you want the chart inserted in the same worksheet as the data, select the Place chart as object in radio button and select Bar Chart from the adjacent drop-down list. When you click Finish, the new chart will appear in your Bar Chart worksheet.
Creating a standard bar chart programmatically. Creating a standard bar chart programmatically for the first time with VBScript could be quite daunting if you had to start from scratch by digging into the Excel object model or searching through the MSDN Web site. But as it turns out, you can easily complete the groundwork for this chart-creation task.
By using Excel's macro recorder, you can produce virtually all of the code you'll need to programmatically produce Excel charts-or almost any Excel entity you want to produce. (For an introduction to the process of recording a macro and converting Excel VBA macros into working VBScript code, see the section "Getting a Recorded Macro into VBScript Code," in Part 1.)
To record a macro for making this chart, select Macro on the Tools menu, choose Record New Macro, give the macro a name, and click OK. Then create the chart as described in the "Creating a standard bar chart manually" section. When you're done, click Stop Recording, which appears when you use your mouse to hover over a square blue button on the macro recording toolbar.
To view and copy the VBA code that the recorded macro created, open the macro editor by selecting Macro on the Tools menu and choosing Macros. In the Macros dialog box, select the macro you just created and click Edit. What you'll see is VBA code that can programmatically produce the same columnar chart that you created manually. But because VBA code differs slightly from VBScript code, you must transform one code into the other.
First, copy the VBA macro code, omitting the Sub and End Sub statements and the commented code (unless you think the comments might prove beneficial). Here's an example of what the recorded macro code looks like, after those omissions:
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Bar Chart").Range("A1:D4"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Bar Chart"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
When you examine the code and recall the steps you took to create the chart, you'll see that the lines of code are relatively self-explanatory. The first line of code adds a chart. The second line of code defines the chart type. The third line references the data source and row or column. The fourth line indicates where you want the chart to go. The fifth line sets up title choices. Because there are no chart, category, or value titles, those properties are set to False. All you have to do now is make a few changes to convert the VBA code to VBScript code.
As you might recall from Part 1, you need to precede Excel elements such as Chart and ActiveChart with the name of your Excel application object variable followed by a period. In this case, you'd use XL. That rule doesn't apply, however, if Range is used as a property of another element, as is the case in the following line of code:
XL.ActiveChart.SetSourceData XL.Sheets("Bar Chart").Range("A1:D4"), xlColumns
You also need to remove words that end with a colon and equal sign, such as Source:=, Where:=, PlotBy:=, and Name:=. One reference in the VBA macro code that might not seem obvious at first is the PlotBy:= argument. However, as you might have guessed by its reference to xlColumns, it sets the series to either rows or columns. This element of the chart is also commonly known in Excel terminology as the legend. With those instructions in mind, the following code shows you what the converted code would look like:
XL.Charts.Add
XL.ActiveChart.ChartType = xlColumnClustered
XL.ActiveChart.SetSourceData XL.Sheets("Bar Chart").Range("A1:D4"), xlColumns
XL.ActiveChart.Location xlLocationAsObject, "Bar Chart"
With XL.ActiveChart
.HasTitle = False
.Axes(xlCategory).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
You also need to look up the values for the Excel constants and include these statements in your VBScript code:
Const xlColumnClustered = 51
Const xlColumns = 2
Const xlLocationAsObject = 2
Const xlCategory = 1
Const xlValue = 2
Const xlRows = 1
Creating a custom bar chart programmatically. In Excel, you can create custom charts. The HTA's XL Charts option demonstrates how to programmatically create a built-in custom 3D bar chart called Columns with Depth. I won't discuss the code for this chart in detail because it's similar to the code for the standard bar chart. However, the custom bar chart code does have three variations worth mentioning. First, as the code at callout B in Listing 1 shows, you use the line
XL.ActiveChart.ApplyCustomType xlBuiltIn, "Columns with Depth"
to create this type of chart. Second, the custom bar chart uses rows rather than months as the series. The months appear as categories. Finally, the custom bar chart includes titles for the chart, sales quarters, and dollar ranges. The code then adds the custom bar chart to a new worksheet named "Columns with Depth."
XL Hyperlinks
The XL Hyperlinks demo code in Listing 2 shows you how to add hyperlinks to your spreadsheets. Hyperlinks are easy to create, and they add a nice touch to spreadsheets-especially if you want to refer to associated materials or related data.
The code for creating a hyperlink is pretty straightforward. First, you populate a cell with some descriptive text, such as Link to Microsoft Web Site or Link to Budget Data. Then, you select that cell and apply the ActiveSheet.Hyperlinks.Add method. This method takes four arguments. The first argument indicates the selected cell where the hyperlink will be placed. The second argument is the address the link will access. The third argument points to a worksheet location, and the last argument defines a tooltip. This tooltip is what appears if you use your mouse to briefly hover over the object.
To create a link to a Web site, you simply provide the Web site URL as the second argument and leave the third argument blank, as the code at callout A in Listing 2 shows. To create a link to a worksheet cell somewhere within your spreadsheet, you provide an empty set of double quotes as the second argument and provide the reference to the worksheet cell enclosed in double quotes as the third argument, as the code at callout B in Listing 2 shows. Cells(1, 2) refers to the cell at row 1 column 2, which is technically the same as cell B1. You set cell values by referring to their row and column, and you select a cell or range of cells by referring to columns and rows.
Chart Creation and Hyperlinking
The in-depth discussion of the XLCharts subroutine and the brief coverage of the XLHyperlinks subroutine should help you create useful reports and make them more effective by linking to associated information. And, you can do much more with the dynamic duo of VBScript scripting and Excel, as indicated by the many options in ExcelerateYourVBScripts.hta. Next month, I'll discuss a couple more of those options, including the Concatenate and Find Duplicates option.
End of Article
Prev. page
1
[2]
next page -->
