frank10 Posted November 11, 2020 Share Posted November 11, 2020 (edited) 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 Link to comment Share on other sites More sharing options...
GokAy Posted November 11, 2020 Share Posted November 11, 2020 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 Link to comment Share on other sites More sharing options...
frank10 Posted November 11, 2020 Author Share Posted November 11, 2020 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now