Excel VBA 中的子字符串着色：为什么一些明显的方法不起作用？

Substring colouring from Excel VBA: why do some obvious methods not work?

提问by matt_black

I've been building some interesting visualizations that rely on VBA code's ability to set different colours for substrings in Excel. For a cell containing a string the syntax works like this rCell.Characters(start,end).Font.Color=SomeColour

我一直在构建一些有趣的可视化，这些可视化依赖于 VBA 代码为 Excel 中的子字符串设置不同颜色的能力。对于包含字符串的单元格，语法如下所示rCell.Characters(start,end).Font.Color=SomeColour

My application builds the strings and sets the colour values in one step by appending new strings onto the existing values and then setting the colour of the new string. This didn't work. Starting with a complete string and then colouring multiple sub-strings doeswork.

我的应用程序通过将新字符串附加到现有值上，然后设置新字符串的颜色，一步构建字符串并设置颜色值。这没有用。从一个完整的字符串开始，然后为多个子字符串着色确实有效。

Two simple routines illustrate the difference:

两个简单的例程说明了差异：

    Sub TestColourString1()
    'designed to show that substring colour can be done to preexisting string
    Dim rngTestString As Range

    Set rngTestString = Range("colour_string")

    rngTestString.Value = "red green blue"

    rngTestString.Characters(1, 4).Font.Color = RGB(255, 0, 0)
    rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0)
    rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255)

    End Sub




    Sub TestColourString2()
    'designed to show that setting colour while building string doesn't work
    Dim rngTestString As Range

    Set rngTestString = Range("colour_string")

    rngTestString.Value = "red "
    rngTestString.Characters(1, 4).Font.Color = RGB(255, 0, 0)

    rngTestString.Value = rngTestString.Value & "green "
    rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0)

    rngTestString.Value = rngTestString.Value & "blue"
    rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255)


    End Sub

The two routines result in the two different results shown below:

这两个例程会产生如下所示的两种不同结果：

For longer strings with more subsegments it is even worse. I'm using Excel 2010.

对于具有更多子段的较长字符串，情况更糟。我正在使用 Excel 2010。

So is this my fault or is it a bug? Is there a better way to create and colour strings from VBA?

那么这是我的错还是错误？有没有更好的方法从 VBA 创建和着色字符串？

回答by GSerg

Assigning the .Valuedoes not magically figure how to append to the existing data. It erases the old data and puts in the new data.

分配.Value并不能神奇地说明如何附加到现有数据。它擦除旧数据并放入新数据。

If the characters had colouring, the colour of the first character is used to colour the new string.

如果字符有颜色，则使用第一个字符的颜色为新字符串着色。

If you want the actual appending, same as if you manually used the formula bar in Excel, then append using .Characters:

如果你想要实际的追加，就像你在 Excel 中手动使用公式栏一样，然后使用.Characters：

Dim rngTestString As Range

Set rngTestString = Range("colour_string")

Range("colour_string").Characters(Len(Range("colour_string").Value) + 1).Text = "red "
rngTestString.Characters(1, 4).Font.Color = RGB(255, 0, 0)

Range("colour_string").Characters(Len(Range("colour_string").Value) + 1).Text = "green "
rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0)

Range("colour_string").Characters(Len(Range("colour_string").Value) + 1).Text = "blue"
rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255)

回答by István Hirsch

Formatting (including coloring) substrigns in a range of excel cells with a macro, see the video:

使用宏在一系列 excel 单元格中格式化（包括着色）子字符串，请参阅视频：

http://youtu.be/O0h6T5Z7HwY

http://youtu.be/O0h6T5Z7HwY