在VBA中如何格式化文本/字符串？ [英] How do you format text/strings in VBA?

问题描述

在下面的代码中，我使用一些输入参数，无论是文本还是单元格，并使用我需要的格式将它们组合成一个字符串。我需要将Task_Name加粗，以及Lead：的文本。我知道你不能把文字变成粗体，但是我该怎么做呢？我正在存储该值的单元格最终用于Word邮件合并。

我需要格式化字符串的一部分。在下面的代码中，我需要使Task_Name，Lead：等都是粗体。

 函数GENERATE_STAFFING_SECTION（Task_Name， Lead_By，Members，Instructions）
 Dim tmpSection As String 
 
如果Len（Task_Name> 0）And Len（Lead_By）> 0和Len（成员）> 0和Len（说明）> 0然后
 tmpSection = vbLf _ 
& Task_Name _ 
& vbLf& 铅：& Lead_By _ 
& vbLf& 大使：&会员_ 
& vbLf& 说明：&说明_ 
& vbLf 
 Else 
 tmpSection =
 End If 
 
 GENERATE_STAFFING_SECTION = tmpSection 
结束函数
  

另外，我知道这不是最干净的代码，所以如果有其他建议来改进它们，那么他们是最受欢迎的。

谢谢！

解决方案

您无法直接添加任何字符串，使单元格具有粗体字符。

将字符串写入单元格后，您需要返回并重新处理单元格。
例如：

 使用ActiveCell.Characters（开始：= 11，长度：= 6）.Font 
 .Name =Arial
 .FontStyle =Bold
 .Size = 10 
 .Strikethrough = False 
 .Superscript = False 
 .Subscript = False 
 .OutlineFont = False 
 .Shadow = False 
 .Underline = xlUnderlineStyleNone 
 .ColorIndex = xlAutomatic 
结束
  / pre> 
 
 
此代码段将仅将单元格的一部分设置为粗体。
 
 
 
编辑：
 
 
 
此代码可用于实现上述内容，并提供您想要的内容。 
它可以写得更好，但应该提供你必须写的内容：
 公开Sub FormatOuput（）
 
 Dim i As Integer 
 
'格式Task_name 
i = InStr（1，ActiveCell.Text，vbLf）
 MakeBold 1，i 
 
'格式'铅'
 MakeBold i + 1，4 
 
'格式'大使'
i = InStr（i + 1，ActiveCell.Text ，vbLf）
 MakeBold i + 1，11 
 
'格式'说明'
i = InStr（i + 1，ActiveCell.Text，vbLf）
 MakeBold i + 1，10 
 
 End Sub 
 
 Public Sub MakeBold（startPos As Integer，charCount As Integer）
使用ActiveCell.Characters（start：= startPos，length：= charCount）.Font 
 .Name =Arial
 .FontStyle =Bold
 .Size = 10 
 .Strikethrough = False 
 .Superscript = False 
 .Subscript = False 
 .OutlineFont = False 
 .Shadow = False 
 .Underline = xlUnderlineStyleNone 
 .ColorIndex = xlAutomatic 
结束
 End Sub 
  
 
In the code below, I take some input parameters, either text or a cell, and combine them to form one string using the formatting I need.  I need to make Task_Name bold, as well as text like "Lead :".  I know you cannot make text in a variable bold, but how do I go about this?  This cell I'm storing the value in is eventually used in a Word mail merge.

I need to format part of a string.  In the code below, I need to make Task_Name, "Lead : ", etc. all bold.
Function GENERATE_STAFFING_SECTION(Task_Name, Lead_By, Members, Instructions)
    Dim tmpSection As String

    If Len(Task_Name > 0) And Len(Lead_By) > 0 And Len(Members) > 0 And Len(Instructions) > 0 Then
        tmpSection = vbLf _
                    & Task_Name _
                    & vbLf & "Lead : " & Lead_By _
                    & vbLf & "Ambassadors : " & Members _
                    & vbLf & "Instructions : " & Instructions _
                    & vbLf
    Else
        tmpSection = ""
    End If

    GENERATE_STAFFING_SECTION = tmpSection
End Function
Also, I know it's not the cleanest code, so if there are any other suggestions for improving it, they are most welcome.

Thanks!
解决方案
You can't add anything to the string directly to make the cell have bold characters.

Once you've written the string out to the cell, you'll need to go back and reprocess the cell. 
For example:
With ActiveCell.Characters(Start:=11, Length:=6).Font 
    .Name = "Arial" 
    .FontStyle = "Bold" 
    .Size = 10 
    .Strikethrough = False 
    .Superscript = False 
    .Subscript = False 
    .OutlineFont = False 
    .Shadow = False 
    .Underline = xlUnderlineStyleNone 
    .ColorIndex = xlAutomatic 
End With 
This snippet will set only a portion of the cell to bold.

EDIT:

This code could be used to implement the above and give you what you want.
It could be written better, but should give you an idea of what you've got to write:
Public Sub FormatOuput()

    Dim i As Integer

    'Format Task_name
    i = InStr(1, ActiveCell.Text, vbLf)
    MakeBold 1, i

    'Format 'Lead'
    MakeBold i + 1, 4

    'Format 'Ambassadors'
    i = InStr(i + 1, ActiveCell.Text, vbLf)
    MakeBold i+1, 11

    'Format 'Instructions'
    i = InStr(i + 1, ActiveCell.Text, vbLf)
    MakeBold i+1, 10

End Sub

Public Sub MakeBold(startPos As Integer, charCount As Integer)
    With ActiveCell.Characters(start:=startPos, length:=charCount).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub


                        
这篇关于在VBA中如何格式化文本/字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！