从Excel VBA的Substring着色:为什么一些明显的方法不起作用? [英] Substring colouring from Excel VBA: why do some obvious methods not work?
问题描述
rCell.Characters(start,end).Font.Color = SomeColour
我的应用程序通过将新字符串附加到现有值上,然后设置新字符串的颜色,构建字符串并在一个步骤中设置颜色值。这没有办法。从一个完整的字符串开始,然后着色多个子字符串 工作。
两个简单的例程说明了区别:
Sub TestColourString1()
'旨在显示子字符串颜色可以预先存在的字符串
Dim rngTestString As Range
设置rngTestString =范围(colour_string)
rngTestString.Value =red green blue
rngTestString.Characters(1,4)。颜色= 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()
'显示在构建字符串不工作时的设置颜色
Dim rngTestString As Range
设置rngTestString = Range(colour_string)
rngTestString.Value =红色
rngTestString.Charact ers(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
这两个例程导致以下两个不同的结果:
对于具有更多子段的更长的字符串,更糟糕的是。我正在使用Excel 2010。
这是我的错,还是一个错误?有没有更好的方法来创建和绘制VBA中的字符串?
分配 .Value
并不奇怪,如何追加到现有的数据。它会删除旧数据并输入新的数据。
如果字符具有着色,则使用第一个字符的颜色来对新字符串进行着色。 >
如果你想要实际的附加,就像在Excel中手动使用公式栏一样,然后使用 .Characters
附加:
Dim rngTestString As Range
设置rngTestString =范围(colour_string)
范围(colour_string)。字符(Len(范围(colour_string)。值)+ 1).Text =red
rngTestString.Characters(1,4).Font.Color = RGB (255,0,0)
范围(colour_string)。字符(Len(Range(colour_string)。Value)+ 1).Text =green
rngTestString。字符(5,10).Font.Color = RGB(0,255,0)
范围(colour_string)字符(Len(Range(colour_string)。Value)+ 1) .Text =blue
rngTestString.Characters(11,14).Font.Color = RGB(0,0,255)
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
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 does work.
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.
So is this my fault or is it a bug? Is there a better way to create and colour strings from VBA?
Assigning the .Value
does not magically figure how to append to the existing data. It erases the old data and puts in the new data.
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
:
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)
这篇关于从Excel VBA的Substring着色:为什么一些明显的方法不起作用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!