How do you format text/strings in VBA?

Question

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!

Solution

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