How to set characters font in excel by m-file?
23 次查看（过去 30 天）
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.
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...
<The VBA characters object doc> (*) shows (start,length) are the two positional arguments, so try
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.
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'));
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.