Delphi and Excel.FormatConditions

Question

I am having problems setting up Conditional Formatting from Delphi XE2 using Early binding with Excel 2010

The Macro I am trying to reproduce is as follows:

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Try as I might I cannot seem to access the equivalent of Selction.FormatConditions(1) to work

The closest I have reached is with the following code:

XR := Xlapp.Range(...) 
XR.FormatConditions.Delete;
XR.FormatConditions.Add(xlCellValue, xlGreater, '=6', EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam);

Which works. When I try and define the colours I have problems

FC := XR.FormatConditions[1];
FC.SetFirstPriority;
with FC.Interior do
begin
   PatternColorIndex := xlAutomatic;
   ThemeColor := xlThemeColorAccent6;
end;

However this keeps telling me that XR.FormatConditions(1) is and IDispatch and thus incompatible with the FormatCondition assignment

What am I doing wrong?

Could you make it clear what the error is. Runtime or compile time? Which line. And what is the precise error message. Use copy/paste to give us that. Select the text of the message and then press CTRL and C together. That copies it to the clipboard. Then edit the question and press CTRL and V together. That pastes. — David Heffernan, Feb 07 '13 at 19:38

Ken White · Accepted Answer · 2014-04-08T02:05:50.343

5

You need to use Selection as an ExcelRange. Excel XP also requires the second and third parameter to be OleVariant, so this should work (it compiles, anyway):

var
  Sel: ExcelRange;
  Op, Formula: OleVariant;
  Condition: FormatCondition;
begin
  Sel := ExcelApplication1.Selection[1] as ExcelRange;
  Op := xlGreater;
  Formula := '=6';
  Sel.FormatConditions.Add(xlCellValue, Op, Formula, EmptyParam);
  Condition := Sel.FormatConditions[1] as FormatCondition;
  Condition.Interior.PatternColorIndex := xlAutomatic;
  // Do whatever else
end;

edited Apr 08 '14 at 02:05

answered Feb 07 '13 at 18:14

Ken White

123,280
14
225
444

XR is an ExcelRange. The .Add compiles, it's the nextblinesvthatvareva problem. – Dan Kelly Feb 07 '13 at 19:05
1

I thought you could extrapolate from the `as ExcelRange` example. I've added another one with `Sel.FormatConditions[0] as FormatCondition;`. It's the same concept - retrieve the actual interface needed by using the `as` operator on an `IDispatch`. – Ken White Feb 07 '13 at 19:32
I missed the "as" in your example. Forcing the cast does what I need – Dan Kelly Feb 08 '13 at 09:12
1

Just to clarify, Dan: That isn't "forcing the cast". It's the proper way to request an actual interface from an IDispatch - Delphi allows that syntax, but the compiler is actually using `QueryInterface` behind the scenes to ask for the desired interface, and if it's not available you won't get one back. It's not like typecasting. :-) – Ken White Feb 08 '13 at 11:56
I had to use Sel.FormatConditions[1] as the index was not zero based – sav Apr 08 '14 at 01:31

Delphi and Excel.FormatConditions

1 Answers1