Excel Chart UDF

[water]

I'm going to create an UDF which will let you create/manipulate Excel Charts.

It will consist of one function to create a simple chart (you just have to specify the data area, the type of the graph to draw, title, position of the chart etc.) and a lot of functions to create/modify all aspects of the chart.

Anyone interested?

Edit: We have released the UDF

Edited December 12, 2011 by water

[GreenCan]

Water,

I would be interested indeed.

I have enhanced the example of mattschinkel and I believe this one will work.

I didn't test it in Office 2010 yet

There is still a lot to do however, and not the least in the error handling.

I hope this is a good starting point for an Excel chart UDF.

I also worked out an Excel Chart Type Constants (MSOfficeExcelChartTypeConstants.au3), herewith attached:

expandcollapse popup

#cs
MSOffice Excel Chart Type Constants
 
REFERENCE
  1.MSDN Library: Excel 2007 Developer Reference | XlChartType Enumeration
  2.MSDN Library: Microsoft Graph Visual Basic Reference | Microsoft Graph Constants
  http://msdn.microsoft.com/en-us/library/aa221100%28v=office.11%29.aspx
 
by: GreenCan
#ce
 
; AREA
const $xl3DArea=-4098   ; 3D AREA
const $xl3DAreaStacked=78   ; 3D STACKED AREA
const $xl3DAreaStacked100=79   ; 3D 100% STACKED AREA
const $xlArea=1   ; AREA
const $xlAreaStacked=76   ; STACKED AREA
const $xxlAreaStacked100=77   ; 100% STACKED AREA
; BAR
Const $xl3DBarClustered=60   ; 3D CLUSTERED BAR
Const $xl3DBarStacked=61   ; 3D STACKED BAR
Const $xl3DBarStacked100=62   ; 3D 100% STACKED BAR
Const $xlBarClustered=57   ; CLUSTERED BAR
Const $xlBarStacked=58   ; STACKED BAR
Const $xlBarStacked100=59   ; 100% STACKED BAR
Const $xlConeBarClustered=102   ; CLUSTERED CONE BAR
Const $xlConeBarStacked=103   ; STACKED CONE BAR
Const $xlConeBarStacked100=104   ; 100% STACKED CONE BAR
Const $xlCylinderBarClustered=95   ; CLUSTERED CYLINDER BAR
Const $xlCylinderBarStacked=96   ; STACKED CYLINDER BAR
Const $xlCylinderBarStacked100=97   ; 100% STACKED CYLINDER BAR
Const $xlPyramidBarClustered=109   ; CLUSTERED PYRAMID BAR
Const $xlPyramidBarStacked=110   ; STACKED PYRAMID BAR
Const $xlPyramidBarStacked100=111   ; 100% STACKED PYRAMID BAR
; BUBBLE
Const $xlBubble3DEffect=87   ; 3D BUBBLE, BUBBLE WITH 3D EFFECTS
Const $xlBubble=15   ; BUBBLE
; COLUMN
Const $xl3DColumnClustered=54   ; 3D CLUSTERED COLUMN
;~ Const $xl3DColumn=-4100   ; 3D COLUMN      ==> already defined in Excel.au3
Const $xlConeCol=105   ; 3D CONE COLUMN
Const $xlCylinderCol=98   ; 3D CYLINDER COLUMN
Const $xlPyramidCol=112   ; 3D PYRAMID COLUMN
Const $xl3DColumnStacked=55   ; 3D STACKED COLUMN
Const $xl3DColumnStacked100=56   ; 3D 100% STACKED COLUMN
Const $xlColumnClustered=51   ; CLUSTERED COLUMN
Const $xlColumnStacked=52   ; STACKED COLUMN
Const $xlColumnStacked100=53   ; 100% STACKED COLUMN
Const $xlConeColClustered=99   ; CLUSTERED CONE COLUMN
Const $xlConeColStacked=100   ; STACKED CONE COLUMN
Const $xlConeColStacked100=101   ; 100% STACKED CONE COLUMN
Const $xlCylinderColClustered=92   ; CLUSTERED CYLINDER COLUMN
Const $xlCylinderColStacked=93   ; STACKED CYLINDER COLUMN
Const $xlCylinderColStacked100=94   ; 100% STACKED CYLINDER COLUMN
Const $xlPyramidColClustered=106   ; CLUSTERED PYRAMID COLUMN
Const $xlPyramidColStacked=107   ; STACKED PYRAMID COLUMN
Const $xlPyramidColStacked100=108   ; 100% STACKED PYRAMID COLUMN
; DOUGHNUT
Const $xlDoughnut=-4120   ; DOUGHNUT
Const $xlDoughnutExploded=80   ; EXPLODED DOUGHNUT
; LINE
Const $xl3DLine=-4101   ; 3D LINE
;~ Const $xlLine=4   ; LINE   ==> already defined in Excel.au3
Const $xlLineMarkers=65   ; LINE WITH MARKERS
Const $xlLineStacked=63   ; STACKED LINE
Const $xlLineStacked100=64   ; 100% STACKED LINE
Const $xlLineMarkersStacked=66   ; STACKED LINE WITH MARKERS
Const $xlLineMarkersStacked100=67   ; 100% STACKED LINE WITH MARKERS
; PIE
Const $xl3DPie=-4102   ; 3D PIE
Const $xl3DPieExploded=70   ; 3D EXPLODED PIE
Const $xlBarOfPie=71   ; BAR OF PIE
Const $xlPieExploded=69   ; EXPLODED PIE
Const $xlPie=5   ; PIE
Const $xlPieOfPie=68   ; PIE OF PIE
; RADAR
;~ Const $xlRadar=-4151   ; RADAR     ==> already defined in Excel.au3
Const $xlRadarFilled=82   ; FILLED RADAR
Const $xlRadarMarkers=81   ; RADAR WITH DATA MARKERS
; SCATTER
Const $xlXYScatter=-4169   ; SCATTER
Const $xlXYScatterLines=74   ; SCATTER WITH LINES
Const $xlXYScatterLinesNoMarkers=75   ; SCATTER WITH LINES AND NO DATA MARKERS
Const $xlXYScatterSmooth=72   ; SCATTER WITH SMOOTH LINES
Const $xlXYScatterSmoothNoMarkers=73   ; SCATTER WITH SMOOTH LINES AND NO DATA MARKERS
; STOCK
Const $xlStockHLC=88   ; STOCK HLC (HIGH-LOW-CLOSE)
Const $xlStockOHLC=89   ; STOCK OHLC (OPEN-HIGH-LOW-CLOSE)
Const $xlStockVHLC=90   ; STOCK VHLC (VOLUME-HIGH-LOW-CLOSE)
Const $xlStockVOHLC=91   ; STOCK VOHLC (VOLUME-OPEN-HIGH-LOW-CLOSE)
; SURFACE
Const $xlSurface=83   ; 3D SURFACE
Const $xlSurfaceWireframe=84   ; 3D SURFACE WIREFRAME
Const $xlSurfaceTopView=85   ; SURFACE TOP VIEW
Const $xlSurfaceTopViewWireframe=86   ; SURFACE TOP VIEW WIREFRAME

Here is the udf and example:

expandcollapse popup

#include <Excel.au3>
#include <MSOfficeExcelChartTypeConstants.au3>
 
; not defined in Excel.au3
Const $xlSeriesAxis = 3
 
Example()
Exit
 
 
Func Example()
; open excel file
$oExcel = _ExcelBookNew()
 
; create data
_ExcelWriteCell($oExcel, "Date", 1, 1)
_ExcelWriteCell($oExcel, "10/01/2011", 2, 1)
_ExcelWriteCell($oExcel, "10/02/2011", 3, 1)
_ExcelWriteCell($oExcel, "10/03/2011", 4, 1)
_ExcelWriteCell($oExcel, "10/04/2011", 5, 1)
_ExcelWriteCell($oExcel, "10/05/2011", 6, 1)
$oExcel.ActiveSheet.Columns(1).AutoFit
_ExcelWriteCell($oExcel, "Sales Store 1", 1, 2)
_ExcelWriteCell($oExcel, "10", 2, 2)
_ExcelWriteCell($oExcel, "23", 3, 2)
_ExcelWriteCell($oExcel, "15", 4, 2)
_ExcelWriteCell($oExcel, "20", 5, 2)
_ExcelWriteCell($oExcel, "34", 6, 2)
$oExcel.ActiveSheet.Columns(2).AutoFit
_ExcelWriteCell($oExcel, "Sales Store 2", 1, 3)
_ExcelWriteCell($oExcel, "18", 2, 3)
_ExcelWriteCell($oExcel, "23", 3, 3)
_ExcelWriteCell($oExcel, "40", 4, 3)
_ExcelWriteCell($oExcel, "32", 5, 3)
_ExcelWriteCell($oExcel, "28", 6, 3)
$oExcel.ActiveSheet.Columns(3).AutoFit
; Example 1: stacked bar chart - requires using arrays for $DataName and $DataRange (multiple data lines)
$Graph_position = "B2:K24"
$XValueRange = "=Sheet1!R2C1:R6C1"
Dim $_DataRange[3]=[2,"=Sheet1!R2C2:R6C2","=Sheet1!R2C3:R6C3"]
Dim $_DataName [3]=[2,"=Sheet1!R1C2:R1C2","=Sheet1!R1C3:R1C3"]
$iWorksheet = 2
_CreateExcelChart($oExcel, $iWorksheet, "Sales", $xl3DColumn, $Graph_position, $XValueRange, $_DataRange, $_DataName, 0, "Date", "Location", "Quantity")
; Example 2: example with one data Column
$Graph_position = "B26:K45"
$XValueRange = "=Sheet1!R1C1:R6C1"
$DataRange = "=Sheet1!R2C2:R6C2"
$_DataName = "=Sheet1!R1C2:R1C2"
$Title = _ExcelReadCell($oExcel, "B1")
  _CreateExcelChart($oExcel, $iWorksheet, $Title , $xl3DPieExploded, $Graph_position, $XValueRange, $DataRange, $_DataName)
 
; set the names of the worksheets
_ExcelSheetNameSet($oExcel, "Data")
_ExcelSheetActivate($oExcel, $iWorksheet)
_ExcelSheetNameSet($oExcel, "Graph")
Return
EndFunc ;==> Example
 
; #FUNCTION# ====================================================================================================
; Name...........:  _CreateExcelChart
; Description....:  Create a graph win Excel
; Syntax.........:  _CreateExcelChart( $oExcel,  $iWorksheet,  $sTitle ,  $iChartType,  $sSizeByCells,  $XValueRange,  $DataRange, $DataName, $iLegend = 1, $iXTitle = "", $iYTitle = "", $iZTitle = "")
; Parameters ....:  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;       $iWorksheet - Worksheet number to use for the graph creation (eg. 1)
;                  $sTitle - [Optional] - Chart Title
;        If empty, no title will be displayed
;       $iChartType - Chart type Number to be used ( see MSOfficeExcelChartTypeConstants.au3 for details, eg. $xl3DColumn)
;       $sSizeByCells - The LH top and RH bottom corner of the Graph (eg. "B2:K24")
;    $XValueRange - Category (X) axis label range
;      always a single range (eg. "=Sheet1!R2C1:R6C1")
;    $DataRange - The values range
;       either a single range (eg. "=Sheet1!R2C2:R6C2")
;      or an array (eg. Dim $_DataRange[3]=[2,"=Sheet1!R2C2:R6C2","=Sheet1!R2C3:R6C3"])
;      Note: Element 0 of the array is required but not used
;    $DataName - Header Name of the range
;       either a single range (eg. "=Sheet1!R1C2:R1C2")
;      or an array (eg. Dim $_DataName [3]=[2,"=Sheet1!R1C2:R1C2","=Sheet1!R1C3:R1C3"])
;      Note: Element 0 of the array is required but not used
;    $iLegend - [Optional] 1 is show legend (Default)
;          0 hide legend
;    $iXTitle - [Optional] X Axis title (Default = Off)
;    $iYTitle - [Optional] Y Axis title (Default = Off)
;    $iZTitle - [Optional] Y Axis title (Default = Off)
;
; Return values..:  none
;
; Authors........:  GreenCan
;
; Example........: Yes
; Remarks........:
; ===============================================================================================================
Func _CreateExcelChart( $oExcel,  $iWorksheet,  $sTitle,  $iChartType,  $SizeByCells,  $XValueRange,  $DataRange, $DataName, $iLegend = 1, $iXTitle = "", $iYTitle = "", $iZTitle = "")
$PlotBy = 2
; activate worksheet
$oSheet = $oExcel.Worksheets ($iWorksheet)
Local $oChartRange;- The range where you want the chart
Local $oNewChart ;- The ChartObject itself
$oChartRange = $oSheet.Range($SizeByCells)
$oNewChart = $oSheet.ChartObjects.Add($oChartRange.Left, $oChartRange.Top, $oChartRange.Width, $oChartRange.Height)
$oChart = $oNewChart.Chart
 
With $oChart
  .ChartType = $iChartType
  .SetSourceData ($oExcel.Worksheets(2).Range("A1:A1"), $PlotBy )
EndWith
 
If $oChart.SeriesCollection.Count = 0 Then
   $oChart.SeriesCollection.NewSeries
EndIf
;give an error "Both $DataRange & $DataName must be same type"
If (IsArray($DataName) And Not(IsArray($DataRange))) Or (Not(IsArray($DataName)) And IsArray($DataRange)) Then
   ;MsgBox(4096, "", "Both $DataRange & $DataName must be the same type")
   ConsoleWrite("Both $DataRange & $DataName must be the same type" & @CR)
   SetError(1)
   Return
EndIf
If IsArray($DataRange) Then
   $oChart.SeriesCollection(1).Delete
   For $i = 1 To UBound($DataName)-1
    $oChart.SeriesCollection.NewSeries
    With $oChart.SeriesCollection($i)
  .Name = $DataName[$i] ;- set name of values
  .XValues = $XValueRange ; X values ;R=row, C=colunm
  .Values = $DataRange[$i]
    EndWith
   Next
Else
   With $oChart.SeriesCollection(1)
    .Name = $DataName  ;- set name of values
    .XValues = $XValueRange ; X values ;R=row, C=colunm
    .Values = $DataRange
   EndWith
  EndIf
 
With $oChart
  ;.PlotArea.Select
  .HasDataTable = False
 
  ; Chart title
  If $sTitle <> "" Then
   .HasTitle = 1
   .ChartTitle.Characters.Text = $sTitle ;- set name of chart
  Else
   .HasTitle = 0
  EndIf
  ; legend
  If $iLegend = 0 Then
   .HasLegend = 0
  EndIf
  ; X Axis title (1)
  If $iXTitle <> "" Then
   .Axes($xlCategory).HasTitle = 1
   .Axes($xlCategory).AxisTitle.Characters.Text = $iXTitle
  EndIf
  ; Z Axis title (2)
  If $iZTitle <> "" Then
   .Axes($xlValue).HasTitle = 1
   .Axes($xlValue).AxisTitle.Characters.Text = $iZTitle
  EndIf
  ; Y Axis title (3)
  If $iYTitle <> "" Then
   .Axes($xlSeriesAxis).HasTitle = 1
   .Axes($xlSeriesAxis).AxisTitle.Characters.Text = $iYTitle
  EndIf
 
EndWith
Return
EndFunc ;==>_CreateExcelChart

The example shows how to create 2 graph, one 3D Column with 2 rows of data and one with an exploded Pie with one row of data.

GreenCan

[water]

Hi GreenCan,

thanks a lot for the code!

I hope to start the design phase for the UDF quite soon. I will post the list of functions and a description for each before starting to code.

So we can discuss the design and see if something is missing etc.

Water

[water]

The "improvements" made in Office 2007 are so drastic that a lot of features from earlier Office versions do not work anymore or work differently.

Please check:

Excel 97-2003 features that are not supported in Office Excel 2007 or

What happened to my favorite charting features?

To reduce complexity of the UDF I'm only going to support Excel 2007 and later.

What do you think? I this a problem for you?

[guinness]

What do you think?

Mainstream support for Office 2003 application ended in April 2009 and extended support ends in April 2014

From wikipedia about Office 2003 and future updates. I think it's suffice to say Excel 2007 is a good choice.

For those interested ... extended support is obtained when a company signs an exclusive contract with Microsoft to support the for mentioned product for a limited time.

[water]

In July I asked a similar regarding Outlook. Not too many votes but I got the impression that many users still work with Outlook 2003.

I just want to get sure that people don't get disappointed when their old Excel version isn't supported.

[GreenCan]

Yes, the code that I posted on 4 Nov #2 only works with 2003. It crashed on a 2007. I didn't have the time to check the reason why it crashed.

And yes, I am using 2003 still but will move forward to 2010 soon.

I guess, a 2007+ UDF should suffice.

[water]

Hi GreenCan,

thanks for the reply.

I was able to make it work with Excel 2010. I'm going to implement the function for chart creation plus one example function to work with the chart title.

I've added some error checking plus a COM error handler.

I hope I can post a preview quite soon

[Bert]

Hey Water, would it not be more "clean" to update excel.au3 to include what you have in mind?

[water]

Hi MPH,

I fear that the Excel Chart UDF will become quite large. OutlookEX has ~5600 lines, the Active Directory UDF ~4500 lines.

The Excel UDF is an "official" UDF that just needs to be brushed up a bit, whereas the Chart functionality is still in design phase and needs at least a year before the first "production" version can be released.

Just my 0.02$

[water]

Screenshot of the chart create function:

[water]

A first try. The UDF plus some example scripts:

ExcelChart.au3 - The UDF

_XLChart_AxisSet.au3 - Set the properties of the selected Axis

_XLChart_ChartCreate.au3 - Creates 3 charts on worksheet 1 together with the data plus 1 chart on a separate chart sheet

_XLChart_FontSet.au3 - Changes the font settings of the title in chart 1 and the x-axis title in chart 2

_XLChart_TicksSet.au3 - Set tick marks and gridlines of a chart.

_XLChart_TitleGet.au3 - Return information about a title. This can be the chart or any axis title.

_XLChart_TitleSet.au3 - Set properties of a title. This can be the chart or any axis title.

Functions painted in orange are new.

What do you think?

We released the UDF

Edited December 12, 2011 by water

[GreenCan]

Water,

A quick check on 2003...

_XLChart_ChartCreate.au3: The chart "A28:F46" is empty.

_XLChart_FontSet.au3 works fine.

I have to dig in the problem but I leave on a business trip for the next 2 days. I will try to get more details by Friday.

[water]

Hi GreenCan,

Thanks for testing!

Glad most of the charts work with Excel 2003. As the COM error handler is already implemented you could insert

$iXLC_Debug = 2

at the top of the script and you will get detailed error information in a MsgBox.

And don't hurry! We have all the time in the world to create this UDF

Edited November 9, 2011 by water

[GreenCan]

Hi Water,

These are the errors in xl2003:

COM Error Encountered in _XLChart_ChartCreate.au3
ExcelChart UDF version = 0.1.0
Scriptline = 199
NumberHex = 80020009
Number = -2147352567
WinDescription =
Description = Unable to set the Name property of the Series class
Source = Microsoft Excel
HelpFile = C:\Program Files\Microsoft Office\Office\1033\xlmain9.chm
HelpContext = 0
LastDllError = 0

However, I wouldn't worry about these, as previously we decided to focus on Excel 2007+, I would not try to resolve these.

++++

I have written a new function _XLChart_TickLabelPosition

expandcollapse popup

#include-once
#include <excel.au3>
; Graph - interface constants
; Axis labels
Global Const $xlNextToAxis = 4 ;
; Global Const $xlNone = -4142  ; <== Already defined in Excel.au3
Global Const $xlHigh = -4127
Global Const $xlLow = -4134

#cs
; Yet unused Graph - interface constants
Global Const $xlAll = -4104
Global Const $xlBoth = 1
Global Const $xlChecker = 9
Global Const $xlCircle = 8
Global Const $xlCorner = 2
Global Const $xlCrissCross = 16
Global Const $xlCross = 4
Global Const $xlDiamond = 2
Global Const $xlDistributed = -4117
Global Const $xlDoubleAccounting = 5
Global Const $xlFixedValue = 1
Global Const $xlFormats = -4122
Global Const $xlGray16 = 17
Global Const $xlGray8 = 18
Global Const $xlGrid = 15
Global Const $xlInside = 2
Global Const $xlJustify = -4130
Global Const $xlLightDown = 13
Global Const $xlLightHorizontal = 11
Global Const $xlLightUp = 14
Global Const $xlLightVertical = 12
Global Const $xlManual = -4135
Global Const $xlMinusValues = 3
Global Const $xlModule = -4141
Global Const $xlOff = -4146
Global Const $xlOn = 1
Global Const $xlPercent = 2
Global Const $xlPlus = 9
Global Const $xlPlusValues = 2
Global Const $xlSemiGray75 = 10
Global Const $xlShowLabel = 4
Global Const $xlShowLabelAndPercent = 5
Global Const $xlShowPercent = 3
Global Const $xlShowValue = 2
Global Const $xlSimple = -4154
Global Const $xlSingle = 2
Global Const $xlSingleAccounting = 4
Global Const $xlSolid = 1
Global Const $xlSquare = 1
Global Const $xlStar = 5
Global Const $xlStError = 4
Global Const $xlToolbarButton = 2
Global Const $xlTriangle = 3
Global Const $xlGray25 = -4124
Global Const $xlGray50 = -4125
Global Const $xlGray75 = -4126
Global Const $xl3DBar = -4099
Global Const $xl3DSurface = -4103
Global Const $xlBar = 2
Global Const $xlColumn = 3
Global Const $xlCombination = -4111
Global Const $xlCustom = -4114
Global Const $xlDefaultAutoFormat = -1
Global Const $xlMaximum = 2
Global Const $xlMinimum = 4
Global Const $xlOpaque = 3
Global Const $xlTransparent = 2
Global Const $xlBidi = -5000
Global Const $xlLatin = -5001
Global Const $xlContext = -5002
Global Const $xlLTR = -5003
Global Const $xlRTL = -5004
Global Const $xlFullScript = 1
Global Const $xlPartialScript = 2
Global Const $xlMixedScript = 3
Global Const $xlMixedAuthorizedScript = 4
Global Const $xlDefault = -4143
Global Const $xlVisualCursor = 2
Global Const $xlLogicalCursor = 1
Global Const $xlSystem = 1
Global Const $xlPartial = 3
Global Const $xlHindiNumerals = 3
Global Const $xlBidiCalendar = 3
Global Const $xlGregorian = 2
Global Const $xlComplete = 4
Global Const $xlScale = 3
Global Const $xlWizardDisplayAlways = 1
Global Const $xlWizardDisplayDefault = 0
Global Const $xlWizardDisplayNever = 2
#ce

; #FUNCTION# ====================================================================================================
; Name...........: _XLChart_TickLabelPosition
; Description....: Set TickLabel Position for the specified object.
; Syntax.........: _XLChart_FontSet($oXLC_Object, $bXLC_Visible = Default)
; Parameters ....: $oXLC_Object  - Object for which the font properties should be set (ChartTitle, AxisTitle ...)
;                 $bXLC_Visible - If True the Ticklabel will not display ($xlNone)
;      other accepted values:  $xlHigh  - Above the graph
;           $xlNextToAxis -
;           $xlLow
; Return values .: Success - 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oXLC_Object is no object
; Authors........: GreenCan
; Modified ......:
; Remarks .......: Valid objects are:
;     Axes($xlSeriesAxis) ==> the Y Axis title
;     Axes(xlCategory)  ==> the X Axis title
;     Axes(xlValue)   ==> the Z Axis title
;   
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _XLChart_TickLabelPosition($oXLC_Object, $bXLC_Visible = Default)
If Not IsObj($oXLC_Object) Then Return SetError(1, 0, 0)

If $bXLC_Visible = Default Then
  $oXLC_Object.TickLabelPosition = $xlNone
Else
  $oXLC_Object.TickLabelPosition = $bXLC_Visible
EndIf
Return 1
EndFunc   ;==>_XLChart_TickLabelPosition

The series label in the example, second graph (sales store 1) should prefarably be hidden, that's what the function does.

Although not so useful, it can also hide or change the position of the other axe labels.

The script contains a new set of yet unused graph constants (put in comment)

This is the modified example script, the new function is example 5 (I permitted myself to put your both examples together, hope you don't mind...)

expandcollapse popup

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

; *****************************************************************************
; Create example environment
; *****************************************************************************
Global $aExcel = _XLChart_Example()
If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example!")
; *****************************************************************************
; 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!B1"]
Local $oChart1 = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DColumn, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "Location", "Quantity", True)
; *****************************************************************************
; Example 2
; 3D PYRAMID COLUMN: With legend
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
$Graph_position = "H8:M26"
Local $oChart2 = _XLChart_ChartCreate($aExcel[0], 1, $xlPyramidCol, $Graph_position, "", $XValueRange, $DataRange, $DataName, True, "Sales", "Date", "", "Quantity")
; *****************************************************************************
; Example 3
; 3D STACKED AREA: Without legend
; *****************************************************************************
$Graph_position = "A28:F46"
Local $oChart3 = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DAreaStacked, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date")
; *****************************************************************************
; Example 4
; 3D EXPLODED PIE: With legend. Create the chart on a separate chart sheet
; *****************************************************************************
$Graph_position = "H28:M46"
$XValueRange = "=_XLChart_Example!R2C1:R6C1"
$DataRange = "=_XLChart_Example!R2C2:R6C2"
$DataName = "=_XLChart_Example!B1"
Global $Title = _ExcelReadCell($aExcel[0], "B1")
Local $oChart3 = _XLChart_ChartCreate($aExcel[0], 1, $xl3DPieExploded, 0, "Example Chart", $XValueRange, $DataRange, $DataName, True, $Title)
; *****************************************************************************
; Example 5
; Remove the Y Axis title (the series axis or $xlSeriesAxis)
; *****************************************************************************
_XLChart_TickLabelPosition($oChart2.Axes($xlSeriesAxis))

; *****************************************************************************
; Example 6
; Change title of Chart 1: Arial, 24, bold, italic, underline, magenta
; *****************************************************************************
_XLChart_FontSet($oChart1.ChartTitle, "Arial", 24, True, True, True, 0xFF00FF)
; *****************************************************************************
; Example 7
; Change x-Axis title of chart 2: Courier New, 36, bold, green
; *****************************************************************************
_XLChart_FontSet($oChart2.Axes($xlCategory).AxisTitle, "Courier New", 36, True, False, False, 0x00FF00)

For _XLChart_FontSet, I would suggest to add in remarks for which objects this function is valid, the same way that I did in _XLChart_TickLabelPosition.

This will make it more clear and useful for anyone who would use the udf in the future.

GreenCan

[water]

Hi GreenCan,

thanks for testing!

At the moment I try to shed some light on the structure of the Excel object model. Based on this structure we should group together the functionality of the UDF:

; A chart on its own sheet
Workbook
  Chart

; A chart on a worksheet
Workbook
  Worksheet
    Chartobject
      Chart

; For both types of charts
Chart
  Axes (Collection)
  ChartArea
  ChartTitle
  Datatable
  Floor
  Legend
  PageSetup
  PlotArea
  SeriesCollection (Collection)
    Series
      Border
      Points (Collection)
      Interior
  SideWall
  Walls

I intend to create two functions for every object. One to set the object, another to query the object.

This functions will be separated into two groups. One group with functions unique to a single object (e.g. only an axis has tick marks) and a second group of functions which can be used on multiple objects (e.g. _XLC_TitleSet sets the title for the chart and axis objects).

What do you think?

In a next step I'll try to list all functions which will handle axes. In one of this functions your _XLChart_TickLabelPosition function will be incorporated. How does this sound?

[GreenCan]

Yes, your approach makes sense.

Let me know if I can help with something.

[water]

Some kind of priority list would be fine.

What functions need to be in the UDF, what would be nice to have and what should only be done if we have very, very much spare time

[Zedna]

I'm going to create an UDF which will let you create/manipulate Excel Charts.

It will consist of one function to create a simple chart (you just have to specify the data area, the type of the graph to draw, title, position of the chart etc.) and a lot of functions to create/modify all aspects of the chart.

Anyone interested?

I can use such function in one of my current projects.

So definitely YES.

EDIT: It would be awesome to be it compatible also with OpenOffice/LibreOffice which are FREEWARE, MS Office is not FREEWARE.

Edited November 12, 2011 by Zedna

[GreenCan]

Water,

I'll work on this, give me a few days

Meanwhile, here's a small function that checks the installed Excel version, I guess that will be useful for compatibility issues

expandcollapse popup

; *****************************************************************************
; Excel Version Check - Warning
; *****************************************************************************
Local $ExcelVersion = _XLversion()
If $ExcelVersion <> "Excel 2007" And $ExcelVersion <> "Excel 2010" Then MsgBox(48,"Warning only","The ExcelChart UDF is supporting Excel 2007 and 2010 only." & @CRLF & "The application will continue after this message",3)
; #FUNCTION# ====================================================================================================
; Name...........: _XLversion
; Description....: Returns the installed Excel version
; Syntax.........: _XLversion()
; Parameters ....: none
; Return values .: Success - Excel version
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oXLC_Object is no object
; Authors........: GreenCan
; Modified ......:
; Remarks .......: ExcelChart is written for Excel 2007 and 2010    
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _XLversion()
$oExcel = ObjCreate("Excel.Application")
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)

$ExcelVersion = $oExcel.Version
Select
  Case $ExcelVersion = "5.0"
   Return "Excel 5"
  Case $ExcelVersion = "7.0"
   Return "Excel 95"
  Case $ExcelVersion = "8.0"
   Return "Excel 97"
  Case $ExcelVersion = "9.0"
   Return "Excel 2000"
  Case $ExcelVersion = "10.0"
   Return "Excel 2002"
  Case $ExcelVersion = "11.0"
   Return "Excel 2003"
  Case $ExcelVersion = "12.0"
   Return "Excel 2007"
  Case $ExcelVersion = "14.0"
   Return "Excel 2010"  
  Case Else
   Return "Unknown version"
EndSelect
EndFunc   ;==>_XLversion

@Zedna

I have OpenOffice on my PC and I just installed Excel 2007 for this project , concentrating on Excel will already require some efforts.

Working on OpenOffice.org API charts could be a future project but there is still a lot of work to do in the OOoCOM_UDF (only 9 functions defined?, the basic functions are not yet complete)

GreenCan