vba 使用公式格式化 Excel 单元格中的文本子集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15129549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format a subset of text in Excel Cell Using Formula
提问by Hightower
I Have built a string using a formula in excel. as an example
我已经使用excel中的公式构建了一个字符串。举个例子
Cell C3 contains text "Languages"
Cell C4 = "English, Spanish,German, French"
My Forumla = C3 & ":" & CHAR(10) & C4
单元格 C3 包含文本“语言”
单元格 C4 =“英语、西班牙语、德语、法语”
我的论坛 = C3 & “:” & CHAR(10) & C4
The Desired text would be:
所需的文本是：
Languages:
English, Spanish, German, French
语言：
英语、西班牙语、德语、法语
(where the bold text would actually be some color like red)
（粗体文本实际上是某种颜色，如红色）
Is there a way to do this in Excel (change partial text formatting) .
有没有办法在 Excel 中执行此操作（更改部分文本格式）。
I Have tried a formula... (Not working)
我试过一个公式......（不工作）
Function formatText(InText As Range)
'Set font color
InText.Characters(1.5).Font.Color = Red
'InText.Characters((InStr(1, ":", InText) + 1), (Len(InText) - InStr(1, ":", InText))).Font.ColorIndex = 3
End Function
采纳答案by chris neilsen
Your posted function with work if and only if
当且仅当您发布的功能与工作
- It is called from a
Sub(ie, as other have mentioned, not as a UDF)
- 它是从 a 调用的
Sub（即，正如其他人提到的，不是作为 UDF）
And
和
- the value(s) contained in range
InTextare string constants. (This is the main point of my answer)
- range
InText中包含的值是字符串常量。（这是我回答的重点）
It will not workfor any cells in range InTextcontaining a formula. AFAIK you cannot format partof a string returned by a formula.
它不适用于InText包含公式的范围内的任何单元格。AFAIK 您不能格式化公式返回的字符串的一部分。
BTW I would love to be proved wrong on this!
顺便说一句，我很想在这方面被证明是错误的！
回答by tpkaplan
Regarding Hightower's question, "how would you cast a formula output to a string so that you can apply the text formatting?"
关于 Hightower 的问题，“您如何将公式输出转换为字符串，以便您可以应用文本格式？”
To "cast" the output of a formula so that you can apply text formatting, you must write the value returned by the formula into the spreadsheet, then apply the formatting to the value you wrote. You could either write the value into the cell containing the formula (which will erase the formula), or you could write the value into a different place in the spreadsheet (which will preserve the formula but then you'll be seeing double).
要“转换”公式的输出以便您可以应用文本格式，您必须将公式返回的值写入电子表格，然后将格式应用于您写入的值。您可以将值写入包含公式的单元格（这将删除公式），或者您可以将值写入电子表格中的不同位置（这将保留公式，但随后您将看到双倍）。
Sub Cell_Format(InText as Range)
InText.formula = cstr(InText.value) ' converts result of formula into a string literal
'or: InText.offset(0,1).formula = cstr(InText.value) -- writes the value in the cell next to InText
InText.characters(1, 5).font.color = vbRed
End Sub
Then Cell_Format range("$A$1")will replace the formula in cell $A$1 with a string constant and change the color of the first five characters to red.
然后Cell_Format range("$A$1")将单元格 $A$1 中的公式替换为字符串常量并将前五个字符的颜色更改为红色。
If you want to do this for a range larger than one cell, add this code to the above:
如果要对大于一个单元格的范围执行此操作，请将此代码添加到上面：
Sub Range_Format(InText as Range)
For each c in InText
Cell_Format(c)
Next
End Sub
回答by tpkaplan
You cannot directly call the below UDF in Excel interface. For that you will have use an event as UDF cannot change the physical characteristic of a cell. For more details you may read this link. http://support.microsoft.com/kb/170787
Excel界面中不能直接调用下面的UDF。为此，您将使用一个事件，因为 UDF 无法更改单元格的物理特性。有关更多详细信息，您可以阅读此链接。http://support.microsoft.com/kb/170787
Function formatText(InText As Range)
'Set font color
InText.Interior.Color = vbRed
'InText.Characters((InStr(1, ":", InText) + 1), (Len(InText) - InStr(1, ":", InText))).Font.ColorIndex = 3
End Function
回答by Peter Albert
You need to use this code:
您需要使用此代码：
InText.Characters(1,5).Font.Color = RGB(255, 0, 0)
If you want to make it flexible, so that only the (fully) second line is red, use this code:
如果您想使其灵活，以便只有（完全）第二行是红色的，请使用以下代码：
InText.Characters(Instr(InText, vbCr)+1, Len(InText)-Instr(InText, vbCr)).Font.Color = RGB(255, 0, 0)
Note that your function needs to be called from VBA, i.e. you cannot use it as a User-Defined-Function! UDFs can only return a result but never modify a cell!
请注意，您的函数需要从 VBA 中调用，即您不能将其用作用户定义的函数！UDF 只能返回结果而不能修改单元格！
