excel set iconsets arrows for trend

[frank10]

I want to display the three arrows for trends: up, equal, down arrows on a column with values <0, 0, >0

 

I found a script that uses iconsets but it's giving errors:

I did:

Local $xlConditionValueNumber = 0
local $xl3Arrows = 1
local $oIconSet = $oExcel.ActiveWorkbook.Sheets("Graphs").Range("F1:F100").FormatConditions.AddIconSetCondition
$oIconSet.IconSet = $oWB.IconSets($xl3Arrows)
;~ $oIconSet.ReverseOrder = False
;~ $oIconSet.ShowIconOnly = False 

With $oIconSet.IconCriteria(1)
    ConsoleWrite($oIconSet.IconCriteria(1).Type & @CRLF)
    .Type = $xlConditionValueNumber
EndWith

And it can read the current  .Type value, BUT it gives error if I try to modify it...

err.number is:      0x80020009
    err.windescription: Eccezione.

    err.description is:     
    err.source is:      
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  55
    err.retcode is:     0x800A03EC

It's the same with .Value

I can only change the IconCriteria(2) and (3), no changes to (0) or (1).

So this works:

$oIconCriterion = $oIconSet.IconCriteria(2)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 0
$oIconCriterion.Operator = $xlGreater
$oIconCriterion = $oIconSet.IconCriteria(3)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 0
$oIconCriterion.Operator = $xlGreaterEqual

But it displays only down and up arrows.

So, how can I modify the IconCriteria to fit my arrows schema, (in particular to display the equal arrow for 0 values)?

 

Thi is a full example script:

 

expandcollapse popup

#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>
#include-once
; #include <ExcelChart.au3>

Local $oError = ObjEvent("AutoIt.Error", "_ErrFunc")
Global Const $xlDouble = -4119 ; Double line.

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "-1", "F1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "0", "F2")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "1", "F3")

Local $xl3Arrows = 1
Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

$oIconSet = $oExcel.ActiveWorkbook.Sheets(1).Range("F1:F3").FormatConditions.AddIconSetCondition
$oIconSet.IconSet = $oExcel.ActiveWorkbook.IconSets($xl3Arrows)
ConsoleWrite( $xlEqual & "_" & $xlGreater & "_" & $xlGreaterEqual  & @CRLF)

;~ $oIconCriterion = $oIconSet.IconCriteria(0)
;~ $oIconCriterion.Type = $xlConditionValueNumber
;~ $oIconCriterion.Value = 20
;~ $oIconCriterion.Operator = $xlGreater
;~ $oIconCriterion = $oIconSet.IconCriteria(1)  ; NO modifica 0 o 1!!!   Percent(val=3)  >= (val=7) 0
;~ $oIconCriterion.Type = $xlConditionValueNumber
;~ $oIconCriterion.Value = 1
;~ $oIconCriterion.Operator = $xlGreaterEqual
$oIconCriterion = $oIconSet.IconCriteria(2)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 0
$oIconCriterion.Operator = $xlGreater
$oIconCriterion = $oIconSet.IconCriteria(3)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 0
$oIconCriterion.Operator = $xlGreaterEqual


; Retrieve information about each IconCriteria
For $oIconCriterion In $oIconSet.IconCriteria
    ConsoleWrite($oIconCriterion.Type & "-" & $oIconCriterion.Value & "-" & $oIconCriterion.Operator & @CRLF)
Next

Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

Edited November 11, 2020 by frank10

[GokAy]

Hey Frank,

Never used iconsets, but searched a little and IconCriteria's start from 1. And IconCriteria(1) seems to be not assignable, maybe a default empty one?

So try using 2,3 and 4. Also, check this one out:

https://stackoverflow.com/questions/57182771/how-to-customize-icons-for-conditions

[frank10]

Thank you.

Yes, 1 cannot be modified, but neither 4.

I was misleaded by thinking I must modify all 3 values.

Instead I changed values a bit on 2 and 3 and I got it!

$oIconCriterion = $oIconSet.IconCriteria(2)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 0
$oIconCriterion.Operator = $xlLess
$oIconCriterion = $oIconSet.IconCriteria(3)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 1
$oIconCriterion.Operator = $xlGreaterEqual