ExcelChart UDF - Example Scripts

[water]

In this thread you will find some useful scripts based on the ExcelChart UDF (for download please see my signature).

[GreenCan]

A small example of a Saddle chart (for more information see http://www.horsesaddleshop.com/saddle-seat-size-chart.html )

Greencan

PS see also the other published examples in the ExcelChart Download topic

_XLChart_Saddle.au3

[water]

Version 0.2.2.0 of the UDF has been released.

Have fun testing!

For download please see my signature.

[GreenCan]

This is an example of a Thermometer Chart created with ExcelChart function.

GreenCan

_XLChart_Example_Thermometer Chart v3.0.zip

Edited February 17, 2012 by GreenCan

[GreenCan]

Updated version of Gauge Chart (requires ExcelChart v 0.2.2.0)

_XLChart_Example_Gauge Chart.au3

expandcollapse popup

#include "ExcelChart.au3"
#include <Excel.au3>
#include <Array.au3>
Opt('MustDeclareVars', 1)
Global $iXLC_Debug = 0
; *****************************************************************************
; Create example environment
; *****************************************************************************
Global $oExcel = _ExcelBookNew(1)
If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _ExcelBookNew!")
; *****************************************************************************
; Excel Version Check - exit if earlier than Excel 2007
; *****************************************************************************
If _XLChart_Version($oExcel) < 12 Then Exit MsgBox(16, "Excel Gauge Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).")
#region declare
Local $sCell, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sFormula, $sFunction, _
$iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideH, $iEdgeInsideV
#endregion declare
; New function to be developed
$oExcel.ActiveWindow.DisplayGridlines = False
; *****************************************************************************
; Set title
; *****************************************************************************
$sCell = "A1"
_ExcelWriteCell($oExcel, "Gauge Chart Example", $sCell)
; set row height
_ExcelRowHeightSet($oExcel, "1:1", 30)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelRowHeightSet' on line " & @ScriptLineNumber)
; set the font
_ExcelFontSet($oExcel, $sCell, 1, 1, 1, "Arial Rounded MT Bold")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSet' on line " & @ScriptLineNumber)
; set the font size
_ExcelFontSetSize($oExcel, $sCell, 1, 1, 1, 14)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetSize' on line " & @ScriptLineNumber)
; set font property
_ExcelFontSetProperties($oExcel, $sCell, 1, 1, 1, True, True, False)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetProperties' on line " & @ScriptLineNumber)
; set horizontal alignment of title only
_ExcelHorizontalAlignSet($oExcel, $sCell, 1, 1, 1, "center")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber)
; set vertical alignment
_ExcelVerticalAlignSet($oExcel, $sCell, 1, 1, 1 , "center")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelVerticalAlignSet' on line " & @ScriptLineNumber)
; main  Title color
_ExcelFontSetColor($oExcel, $sCell, 1, 1, 1, 255, 0x8D1A1A) ; font color
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber)
_ExcelCellColorSet($oExcel, $sCell, 1, 1, 1, 255, 0x7DFFFF) ; color the background
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber)
; Now Merge the main title line
$sRangeOrRowStart = 1
$iColStart = 1
$iRowEnd = $sRangeOrRowStart
$iColEnd = 10
_ExcelCellMerge($oExcel, True, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber)
$sCell = "A2"
_ExcelWriteCell($oExcel, "By GreenCan", $sCell)
_ExcelFontSetProperties($oExcel, $sCell, 1, 1, 1, False, True, False)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetProperties' on line " & @ScriptLineNumber)
_ExcelCellColorSet($oExcel, $sCell, 1, 1, 1, 255, 0x00CEFF) ; color the background
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber)
_ExcelCellMerge($oExcel, True, $sRangeOrRowStart + 1, $iColStart, $iRowEnd + 1, $iColEnd)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber)
; Disable screen updating to enhance performance
_XLChart_ScreenUpdateSet($oExcel, 0) ; 2)
$sCell = "H18"
_ExcelWriteCell($oExcel,  "Press Escape to quit", $sCell)
; set the font size
_ExcelFontSetSize($oExcel, $sCell, 1, 1, 1, 14)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetSize' on line " & @ScriptLineNumber)
; set font property
_ExcelFontSetProperties($oExcel, $sCell, 1, 1, 1, True, True, False)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetProperties' on line " & @ScriptLineNumber)
; set font color
_ExcelFontSetColor($oExcel, $sCell, 1, 1, 1, 255, 0x0000FF) ; font color
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber)
; *****************************************************************************
; Create the data for the Gauge Chart
; *****************************************************************************
; draw borders
$iEdgeLeft = 1
$iEdgeTop = 1
$iEdgeBottom = 1
$iEdgeRight = 1
$iEdgeInsideH = 1
$iEdgeInsideV = 0
_ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart, $iColStart, $iRowEnd + 1, $iColEnd, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber)
; set variable Data section
$sRangeOrRowStart = 3
$iColStart = 2
$iRowEnd = $sRangeOrRowStart
$iColEnd = $iColStart
_ExcelWriteCell($oExcel, "Changeable" & @CRLF & "values:", $sRangeOrRowStart, $iColStart + 1)
_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart + 1, $iRowEnd, $iColEnd + 1,"right" )
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber)
_ExcelWriteCell($oExcel, "Gauge Value (0 to 100)", $sRangeOrRowStart + 1, $iColStart)
; _ExcelWriteCell($oExcel, 25, $sRangeOrRowStart + 1, $iColStart + 1) ; removed for the dynamic example
_ExcelWriteCell($oExcel, "Red", $sRangeOrRowStart + 3, $iColStart)
_ExcelWriteCell($oExcel, 45, $sRangeOrRowStart + 3, $iColStart + 1)
_ExcelWriteCell($oExcel, "Yellow", $sRangeOrRowStart + 4, $iColStart)
_ExcelWriteCell($oExcel, 55, $sRangeOrRowStart + 4, $iColStart + 1)
; autofit column B
_ExcelColWidthSet($oExcel, "B:B", "autofit")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelColWidthSet' on line " & @ScriptLineNumber)
_ExcelColWidthSet($oExcel, "C", 12)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelColWidthSet' on line " & @ScriptLineNumber)
; set background of input fields
_ExcelCellColorSet($oExcel, $sRangeOrRowStart + 1, $iColStart + 1, $iRowEnd + 1, $iColEnd + 1, 255, 0x80FF80)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber)
; color the background
_ExcelCellColorSet($oExcel, $sRangeOrRowStart + 3, $iColStart + 1, $iRowEnd + 4, $iColEnd + 1, 255, 0x80FF80)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function ''_ExcelCellColorSet on line " & @ScriptLineNumber)
; draw borders
_ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart + 1, $iColStart + 1, $iRowEnd + 1, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber)
_ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart + 3, $iColStart + 1, $iRowEnd + 4, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber)
; set Gauge Data
$sRangeOrRowStart = 4
$iColStart = 9
$iRowEnd = $sRangeOrRowStart
$iColEnd = $iColStart
_ExcelWriteCell($oExcel, "Gauge", $sRangeOrRowStart, $iColStart)
; Font and background color
_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0xFFFFFF) ; font color
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber)
_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0x8D1A1A) ; color the background
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber)
; merge title
_ExcelCellMerge($oExcel, True, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd + 1)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber)
; and set alignment
_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, "center")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber)
_ExcelWriteCell($oExcel, "Start", $sRangeOrRowStart + 1, $iColStart)
_ExcelWriteCell($oExcel, "Red", $sRangeOrRowStart + 2, $iColStart)
_ExcelWriteCell($oExcel, "Yellow", $sRangeOrRowStart + 3, $iColStart)
_ExcelWriteCell($oExcel, "End", $sRangeOrRowStart + 4, $iColStart)
_ExcelWriteCell($oExcel, "Blank", $sRangeOrRowStart + 5, $iColStart)
_ExcelWriteCell($oExcel, 0, $sRangeOrRowStart + 1, $iColStart + 1)
_ExcelWriteFormula($oExcel, "=C6" , "J6")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula'_ExcelWriteFormula on line " & @ScriptLineNumber)
_ExcelWriteFormula($oExcel, "=C7-C6" , "J7")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula' on line " & @ScriptLineNumber)
_ExcelWriteFormula($oExcel, "=100-C7" , "J8")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula' on line " & @ScriptLineNumber)
_ExcelWriteCell($oExcel, 100, $sRangeOrRowStart + 5, $iColStart + 1)
; draw borders
$iEdgeInsideV = 1
_ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart, $iColStart, $iRowEnd + 5, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber)
; set Indicator Data
$sRangeOrRowStart += 7
$iColStart = 9
$iRowEnd = $sRangeOrRowStart
$iColEnd = $iColStart
_ExcelWriteCell($oExcel, "Indicator", $sRangeOrRowStart, $iColStart)
; Font and background color
_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0xFFFFFF) ; font color
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber)
_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0x8D1A1A) ; color the background
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber)
; merge title
_ExcelCellMerge($oExcel, True, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd + 1)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber)
; and set alignment
_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, "center")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber)
_ExcelWriteCell($oExcel, "GV", $sRangeOrRowStart + 1, $iColStart)
_ExcelWriteCell($oExcel, "Pointer", $sRangeOrRowStart + 2, $iColStart)
_ExcelWriteCell($oExcel, "End", $sRangeOrRowStart + 3, $iColStart)
_ExcelWriteCell($oExcel,  1, $sRangeOrRowStart + 2, $iColStart + 1)
_ExcelWriteFormula($oExcel, "=C4" , "J12")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula'_ExcelWriteFormula on line " & @ScriptLineNumber)
$sFormula = "=200-(J12+J13)"
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_Excel_ColumnNumberToLetter' on line " & @ScriptLineNumber)
ConsoleWrite("Translated function: " & $sFormula & @CR )
$sCell = _Excel_ColumnNumberToLetter($iColStart + 1) &  string($sRangeOrRowStart + 3)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_Excel_ColumnNumberToLetter' on line " & @ScriptLineNumber)
_ExcelWriteFormula($oExcel, $sFormula , $sCell)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula' on line " & @ScriptLineNumber)
; set borders
_ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart, $iColStart, $iRowEnd + 3, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH)
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber)
; name the sheet
_ExcelSheetNameSet($oExcel, "Gauge Chart")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelSheetNameSet' on line " & @ScriptLineNumber)
; *****************************************************************************
; create the Gauge chart
; *****************************************************************************
_XLChart_Gauge($oExcel, 1, "B10:F27", "$J$5:$J$9", "$I$5:$I$9", "$J$12:$J$14", "$I$12:$I$14")
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_XLChart_Gauge' on line " & @ScriptLineNumber)
; New Excel function to be developed
; protect sheet to avoid formula overwriting
; allow writing in cell C4, C6 and C7 only
$oExcel.Activesheet.Range("C4").Locked = False
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.Activesheet.Range().Locked' on line " & @ScriptLineNumber)
$oExcel.Activesheet.Range("C6:C7").Locked = False
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.Activesheet.Range().Locked' on line " & @ScriptLineNumber)
; allow writing in cell H18 for the displayed message
$oExcel.Activesheet.Range("H18").Locked = False
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.Activesheet.Range().Locked' on line " & @ScriptLineNumber)
; New Excel function to be developed
; now protect the sheet
$oExcel.ActiveSheet.Protect()
If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.ActiveSheet.Protect()' on line " & @ScriptLineNumber)
; Now enable screen updating again so that we can see the result
_XLChart_ScreenUpdateSet($oExcel, 1)
; *****************************************************************************
; Now demo Gauge
; *****************************************************************************
HotKeySet("{ESC}", "Terminate")
Local $iStart = 1, $iEnd
While 1
$iEnd = Random(51, 100, 1)
For $i = $iStart to $iEnd
  _ExcelWriteCell($oExcel, $i, "C4")
Next
$iStart = Random(0, 50, 1)
For $i = $iEnd to $iStart Step -1
  _ExcelWriteCell($oExcel, $i, "C4")
Next
WEnd
Exit
Func Terminate()
_ExcelWriteCell($oExcel,  "", "H18")
    Exit
EndFunc ;==>Terminate
; ===============================================================================================================
; Name...........: _XLChart_Gauge
; Description....: Create a _XLChart_Gauge chart (dashboard)
; Syntax.........: _XLChart_Gauge($oXLC_Excel, $vXLC_Worksheet, $sXLC_SizeByCells, $GaugeDataRange, $GaugeDataName, $IndicatorDataRange, $IndicatorDataName)
; Parameters ....: $oXLC_Excel     - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                 $vXLC_Worksheet   - Worksheet number or name to use for the chart creation (eg. 1)
;                 $sXLC_SizeByCells - The left-hand top and right-hand bottom corner of the chart (eg. "B2:K24").
;      $GaugeDataRange  - The values range used for the Gauge chart
;      $GaugeDataName  - Header name of the range of the Gauge chart
;      $IndicatorDataRange - The values range used for the Indicator chart
;      $IndicatorDataName - Header name of the range of the Indicator chart
; Return values .: Success - Draws the Gauge chart
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oXLC_Excel is not an object
;                 |2 - $vXLC_Worksheet could not be found
;                 |3 - Doughnut _XLChart_ChartCreate error. See @extended for details
;                 |4 - Doughnut _XLChart_DoughnutGroupSet error. See @extended for details
;                 |5 - Doughnut _XLChart_FillSet error. See @extended for details
;                 |6 - Pie _XLChart_ChartCreate error. See @extended for details
;                 |7 - Pie _XLChart_PieGroupSet error. See @extended for details
;                 |8 - Pie _XLChart_FillSet error. See @extended for details
;                 |9 - Pie _XLChart_LineSet error. See @extended for details
;                 |10 - Pie _XLChart_DatalabelSet error. See @extended for details
;                 |11 - Pie _XLChart_ObjectDelete error. See @extended for details
;                 |12 - Pie DataLabel.Text error. See @extended for details
; Authors........: GreenCan
; Modified ......:
; Remarks .......:  ; Enumeration XlReferenceStyle
;     Global Const $xlA1 = 1
;     Global Const $xlR1C1 = -4150 ;<== Already defined
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _XLChart_Gauge($oXLC_Excel, $vXLC_Worksheet, $sXLC_SizeByCells, $GaugeDataRange, $GaugeDataName, $IndicatorDataRange, $IndicatorDataName)
If Not IsObj($oXLC_Excel) Then Return SetError(1, 0, 0)
; Activate worksheet
Local $oXLC_Sheet = $oXLC_Excel.Worksheets($vXLC_Worksheet)
If @error Then Return SetError(2, @error, 0)
Local $sXLC_SheetName = $oXLC_Excel.Worksheets($vXLC_Worksheet).Name
If @error Then Return SetError(2, @error, 0)
; *****************************************************************************
; 1. Doughnut chart
; *****************************************************************************
Local $Graph_position = "='" & $sXLC_SheetName & "'!" & $sXLC_SizeByCells
$GaugeDataRange = "='" & $sXLC_SheetName & "'!" & $GaugeDataRange
$GaugeDataName = "='" & $sXLC_SheetName & "'!" & $GaugeDataName
Local $oChart1 = _XLChart_ChartCreate($oXLC_Excel, "Gauge Chart", $xlDoughnut, $Graph_position, "", "", $GaugeDataRange, $GaugeDataName, False, "Gauge")
If @error Then Return SetError(3, @error, 0)
; Chart rotate 270 degrees
_XLChart_DoughnutGroupSet($oChart1.ChartGroups(1), 270)
If @error Then Return SetError(4, @error, 0)
; set chart 3 series 1 color and transparency
_XLChart_FillSet($oChart1.SeriesCollection(1).Points(5), 0xFFFFFF, Default, False, Default, Default, Default, 1) ; lower half transparent
If @error Then Return SetError(5, @error, 0)
_XLChart_FillSet($oChart1.SeriesCollection(1).Points(2), 0xFF0000, 0xFFFFFF, False, 4, 1, 0.4) ; red fill gradient
If @error Then Return SetError(5, @error, 0)
_XLChart_FillSet($oChart1.SeriesCollection(1).Points(3), 0xFFCE00, Default) ; yellow fill
If @error Then Return SetError(5, @error, 0)
_XLChart_FillSet($oChart1.SeriesCollection(1).Points(4), 0x00FF19); green fill
If @error Then Return SetError(5, @error, 0)
; *****************************************************************************
; 2. Pie chart
; *****************************************************************************
$IndicatorDataRange = "='" & $sXLC_SheetName & "'!" & $IndicatorDataRange
$IndicatorDataName = "='" & $sXLC_SheetName & "'!" & $IndicatorDataName
Local $oChart2 = _XLChart_ChartCreate($oExcel, "Gauge Chart", $xlPie, $Graph_position, "", "", $IndicatorDataRange, $IndicatorDataName, False, " ")
If @error Then Return SetError(6, @error, 0)
; Chart rotate 270 degrees
_XLChart_PieGroupSet($oChart2.ChartGroups(1), 270)
If @error Then Return SetError(7, @error, 0)
; Pointers
_XLChart_FillSet($oChart2.SeriesCollection(1).Points(1), 0xFFFFFF, Default, Default, 3, 1, Default, 1) ; lower half transparent
If @error Then Return SetError(8, @error, 0)
_XLChart_FillSet($oChart2.SeriesCollection(1).Points(3), 0xFFFFFF, Default, Default, 3, 1, Default, 1) ; lower half transparent
If @error Then Return SetError(8, @error, 0)
_XLChart_FillSet($oChart2.SeriesCollection(1).Points(2), 0x000037) ; pointer
If @error Then Return SetError(8, @error, 0)
; set Border transparent
_XLChart_LineSet($oChart2.ChartArea, Default, Default, Default, Default, Default, $xlSheetHidden)
If @error Then Return SetError(9, @error, 0)
; Set chart area transparent
_XLChart_FillSet($oChart2.ChartArea, 0xCD0000, Default, Default, Default, Default, Default, 1)
If @error Then Return SetError(8, @error, 0)
; Set data label for points 2
_XLChart_DatalabelSet($oChart2.SeriesCollection(1), 2, True, Default, Default, Default, Default, $xlLabelPositionOutsideEnd)
If @error Then Return SetError(10, @error, 0)
; Delete the datalabels 1 and 3
_XLChart_ObjectDelete($oChart2.SeriesCollection(1).Points(1).DataLabel)
If @error Then Return SetError(11, @error, 0)
_XLChart_ObjectDelete($oChart2.SeriesCollection(1).Points(3).DataLabel)
If @error Then Return SetError(11, @error, 0)
; set the cell value of C4 in the data label point 2
; Water and I have decided not to make a function of the following line, except is there is a request for it
$oChart2.SeriesCollection(1).Points(2).DataLabel.Text = StringTrimRight($IndicatorDataRange,StringLen($IndicatorDataRange) - StringInStr($IndicatorDataRange,":") + 1 )
If @error Then Return SetError(12, @error, 0)
Return
EndFunc ;==>_XLChart_Gauge
#region ExcelEX udf extract
; #FUNCTION# ====================================================================================================
; Name...........: _Excel_ColumnNumberToLetter
; Description....: Convert column number to column letter.
; Syntax.........: _Excel_ColumnNumberToLetter($iColumnNumber)
; Parameters ....: $iColumnNumber - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; Return values .: Success - Returns the ColumnLetter in the range A to XFD
;                 Failure - Returns 0 and sets @error:
;                 |1 - Specified $iColumnNumber exceeds maximum authorized columns in Excel
; Authors........: GreenCan
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _Excel_ColumnNumberToLetter($iColumnNumber)
If $iColumnNumber > 16384 Then Return SetError(1, 0, 0)
If $iColumnNumber > 702 Then
  ; as from AAA
  Return Chr( Int( ( Int( Mod( $iColumnNumber - 703, 26 ) /26 )  + Int( ( $iColumnNumber - 703 ) /26 ) ) / 26 ) + 65 ) & _
      Chr( Mod( Int( Mod( $iColumnNumber - 703, 26 ) /26 )  + Int( ( $iColumnNumber - 703 ) /26 ) , 26) + 65 ) & _
      Chr( Mod( $iColumnNumber - 703, 26 ) + 65 )
ElseIf $iColumnNumber > 26 Then
  ; as from AA
  Return Chr( Int( ( $iColumnNumber - 1 ) / 26 ) + 64 ) & _
      Chr( Mod( $iColumnNumber - 1, 26 ) + 65 )
Else
  ; Columns A-Z
  Return Chr( $iColumnNumber + 64 )
EndIf
EndFunc ;==>_Excel_ColumnNumberToLetter
#endregion
#region ExcelCom udf extract (FOR THE PURPOSE OF THIS EXAMPLE ONLY)
; #FUNCTION# ====================================================================================================
; Function:  _ExcelCellColorSet
; Description:    Set the cell interior color value of a range in an Excel object.
; Syntax:          _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;         $iColorIndex = 1, $hColor = 0x000000)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;    $iColStart - The starting column for the number format(left) (default=1)
;    $iRowEnd - The ending row for the number format (bottom) (default=1)
;    $iColEnd - The ending column for the number format (right) (default=1)
;    $iColorIndex - The interior color index (default = 1, if > 254 then $hColor is used instead)
;    $hColor - Hex value of color used when colorindex > 254
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
;     @error=2 - Starting row or column invalid
;      @extended=0 - Starting row invalid
;      @extended=1 - Starting column invalid
;     @error=3 - Ending row or column invalid
;      @extended=0 - Ending row invalid
;      @extended=1 - Ending column invalid
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
  If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
  If $iColStart < 1 Then Return SetError(2, 1, 0)
  If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
  If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
  If $iColorIndex < 255 Then
   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Interior.ColorIndex = $iColorIndex
  Else
   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Interior.Color = $hColor
  EndIf
Else
  If $iColorIndex < 255 Then
   $oExcel.Activesheet.Range($sRangeOrRowStart).Interior.ColorIndex = $iColorIndex
  Else
   $oExcel.Activesheet.Range($sRangeOrRowStart).Interior.Color = $hColor
  EndIf
EndIf
Return 1
EndFunc ;==>_ExcelCellColorSet
; #FUNCTION# ====================================================================================================
; Function:  _ExcelCellMerge
; Description:    Merge/UnMerge cell(s) in a range.
; Syntax:          _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $fDoMerge - Flag, True performs a merge on the range, False reverses an existing merge in the range (True or False) (default=False)
;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;    $iColStart - The starting column for the number format(left) (default=1)
;    $iRowEnd - The ending row for the number format (bottom) (default=1)
;    $iColEnd - The ending column for the number format (right) (default=1)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
;     @error=2 - Starting row or column invalid
;      @extended=0 - Starting row invalid
;      @extended=1 - Starting column invalid
;     @error=3 - Ending row or column invalid
;      @extended=0 - Ending row invalid
;      @extended=1 - Ending column invalid
;     @error=4 - $fDoMerge is not set as True or False
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If $fDoMerge <> False and $fDoMerge <> True Then Return SetError(4, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
  If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
  If $iColStart < 1 Then Return SetError(2, 1, 0)
  If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
  If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
  $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).MergeCells = $fDoMerge
Else
  $oExcel.Activesheet.Range($sRangeOrRowStart).MergeCells = $fDoMerge
EndIf
Return 1
EndFunc ;==>_ExcelCellMerge
; #FUNCTION# ====================================================================================================
; Function:  _ExcelColWidthSet
; Description:    Set the column width of the specified column(s).
; Syntax:          _ExcelColWidthSet($oExcel, $vColumn, $vWidth)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $sColumn - A valid Excel column, either a number or an A1 string (i.e. 5, or "C" or a range "A:K")
;    $vWidth - The width of the column in points, or the string "autofit"
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelColWidthSet($oExcel, $vColumn, $vWidth)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If $vWidth = "autofit" Then
  $oExcel.Activesheet.Columns($vColumn).Autofit
Else
  $oExcel.Activesheet.Columns($vColumn).ColumnWidth = $vWidth
EndIf
Return 1
EndFunc ;==>_ExcelColWidthSet
; #FUNCTION# ====================================================================================================
; Function:  _ExcelCreateBorders
; Description:    Create Borders around a range of cells
; Syntax:          _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1,
;          $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;              $sBorderStyle - The type of border to use,  $xlThick, $xlThin, $xlDouble
;              $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;              $iColStart - The starting column for the number format(left) (default=1)
;              $iRowEnd - The ending row for the number format (bottom) (default=1)
;              $iColEnd - The ending column for the number format (right) (default=1)
;              $iEdgeLeft - Specify if the left edge of the selected cells should have a border (default=1) Yes
;              $iEdgeTop  - Specify if the Top edge of the selected cells should have a border (default=1) Yes
;              $iEdgeBottom - Specify if the Bottom edge of the selected cells should have a border (default=1) Yes
;              $iEdgeRight - Specify if the Right edge of the selected cells should have a border (default=1) Yes
;              $iEdgeInsideV - Specify if the Inside Verticle edges of the selected cells should have a border (default=0) No
;              $iEdgeInsideH - Specify if the Inside Horizontal edges of the selected cells should have a border (default=0) No
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;                 @error=1 - Specified object does not exist
;                 @error=2 - Starting row or column invalid
;                    @extended=0 - Starting row invalid
;                    @extended=1 - Starting column invalid
;                 @error=3 - Ending row or column invalid
;                    @extended=0 - Ending row invalid
;                    @extended=1 - Ending column invalid
; Author(s):        MikeOsdx <Using Generic Excel functions from locodarwin>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
       $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        if $iEdgeLeft = 1 Then
            With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeLeft)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeTop = 1 Then
            With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeTop)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeBottom = 1 Then
            With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeBottom)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeRight = 1 Then
            With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeRight)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeInsideV = 1 Then
            With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideVertical)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeInsideH = 1 Then
            With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideHorizontal)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
    Else
        if $iEdgeLeft = 1 Then
            With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeLeft)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeTop = 1 Then
            with $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeTop)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeBottom = 1 Then
            With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeBottom)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeRight = 1 Then
            With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeRight)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeInsideV = 1 Then
            With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideVertical)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
        if $iEdgeInsideH = 1 Then
            With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideHorizontal)
                .LineStyle = $xlContinuous
                .ColorIndex = $xlAutomatic
                ;.TintAndShade = 0
                .Weight = $sBorderStyle
            EndWith
        EndIf
    EndIf
    Return 1
EndFunc
; #FUNCTION# ====================================================================================================
; Function:  _ExcelFontSet
; Description:    Set the font face property of a range in an Excel object.
; Syntax:          _ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;    $iColStart - The starting column for the number format(left) (default=1)
;    $iRowEnd - The ending row for the number format (bottom) (default=1)
;    $iColEnd - The ending column for the number format (right) (default=1)
;    $sFontName - The font name to set the range to (default = "Arial")
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
;     @error=2 - Starting row or column invalid
;      @extended=0 - Starting row invalid
;      @extended=1 - Starting column invalid
;     @error=3 - Ending row or column invalid
;      @extended=0 - Ending row invalid
;      @extended=1 - Ending column invalid
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
  If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
  If $iColStart < 1 Then Return SetError(2, 1, 0)
  If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
  If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
  $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.Name = $sFontName
Else
  $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Name = $sFontName
EndIf
Return 1
EndFunc ;==>_ExcelFontSet
; #FUNCTION# ====================================================================================================
; Function:  _ExcelFontSetSize
; Description:    Set the font size property of a range in an Excel object.
; Syntax:          _ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;    $iColStart - The starting column for the number format(left) (default=1)
;    $iRowEnd - The ending row for the number format (bottom) (default=1)
;    $iColEnd - The ending column for the number format (right) (default=1)
;    $sFontSize - The font size in points to set the range to (default = 10 points)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
;     @error=2 - Starting row or column invalid
;      @extended=0 - Starting row invalid
;      @extended=1 - Starting column invalid
;     @error=3 - Ending row or column invalid
;      @extended=0 - Ending row invalid
;      @extended=1 - Ending column invalid
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
  If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
  If $iColStart < 1 Then Return SetError(2, 1, 0)
  If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
  If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
  $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.Size = $iFontSize
Else
  $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Size = $iFontSize
EndIf
Return 1
EndFunc ;==>_ExcelFontSetSize
; #FUNCTION# ====================================================================================================
; Function:  _ExcelFontSetColor
; Description:    Set the font color value of a range in an Excel object.
; Syntax:          _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;        $iColorIndex = 1, $hColor = 0x000000)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;    $iColStart - The starting column for the number format(left) (default=1)
;    $iRowEnd - The ending row for the number format (bottom) (default=1)
;    $iColEnd - The ending column for the number format (right) (default=1)
;    $iColorIndex - The font color index (default = 1, if > 254 then $hColor is used instead)
;    $hColor - Hex value of color used when colorindex > 254
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
;     @error=2 - Starting row or column invalid
;      @extended=0 - Starting row invalid
;      @extended=1 - Starting column invalid
;     @error=3 - Ending row or column invalid
;      @extended=0 - Ending row invalid
;      @extended=1 - Ending column invalid
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
  If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
  If $iColStart < 1 Then Return SetError(2, 1, 0)
  If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
  If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
  If $iColorIndex < 255 Then
   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.ColorIndex = $iColorIndex
  Else
   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.Color = $hColor
  EndIf
Else
  If $iColorIndex < 255 Then
   $oExcel.Activesheet.Range($sRangeOrRowStart).Font.ColorIndex = $iColorIndex
  Else
   $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Color = $hColor
  EndIf
EndIf
Return 1
EndFunc ;==>_ExcelFontSetColor
; #FUNCTION# ====================================================================================================
; Function:  _ExcelRowHeightSet
; Description:    Set the row height of the specified row(s).
; Syntax:          _ExcelRowHeightSet($oExcel, $sRow, $vHeight)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $iRow - The integer representation of a valid Excel row (i.e. 45 or a range "1:10")
;    $vHeight - The height of the row in points, or the string "autofit"
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelRowHeightSet($oExcel, $iRow, $vHeight)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If $vHeight = "autofit" Then
  $oExcel.Activesheet.Rows($iRow).Autofit
Else
  $oExcel.Activesheet.Rows($iRow).RowHeight = $vHeight
EndIf
EndFunc ;==>_ExcelRowHeightSet
; #FUNCTION# ====================================================================================================
; Function:  _ExcelVerticalAlignSet
; Description:    Set the vertical alignment of each cell in a range.
; Syntax:          _ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;    $iColStart - The starting column for the number format(left) (default=1)
;    $iRowEnd - The ending row for the number format (bottom) (default=1)
;    $iColEnd - The ending column for the number format (right) (default=1)
;    $sVertAlign - Vertical alignment ("top"|"center"|"bottom") (default="bottom")
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;     @error=1 - Specified object does not exist
;     @error=2 - Starting row or column invalid
;      @extended=0 - Starting row invalid
;      @extended=1 - Starting column invalid
;     @error=3 - Ending row or column invalid
;      @extended=0 - Ending row invalid
;      @extended=1 - Ending column invalid
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
; #FUNCTION# ====================================================================================================
Func _ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
  If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
  If $iColStart < 1 Then Return SetError(2, 1, 0)
  If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
  If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
  Switch ($sVertAlign)
   Case "bottom"
    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).VerticalAlignment = $xlVAlignBottom
   Case "center", "centre"
    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).VerticalAlignment = $xlVAlignCenter
   Case "top"
    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).VerticalAlignment = $xlVAlignTop
  EndSwitch
Else
  Switch ($sVertAlign)
   Case "bottom"
    $oExcel.Activesheet.Range ($sRangeOrRowStart).VerticalAlignment = $xlVAlignBottom
   Case "center", "centre"
    $oExcel.Activesheet.Range ($sRangeOrRowStart).VerticalAlignment = $xlVAlignCenter
   Case "top"
    $oExcel.Activesheet.Range ($sRangeOrRowStart).VerticalAlignment = $xlVAlignTop
  EndSwitch
EndIf
Return 1
EndFunc ;==>_ExcelVerticalAlignSet
#endregion

Edited February 17, 2012 by GreenCan

[GreenCan]

This is a very nice example of a Conditional Line Chart based on a formula (original example can be found on the website of Andy Pope http://www.andypope.info/index.htm)

The sheet contains complex formulas and makes extensive use of Excel function translation and Application.International Property of Excel.

The chart displays a line that changes colour depending on whether it is above or below the control line. It is based on a xy-scatter chart and if you Press F9, the graph will refresh with new values (because of the random numbers in column B.

The example should function in any of these 16 languages:

English (is the base script language) , Czech, Danish, Dutch, French, German, Spanish, Finnish, Hungarian, Italian, Norwegian, Polish, Portuguese, Brazialian Portuguese, Russian, Swedish and Turkish

(requires function_language_lookup.cfg in the script folder, included in zip file)

Please let me know if this would not be the case.

GreenCan

_XLChart_Example_ConditionalFormating.zip

[ptrex]

This example is a Dashboard I use at work.

GreenCan translated this using the powerfull Excel UDF (thanks to Water).

Thanks a both of you,

Rgds,

ptrex

_XLChart_Example - Dashboard.zip

[water]

Version 0.3.0.0 of the UDF has been released.

Have fun testing!

For download please see my signature.

[water]

Version 0.3.1.1 of the UDF has been released.

Have fun testing!

For download please see my signature.

Edited January 21, 2013 by water

[13lack13lade]

I have a question!!!!!

Is it possible to re-create these graphs or say a dashboard of graphs from excel without having them displayed in excel?

For example i am wanting to make an Autoit GUI where they can load the dashboard which is linked to shared spreadsheets that are constantly updated to give a real-time display of the data however not within excel and rather in the Autoit Window itself... is this possible?

*sorry if this is in the wrong section*

Edited August 27, 2013 by 13lack13lade

[water]

Moved to the "General Help & Support" >thread.

[step887]

First off, very nice work on Excel Charts and the provided examples.

I often have to parse poolmon logs to find memory leaks and if you have read the logs, they are not easiest to go through.

http://support.microsoft.com/kb/177415

So I parse the data and use charts to make nice little line charts. See attached file screen shot of top 25 line chart. It also does 25 individual line charts for the top 25..

the script can take single or multiple files from command line switch (or drag a file on the icon) or it will pop up file open dialog if there is no command line.

expandcollapse popup

#include <array.au3>
#include <excel.au3>
#include <Excelchart.au3>
Global $flarr[100][2] = [[0]], $rec[100] = [0], $index, $arr[100] = [0], $count, $bad = True, $file_log, $farr, $x_time, $cmdlineused
If $cmdline[0] = 1 And FileExists($cmdlineraw) Then;one file
    $file_log = $cmdlineraw
ElseIf $cmdline[0] > 1 Then
    For $i = 1 To $cmdline[0]
        $file_log &= $cmdline[$i] & '|'
    Next
    $file_log = StringTrimRight($file_log, 1)
    $cmdlineused = True
Else
    $file_log = FileOpenDialog('Choose poolmon log', @ScriptDir, "All Files (*.*)", 1 + 4)
EndIf
If Not $file_log Then Exit
If StringRegExp($file_log, '\|') Then
    Local $z = 2, $full
    $farr = StringSplit($file_log, '|')
    If $cmdlineused Then $z = 1
    For $i = $z To $farr[0]
        If $cmdlineused Then
            $full = $farr[$i]
        Else
            $full = $farr[1] & '\' & $farr[$i]
        EndIf
        If FileExists($full) Then
            _ArrAdd($flarr, StringTrimLeft(FileGetTime($full, 1, 1), 8) & '|' & $full, 2)
        EndIf
    Next
    ReDim $flarr[$flarr[0][0] + 1][2]
    _ArraySort($flarr, 0, 1)
    For $i = 1 To $flarr[0][0]
        _file_to_arr($flarr[$i][1], $rec)
        $x_time &= $flarr[$i][0] & '|'
    Next
    $x_time = StringTrimRight($x_time, 1)
    $x_time = StringSplit($x_time, '|', 2)
Else
    _file_to_arr($file_log, $rec)
EndIf
ProgressOn($file_log, "Parsing Log", '', Default, Default, 18)
For $i = 1 To $rec[0]
    $temp = StringRegExp($rec[$i], '([A-z0-9 _]{4} [A-z]{4})\s+[0-9]+\s+[0-9]+\s+[0-9]+\s+([0-9]+)', 1)
    If @error = 0 Then
        $out = StringRegExp($index, $temp[0] & '\|(\d*)\|', 1)
        If @error = 0 Then
            $arr[$out[0]] &= '|' & $temp[1]
        Else
            $index &= $temp[0] & '|' & $arr[0] + 1 & '|'
            _ArrAdd($arr, $temp[0] & '|' & $temp[1])
        EndIf
    EndIf
    ProgressSet(($i / $rec[0]) * 24)
Next
If $rec[0] = 0 Then
    ProgressOff()
    MsgBox(16, @ScriptName, 'No Data to import. Clck ok to exit.')
    Exit
EndIf
ProgressSet(($i / $rec[0]) * 24, '', 'Creating Excel file')
$rec = 0
Local $oExcel = _ExcelBookNew(0)
If @error Then
    MsgBox(16, @ScriptName, 'Error: Unable to create the Excel COM object.')
    Exit
EndIf
_ExcelSheetAddNew($oExcel, 'Top 25')
_ExcelSheetActivate($oExcel, 2)
_ExcelSheetNameSet($oExcel, "Top Charts")
_ExcelSheetActivate($oExcel, 3)
_ExcelSheetNameSet($oExcel, "Data")
Local $top
For $i = 1 To $arr[0]
    $tarr = StringSplit($arr[$i], '|', 2)
    _ExcelWriteArray($oExcel, $i, 3, $tarr)
    $num = UBound($tarr)
    If $top < $num Then $top = $num
    _ExcelWriteFormula($oExcel, '=R' & $i & 'C' & $num + 2 & '-R' & $i & 'C4', $i, 1) ;Uses R1C1 referencing
    _ExcelWriteFormula($oExcel, '=STDEV.S(R' & $i & 'C4:R' & $i & 'C' & $num + 2, $i, 2) ;Uses R1C1 referencing
    $t = _ExcelReadCell($oExcel, $i, 1)
    _ExcelWriteCell($oExcel, $t, $i, 1)
    $t = _ExcelReadCell($oExcel, $i, 2)
    _ExcelWriteCell($oExcel, $t, $i, 2)
    ProgressSet(($i / $arr[0]) * 75 + 24)
Next
ProgressSet(100, '', 'Creating Charts.')
$oExcel.ActiveSheet.UsedRange.Sort($oExcel.ActiveSheet.Range("A1"), 2, $oExcel.ActiveSheet.Range("B1"), Default, 2)
_ExcelRowInsert($oExcel, 1)
_ExcelWriteCell($oExcel, 'End-Start', 1, 1)
_ExcelWriteCell($oExcel, 'Standard Deviation', 1, 2)
_ExcelWriteCell($oExcel, 'Tag', 1, 3)
If IsArray($x_time) Then
    _ExcelWriteArray($oExcel, 1, 4, $x_time)
Else
    For $i = 4 To $top + 2
        _ExcelWriteCell($oExcel, $i - 3, 1, $i)
    Next
EndIf

Local $XValueRange = '=Data!R1C4:R1' & 'C' & $top + 2
Local $asDataRange[26] = [25], $asDataName[26] = [25]
For $i = 1 To 25
    $asDataRange[$i] = '=Data!R' & $i + 1 & 'C4:R' & $i + 1 & 'C' & $top + 2
    $asDataName[$i] = '=Data!C' & $i + 1
Next
_ExcelSheetActivate($oExcel, 1)
_XLChart_ChartCreate($oExcel, 1, 4, 'A1:S40', 'Top 25', $XValueRange, $asDataRange, $asDataName, True, 'Top 25')
Local $r1, $r2, $c = 1
For $i = 1 To $asDataRange[0]
    $mod = Mod($i, 4)
    Switch $mod
        Case 1
            $r = 'A' & $c & ':F' & $c + 13
        Case 2
            $r = 'G' & $c & ':L' & $c + 13
        Case 3
            $r = 'M' & $c & ':R' & $c + 13
        Case 0
            $r = 'S' & $c & ':X' & $c + 13
            $c += 14
    EndSwitch
    $out = _XLChart_ChartCreate($oExcel, 2, $xlLineMarkers, $r, $asDataName[$i], $XValueRange, $asDataRange[$i], $asDataName[$i], False, $asDataName[$i])
Next
ProgressOff()
Local $test = False
Do
    $fout = FileSaveDialog('Output File,', @ScriptDir, "Excel files (*.xls)")
    If FileExists($fout) Then
        $mout = MsgBox(36, 'Overwriite', $fout & ' already exist. Overwrite?')
        If $mout = 6 Then
            $dout = FileDelete($fout)
            If $dout = 0 Then
                MsgBox(0, $fout, 'Failed to save ' & $fout & '.' & @CRLF & 'Please close excel or choose a different filename.')
            Else
                $test = True
            EndIf
        EndIf
    Else
        $test = True
    EndIf
Until $test = True
If $fout <> 1 Then _ExcelBookSaveAs($oExcel, $fout)
$oExcel.Application.Visible = 1


Func _file_to_arr($name, ByRef $arr)
    Local $file = FileOpen($name, 0)

    ; Check if file opened for reading OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open " & $name)
        Exit
    EndIf

    ; Read in lines of text until the EOF is reached
    While 1
        Local $line = FileReadLine($file)
        If @error = -1 Then ExitLoop
        _ArrAdd($rec, $line)
    WEnd

    FileClose($file)
EndFunc   ;==>_file_to_arr

Func _ArrAdd(ByRef $array, $data, $dem = False, $delimt = '|')
    If $dem Then
        Local $t = StringSplit($data, $delimt)
        If @error = 0 Then
            $array[0][0] += 1
            If UBound($array) = $array[0][0] Then ReDim $array[$array[0][0] * 2][UBound($array, 2)]
            For $f = 1 To $t[0]
                $array[$array[0][0]][$f - 1] = $t[$f]
            Next
        EndIf
    Else
        $array[0] += 1
        If UBound($array) = $array[0] Then ReDim $array[$array[0] * 2]
        $array[$array[0]] = $data
    EndIf
EndFunc   ;==>_ArrAdd 

Autoit v3.3.8.1

Excel 2010

 

 

[water]

Glad you like the UDF

Thanks for the example. So people can see how useful the UDF can be to make life easier.

N.B.: If performance is an issue with your script I suggest to have a look at my rewrite of the Excel UDF. Reading/writing is much, much faster (20 to 100 times).

[step887]

So I took your advice and used your excel rewrite

the poolmon file I was using was 10 megs.

The difference was impressive.. 

38.227 secs with your excel UDF

250.344 secs with the built in Excel

Thanks,

below is the updated code I used.. 

expandcollapse popup

#include <array.au3>
#include <excel.au3>
#include <Excel Rewrite.au3>
#include <Excelchart.au3>

Global $flarr[100][2] = [[0]], $rec[100] = [0], $index, $cindex[100] = [0],$arr[100][10] = [[0]], $count, $bad = True, $file_log, $farr, $x_time, $cmdlineused
If $cmdline[0] = 1 And FileExists($cmdlineraw) Then;one file
    $file_log = $cmdlineraw
ElseIf $cmdline[0] > 1 Then
    For $i = 1 To $cmdline[0]
        $file_log &= $cmdline[$i] & '|'
    Next
    $file_log = StringTrimRight($file_log, 1)
    $cmdlineused = True
Else
    $file_log = FileOpenDialog('Choose poolmon log', @ScriptDir, "All Files (*.*)", 1 + 4)
EndIf
If Not $file_log Then Exit
If StringRegExp($file_log, '\|') Then
    Local $z = 2, $full
    $farr = StringSplit($file_log, '|')
    If $cmdlineused Then $z = 1
    For $i = $z To $farr[0]
        If $cmdlineused Then
            $full = $farr[$i]
        Else
            $full = $farr[1] & '\' & $farr[$i]
        EndIf
        If FileExists($full) Then
            _ArrAdd($flarr, StringTrimLeft(FileGetTime($full, 1, 1), 8) & '|' & $full, 2)
        EndIf
    Next
    ReDim $flarr[$flarr[0][0] + 1][2]
    _ArraySort($flarr, 0, 1)
    For $i = 1 To $flarr[0][0]
        _file_to_arr($flarr[$i][1], $rec)
        $x_time &= $flarr[$i][0] & '|'
    Next
    $x_time = StringTrimRight($x_time, 1)
    $x_time = StringSplit($x_time, '|', 2)
Else
    $rec = FileReadToArray($file_log)

EndIf
$timer = TimerInit()
ProgressOn($file_log, "Parsing Log", '', Default, Default, 18)
Local $num = 0, $total = Ubound($rec)-1,$top
For $i = 1 To  $total

    $temp = StringRegExp($rec[$i], '([A-z0-9 _]{4} [A-z]{4})\s+[0-9]+\s+[0-9]+\s+[0-9]+\s+([0-9]+)', 1)
    If @error = 0 Then
        $out = StringRegExp($index, $temp[0] & '\|(\d*)\|', 1)
        If @error = 0 Then

            $cindex[$out[0]] += 1
            If $top < $cindex[$out[0]] Then $top = $cindex[$out[0]]
            _ArrAdd($arr,$temp[1],Default,$out[0],$cindex[$out[0]])
        Else
            $index &= $temp[0] & '|' & $arr[0][0] + 1 & '|'
            _ArrAdd($cindex, 1)
            _ArrAdd($arr, $temp[0] & '|' & $temp[1], '|')
        EndIf
    EndIf
    ProgressSet(($i / $total) * 24)

Next
;~ _ArrayDisplay($arr)
If $arr[0][0] = 0 Then
    ProgressOff()
    MsgBox(16, @ScriptName, 'No Data to import. Clck ok to exit.')
    Exit
EndIf
ProgressSet(($i / $total) * 24, '', 'Creating Excel file')
$rec = 0
;~ Local $oExcel = _ExcelBookNew(0)
Local $oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel,1)
If @error Then
    MsgBox(16, @ScriptName, 'Error: Unable to create the Excel COM object.')
    Exit
EndIf
;~ _ExcelSheetActivate($oExcel,1)
;~ _ExcelSheetNameSet($oExcel, 'Top 25')
;~ _ExcelSheetActivate($oExcel, 2)
_ExcelSheetNameSet($oExcel, "Top Charts")
_ExcelSheetActivate($oExcel, 2)
_ExcelSheetNameSet($oExcel, "Data")
Local $tarr [1][2] = [[0]]
;~ For $i = 1 To $arr[0]
;~  $tarr = StringSplit($arr[$i], '|', 2)
    _Excel_RangeWrite($oWorkbook, 'Data', $arr, "C2")
    For $i = 1 to $arr[0][0]
        $c = _Excel_ColumnToLetter($cindex[$i]+3)
        _Excel_RangeWrite($oWorkbook,'Data','=' & $c & $i & '-D' & $i, 'A' & $i, False)
        _Excel_RangeWrite($oWorkbook,'Data','=STDEV.S(D' & $i & ':' &  $c & $i, 'B' & $i, False)
        ProgressSet(($i / $arr[0][0]) * 75 + 24)
    Next

;~  _ExcelWriteArray($oExcel, $i, 3, $tarr)
;~  $num = UBound($tarr)
;~  If $top < $num Then $top = $num
;~  _ExcelWriteFormula($oExcel, '=R' & $i & 'C' & $num + 2 & '-R' & $i & 'C4', $i, 1) ;Uses R1C1 referencing
;~  _ExcelWriteFormula($oExcel, '=STDEV.S(R' & $i & 'C4:R' & $i & 'C' & $num + 2, $i, 2) ;Uses R1C1 referencing
;~  $t = _ExcelReadCell($oExcel, $i, 1)
;~  _ExcelWriteCell($oExcel, $t, $i, 1)
;~  $t = _ExcelReadCell($oExcel, $i, 2)
;~  _ExcelWriteCell($oExcel, $t, $i, 2)
;~  ProgressSet(($i / $arr[0]) * 75 + 24)
;~ Next
;~ ProgressSet(100, '', 'Creating Charts.')
$oExcel.ActiveSheet.UsedRange.Sort($oExcel.ActiveSheet.Range("A1"), 2, $oExcel.ActiveSheet.Range("B1"), Default, 2)
_ExcelRowInsert($oExcel, 1)
_Excel_RangeWrite($oWorkbook, 'Data','End-Start', 'A1')
_Excel_RangeWrite($oWorkbook, 'Data', 'Standard Deviation', 'B1')
_Excel_RangeWrite($oWorkbook, 'Data','Tag', 'C1')
;~ _ExcelWriteCell($oExcel, 'Standard Deviation', 1, 2)
;~ _ExcelWriteCell($oExcel, 'Tag', 1, 3)
If IsArray($x_time) Then
    _Excel_RangeWrite($oWorkbook, 'Data', $x_time, 'D1:' & _Excel_ColumnToLetter($top) & '1')
Else
    Local $label[1][$top + 3]
    For $i = 1 To $top
        _ArrAdd($label, $i,Default, 0, $i-1)

    Next
    _Excel_RangeWrite($oWorkbook, 'Data',$label, 'D1')
EndIf

Local $XValueRange = '=Data!R1C4:R1' & 'C' & $top + 2
Local $asDataRange[26] = [25], $asDataName[26] = [25]
For $i = 1 To 25
    $asDataRange[$i] = '=Data!R' & $i + 1 & 'C4:R' & $i + 1 & 'C' & $top + 2
    $asDataName[$i] = '=Data!C' & $i + 1
Next
_ExcelSheetActivate($oExcel, 2)
_XLChart_ChartCreate($oExcel, 1, 4, 0, 'Top 25', $XValueRange, $asDataRange, $asDataName, True, 'Top 25')
Local $r1, $r2, $c = 1
For $i = 1 To $asDataRange[0]
    $mod = Mod($i, 4)
    Switch $mod
        Case 1
            $r = 'A' & $c & ':F' & $c + 13
        Case 2
            $r = 'G' & $c & ':L' & $c + 13
        Case 3
            $r = 'M' & $c & ':R' & $c + 13
        Case 0
            $r = 'S' & $c & ':X' & $c + 13
            $c += 14
    EndSwitch
    $out = _XLChart_ChartCreate($oExcel, 2, 4, $r, $asDataName[$i], $XValueRange, $asDataRange[$i], $asDataName[$i], False, $asDataName[$i])
Next
ProgressOff()
MsgBox(0,'',TimerDiff($timer))
Local $test = False
Do
    $fout = FileSaveDialog('Output File,', @ScriptDir, "Excel files (*.xls)")
    If FileExists($fout) Then
        $mout = MsgBox(36, 'Overwriite', $fout & ' already exist. Overwrite?')
        If $mout = 6 Then
            $dout = FileDelete($fout)
            If $dout = 0 Then
                MsgBox(0, $fout, 'Failed to save ' & $fout & '.' & @CRLF & 'Please close excel or choose a different filename.')
            Else
                $test = True
            EndIf
        EndIf
    Else
        $test = True
    EndIf
Until $test = True
If $fout <> 1 Then _ExcelBookSaveAs($oExcel, $fout)
$oExcel.Application.Visible = 1


Func _file_to_arr($name, ByRef $arr)
    Local $file = FileOpen($name, 0)

    ; Check if file opened for reading OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open " & $name)
        Exit
    EndIf

    ; Read in lines of text until the EOF is reached
    While 1
        Local $line = FileReadLine($file)
        If @error = -1 Then ExitLoop
        _ArrAdd($rec, $line)
    WEnd

    FileClose($file)
EndFunc   ;==>_file_to_arr


Func _ArrAdd(ByRef $array, $data, $delimt = False, $row = -1, $col = -1)
    Select
        Case $delimt
            Local $t = StringSplit($data, $delimt)
            If @error = 0 Then
                $acol = Ubound($array,2)
                $arow = Ubound($array)
                If $t[0] >= $acol Then
                    $acol = $t[0] * 2
                    ReDim $array [$arow][$acol]
                EndIf
                $array[0][0] += 1
                If $arow = $array[0][0] Then ReDim $array[$arow * 2][$acol]
                For $f = 1 To $t[0]
                    $array[$array[0][0]][$f - 1] = $t[$f]
                Next
            EndIf
        Case $col > -1 And $row > -1
            $acol = Ubound($array,2)
            $arow = Ubound($array)
;~          ConsoleWrite ($arow & ':' & $row  & @tab & $acol & ':' & $col & @CRLF)
            If $col >= $acol Then Redim $array[$arow][$col *2]
            If $row >= $arow Then ReDim $array[$row * 2][$acol]
            $array[$row][$col] = $data
        Case  $delimt = False And $row = -1 And $col = -1
            $array[0] += 1
            If UBound($array) = $array[0] Then ReDim $array[$array[0] * 2]
            $array[$array[0]] = $data
        EndSelect
EndFunc   ;==>_ArrAdd

Autoit 3.3.9.22

[water]

Thanks for the update!

[water]

Version 0.4.0.0 of the UDF has been released. Only changes made are to make it run with Autoit 3.3.12.0!

Have fun testing!

For download please see my signature.