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

10 次查看（过去 30 天）
kei hin
kei hin 2022-7-13
评论： dpb 2023-9-7
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.
  5 个评论
显示 3更早的评论
kei hin
kei hin 2022-7-19
编辑：kei hin 2022-7-19
@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

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

dpb
dpb 2023-9-6
编辑：dpb 2023-9-6
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.
  1 个评论
dpb
dpb 2023-9-7
>> 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;
>>

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Startup and Shutdown 的更多信息

标签

产品


版本

R2017b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by