How to set characters font in excel by m-file?

Hi all

In VBA, for one cell, I can use 'ActiveCell.Characters(Start:=2, Length:=2).Font.Color = -16776961' to set 2nd and 3rd Characters font color to red.

Now, I want to do the same thing by m-file. But code like this

Worksheet.Range('A1').Characters('Start:=2, Length:=2').Font.ColorIndex = 3; %red

told me index overflow, I guess 'Start:=2, Length:=2' is wrong way to do this. What should I do? Thanks.

[Walter Roberson]

I suggest experimenting with

Worksheet.Range('A1').Characters(2, 2).Font.ColorIndex = 3; %red

[kei hin]

@Walter Roberson It doesn't work. Same error.

[Steven Lord]

Can you show us the code you used to create the variable named Worksheet?

[kei hin]

@Steven Lord

xlswrite([pwd,'\color_code.xlsx'],{'abcdefg'});

Excel = actxserver('Excel.Application');
Excel.Visible = 1; %visible 1
Workbook = Excel.Workbooks.Open([pwd,'\color_code.xlsx']);  %open excel
Worksheet = Workbook.Sheets.Item(1);  %1st sheet in excel
Worksheet.Range('A1').Interior.ColorIndex = 4; %background color green, OK
Worksheet.Range('A1').font.ColorIndex = 5; %font color blue, OK
Worksheet.Range('A1').Characters('Start:=2, Length:=2').Font.ColorIndex = 3; % 2nd and 3rd characters color red, NG

[kei hin]

Any idea?

Answer 1

[dpb]

Worksheet.Range('A1').Characters('Start:=2, Length:=2').Font.ColorIndex = 3; % 2nd and 3rd characters color red, NG

MATLAB doesn't have access to VBA to convert the argument name references to the substring to address -- but the VBA syntax wouldn' t be quoted string but written as

Worksheet.Range('A1').Characters(Start:=2, Length:=2).Font.ColorIndex = 3;

That, as I'm sure you've already discovered doesn't even parse in MATLAB because Start and Length would be interpreted as variables.

The text expression there will have to be replaced with a set of argument values by position, the use of named arguments simply isn't available when writing COM code; part of you having to substitute as the VBA compiler. In addition, (probably?) the expression will have to be broken down to return a handle to that substring object and then apply the properties to that object.

I've never messed with such detail within a cell string so the utilities I have don't have this facility built into them to go get actual implementation, but there's where to start...

ADDENDUM:

<The VBA characters object doc> (*) shows (start,length) are the two positional arguments, so try

startpos=2;
numchars=2;
Worksheet.Range('A1').Characters(startpos,numchars).Font.ColorIndex = 3;

first. Sometimes nested operations don't work with COM directly, either, but that's the first thing to try, just may get lucky.

(*) expression.Characters where expression is a range is actually a property, not a method, so the two arguments are as indexing values; the named parameters aren't available in VBA here, either.

ADDENDUM SECOND:

As noted above, often you can't use VBA syntax with COM because there's a lot that goes on behind the scenes in the compiler that COM/MATLAB simply doesn't have access to. This is one of those cases...

The following worked here, you may be able to streamline it a little, but the key item is you must invoke the Characters method to get the subset object.

Workbook = excel.Workbooks.Open(fullfile(pwd,'Book1.xlsx'));
WorkSheet=Workbook.ActiveSheet;
Range=WorkSheet.Range('A1');
Chars=invoke(Range,'Characters',2,2);
Chars.Font.ColorIndex=3;

Also NOTA BENE: <'ColorIndex' to use the numeric index>; what "3" correlates to will depend upon what the current color palette is; apparently "3" is red in the default, but I'm sure it can be changed by the user and you may get something else if so. I quit at that point; I "know nuthink!" about Excel internals in this area.

[dpb]

>> invoke(WorkSheet.Range('A1'),'Characters',2,2).Font.ColorIndex=3
Unable to use a value of type Interface.00020846_0000_0000_C000_000000000046 as an index. 
>> 

Can't string it all together; it can't use the dynamic return from invoke() and the dot notation for properties.

But, can, if choose to do so, use the range method as the object as an argument to invoke() -- more than likely you'll want/need the Range object for other purposes besides, however, so it probably is only of academic rather than practical interest--

>> Chars=invoke(WorkSheet.Range('A1'),'Characters',2,2)
Chars =
	Interface.00020878_0000_0000_C000_000000000046
>> Chars.Font.ColorIndex=3;
>>