Function Reference

_XLChart_ChartCreate

Create a chart in Excel on the specified worksheet or on a separate chartsheet.

#Include <ExcelChart.au3>
_XLChart_ChartCreate($oExcel, $vWorksheet, $iChartType, $sSizeByCells, $sChartName, $sXValueRange, $vDataRange, $vDataName[, $bShowLegend = True[, $sTitle = ""[, $sXTitle = ""[, $sYTitle = ""[, $sZTitle = ""[, $bShowDatatable = False[, $bScreenUpdate = False]]]]]]])

 

Parameters

$oExcel Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
$vWorksheet Worksheet number or name where the chart should be created (eg. 1)
$iChartType Chart type number to be used (see ExcelChartConstants.au3 for details, eg. $xl3DColumn)
$sSizeByCells The left-hand top and right-hand bottom corner of the chart (eg. "B2:K24").
0 = create the chart on a separate chartsheet. This sheet will be inserted before the sheet specified by $vWorksheet
$sChartName Name of the chart or the chartsheet (if $sSizeByCells = 0)
$sXValueRange Category (X) axis label range always a single range (eg. "=Sheet1!R2C1:R6C1")
$vDataRange The values range. Either a single range or an one-dimensional one based array
$vDataName Header name of the range. Either a single range or an one-dimensional one based array
$bShowLegend Optional: Set to True to show the legend or False to hide it (default = True)
$sTitle Optional: Chart Title. If empty, no title will be displayed (default = "")
$sXTitle Optional: X Axis title (default = "")
$sYTitle Optional: Y Axis title (default = "")
$sZTitle Optional: Y Axis title (default = "")
$bShowDatatable Optional: Set to True to show the data table or False to hide it (default = False)
$bScreenUpdate Optional: Set to False to disable screen updating during chart creation to enhance performance (default = False)

 

Return Value

Success: Object identifier of the created chart, sets @extended to:
    0 - No COM error handler has been initialized for this UDF because another COM error handler was already active
    1 - A COM error handler has been initialized for this UDF
Failure: Returns 0 and sets @error:
    1 - Unable to create custom error handler. See @extended for details (error returned by ObjEvent)
    2 - Both parameters $vDataRange & $vDataName must be of the same type
    3 - Unable to access specified Excel sheet. See @extended for details
    4 - Unable to create range for the chart. Parameter $sSizeByCells is invalid. See @extended for details
    5 - Unable to create new chart. See @extended for details

 

Remarks

The COM error handler will be initialized only if there doesn't already exist another error handler.

Data Tables are available for line, column, area, and barchart types only.

 

Related

 

Example


#AutoIt3Wrapper_UseX64=n
#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include-once
#include <ExcelChart.au3>

; *****************************************************************************
; Create example environment
; *****************************************************************************
Global $aExcel = _XLChart_Example()
If @error = 2 Then Exit MsgBox(16, "Excel Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).")
If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example on line " & @ScriptLineNumber)

; *****************************************************************************
; Example 1
; 3D COLUMN: Without legend, with data table
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
Global $Graph_position = "=_XLChart_Example!A8:F26"
Global $XValueRange = "=_XLChart_Example!R2C1:R6C1"
Global $DataRange[3] = [2, "=_XLChart_Example!R2C2:R6C2", "=_XLChart_Example!R2C3:R6C3"]
Global $DataName[3] = [2, "=_XLChart_Example!B1", "=_XLChart_Example!C1"]
Global $oChart = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DColumn, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "Location", "Quantity")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)

; *****************************************************************************
; Example 2
; 3D PYRAMID COLUMN: With legend
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
$Graph_position = "H8:M26"
$oChart = _XLChart_ChartCreate($aExcel[0], 1, $xlPyramidCol, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "", "Quantity")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)

; *****************************************************************************
; Example 3
; 3D STACKED AREA: Without legend
; *****************************************************************************
$Graph_position = "A28:F46"
$oChart = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DAreaStacked, $Graph_position, "", $XValueRange, $DataRange, $DataName, True, "Sales", "Date")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate'! on line " & @ScriptLineNumber)

; *****************************************************************************
; Example 4
; OPEN-HIGH-LOW-CLOSE Chart
; *****************************************************************************
_ExcelWriteCell($aExcel[0], "Date", 28, 14)
_ExcelWriteCell($aExcel[0], "02/24/2012", 29, 14)
_ExcelWriteCell($aExcel[0], "02/23/2012", 30, 14)
_ExcelWriteCell($aExcel[0], "02/22/2012", 31, 14)
_ExcelWriteCell($aExcel[0], "02/21/2012", 32, 14)
_ExcelWriteCell($aExcel[0], "02/17/2012", 33, 14)
$aExcel[0].ActiveSheet.Columns(14).AutoFit
_ExcelWriteCell($aExcel[0], "Open", 28, 15)
_ExcelWriteCell($aExcel[0], "68.682", 29, 15)
_ExcelWriteCell($aExcel[0], "69.342", 30, 15)
_ExcelWriteCell($aExcel[0], "69.203", 31, 15)
_ExcelWriteCell($aExcel[0], "69.204", 32, 15)
_ExcelWriteCell($aExcel[0], "69.485", 33, 15)
$aExcel[0].ActiveSheet.Columns(15).AutoFit
_ExcelWriteCell($aExcel[0], "High", 28, 16)
_ExcelWriteCell($aExcel[0], "68.826", 29, 16)
_ExcelWriteCell($aExcel[0], "69.375", 30, 16)
_ExcelWriteCell($aExcel[0], "69.463", 31, 16)
_ExcelWriteCell($aExcel[0], "69.364", 32, 16)
_ExcelWriteCell($aExcel[0], "69.598", 33, 16)
$aExcel[0].ActiveSheet.Columns(16).AutoFit
_ExcelWriteCell($aExcel[0], "Low", 28, 17)
_ExcelWriteCell($aExcel[0], "68.263", 29, 17)
_ExcelWriteCell($aExcel[0], "68.654", 30, 17)
_ExcelWriteCell($aExcel[0], "69.085", 31, 17)
_ExcelWriteCell($aExcel[0], "68.891", 32, 17)
_ExcelWriteCell($aExcel[0], "69.251", 33, 17)
$aExcel[0].ActiveSheet.Columns(17).AutoFit
_ExcelWriteCell($aExcel[0], "Close", 28, 18)
_ExcelWriteCell($aExcel[0], "68.403", 29, 18)
_ExcelWriteCell($aExcel[0], "68.882", 30, 18)
_ExcelWriteCell($aExcel[0], "69.307", 31, 18)
_ExcelWriteCell($aExcel[0], "68.135", 32, 18)
_ExcelWriteCell($aExcel[0], "69.450", 33, 18)
$aExcel[0].ActiveSheet.Columns(18).AutoFit
$Graph_position = "H28:M46"
$XValueRange = "=_XLChart_Example!29R2C14:R33C14"
Global $DataRange[5] = [4, "=_XLChart_Example!R29C15:R33C15", "=_XLChart_Example!R29C16:R33C16", "=_XLChart_Example!R29C17:R33C17", "=_XLChart_Example!R29C18:R33C18"]
Global $DataName[5] = [4, "=_XLChart_Example!O1", "=_XLChart_Example!P1", "=_XLChart_Example!Q1", "=_XLChart_Example!R1"]
Global $oChart1 = _XLChart_ChartCreate($aExcel[0], 1, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName, Default, "Open-High-Low-Close Chart")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate'! on line " & @ScriptLineNumber)
; Format chart
_XLChart_FillSet($oChart1.Chartgroups(1).UpBars, -4)    ; Set the UpBars to green
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_FillSet($oChart1.Chartgroups(1).DownBars, -3)  ; Set the DownBars to red
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_ColumnGroupSet($oChart1.ChartGroups(1), 250)   ; Make the bars smaller (by making the gap between the bars bigger)
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)

; *****************************************************************************
; Example 5
; 3D EXPLODED PIE: With legend. Create the chart on a separate chart sheet
; *****************************************************************************
MsgBox(16, "Excel Chart Example Script", "Now creating a pie chart on a separate chart sheet!" & @CRLF & "Please press enter!")
$XValueRange = "=_XLChart_Example!R2C1:R6C1"
$DataRange = "=_XLChart_Example!R2C2:R6C2"
$DataName = "=_XLChart_Example!B1"
Global $Title = _ExcelReadCell($aExcel[0], "B1")
$oChart = _XLChart_ChartCreate($aExcel[0], 1, $xl3DPieExploded, 0, "Example Chart", $XValueRange, $DataRange, $DataName, True, $Title)
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)