8 replies to this topic

[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

Attached Thumbnails

• 

Attached Files

•  _XLChart_Saddle.au3   39.52K   374 downloads

[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

Attached Thumbnails

• 

Attached Files

•  _XLChart_Example_Thermometer Chart v3.0.zip   8.81K   311 downloads

Edited by GreenCan, 17 February 2012 - 11:34 AM.

[GreenCan]

Updated version of Gauge Chart (requires ExcelChart v 0.2.2.0)

_XLChart_Example_Gauge Chart.au3

AutoIt        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

Attached Thumbnails

• 

Edited by GreenCan, 17 February 2012 - 05:47 PM.

[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

Attached Thumbnails

• 

Attached Files

•  _XLChart_Example_ConditionalFormating.zip   45.96K   238 downloads

[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

Attached Thumbnails

• 

Attached Files

•  _XLChart_Example - Dashboard.zip   24.95K   298 downloads

[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 by water, 21 January 2013 - 03:15 PM.