VBA String Characters Excel

Question

I am a complete VBA beginner so please dumb your answer down as much as possible.

I need to bold str2. I have worked out how to do it based on a set number of characters however, I have an issue because str02 is dynamic. It is a customers name and therefore, the length changes meaning I can't count characters because I don't know how many there will be.

i.e

Dear Mr Customer (length changes),

bold Your request has been approved bold

Thank you

Public Sub ExampleConcatenate()

    Dim str1 As String
    Dim str01 As String
    Dim str02 As String
    Dim str03 As String
    Dim str2 As String
    Dim str3 As String

    str1 = Range("A14").Value
    str01 = " "
    str02 = Worksheets("Restructure Calculator").Range("D5").Value
    str03 = vbNewLine
    str2 = Range("A15").Value
    str3 = Range("A16").Value

    Range("A1").Value = str1 & str01 & str02 & str03 & str03 & str2 & str03 & str3

End Sub

VBA strings have no notion of bold vs. ordinary characters. You have to look into the `Text` in the cell you want to display it in. As a hint, make part of the text in a cell bold while the macro recorder is turned on then look at the recorded code. Also, you can use `len()` to determine which characters in the final string that you want to highlight. — John Coleman, Apr 13 '20 at 23:23
Does this answer your question? [excel vba: make part of string bold](https://stackoverflow.com/questions/10295380/excel-vba-make-part-of-string-bold) — John Coleman, Apr 13 '20 at 23:24
Hi John - thank you but I have already tried this. My original set up was a concatenate formula (not done with VBA) and then a macro to copy, paste special by values (to remove the formula) and then bold the relevant parts. The problem is, it always copies and pastes my original customer at the time of recording and does not pick up the new customer information. Even with the VBA above and a separate macro to bold the text, it still always copies and pastes my original customer information. I have a lot of dynamic information that I need it consider. — Holly, Apr 13 '20 at 23:31
Your comment seems to be shifting the question. There are two questions: 1) how to assemble the string you want to assemble and 2) how to bold part of the resulting string. You seemed to be asking about the second question but seem to be shifting to the first. Unfortunately, for that you haven't really provided any necessary information. If you want a macro to dynamically change the sheet in response to changes in the sheet, you could use events such as `Worksheet_Change` — John Coleman, Apr 13 '20 at 23:34
Yes. I need to bold str2. The problem is str02 is not a static number of characters. So how do I bold str2? — Holly, Apr 13 '20 at 23:40
Use something like `len(str1) + len(str01) +len(str02) + len(str03) +len(str03)` to determine the number of characters before it? — John Coleman, Apr 13 '20 at 23:43
Yes I understand that however, how do I then use that information to bold str2? — Holly, Apr 13 '20 at 23:48
Did you look at the link I gave to that other question? That explains the VBA way to make part of a string appearing in a cell to be bold. — John Coleman, Apr 13 '20 at 23:55
Ah sorry I didn't see the link you posted. It looks like I possibly need to try a loop. I will try this and get back to you with if it works or not. Thank you — Holly, Apr 14 '20 at 00:01

Variatus · Answer 1 · 2020-04-14T01:51:53.370

This is what your code might look like. Please try it.

Sub ExampleConcatenate()

    Dim Output As String
    Dim Project As String
    Dim ProjectID As String
    Dim Decision As String
    Dim BodyText As String
    Dim n As Integer

    Project = Range("A14").Value
    ProjectID = Worksheets("Restructure Calculator").Range("D5").Value
    Decision = Range("A15").Value
    BodyText = Range("A16").Value

    Output = Project & " " & ProjectID & String(2, Chr(10))
    n = Len(Output)
    Output = Output & Decision & Chr(10) & BodyText

    With Cells(1, "A")
        .Value = Output
        With .Characters(Start:=n + 1, Length:=Len(Decision)).Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 12
            .Underline = True ' xlUnderlineStyleNone
        End With
    End With
    Rows(1).AutoFit
End Sub

And then fix it.

Your system of numbering strings will lead you to the need for a phonebook to keep track of your numbers. Give meaningful names to your variables and don't give generic names to existing variables that already have meaningful names, such as Str03 = vbNewLine, which, incidentally, doesn't make much logical sense if one considers that Str03 is declared as string whereas vbNewLine is an enumeration of Long data type.
I gave names to the variables that enabled me making some sense. I'm sure it won't be the sense you intended. You can use Edit > Replace to rename variables in the code.
vbNewLine doesn't show up as a CR in an Excel cell (at least, in connection with the String() function it didn't for me). I replaced it with Chr(10).
With the exception of a reference to Worksheet("Restructure Calculator") all your sheet references are on the ActiveSheet, meaning no worksheet is declared for them. It's only a matter of time until your code accidentally writes to Worksheet("Restructure Calculator"), destroying data there. Better to create a variable for the worksheet you want to write to before that happens and make sure all the ranges and cells in your code are identified with the worksheet they refer to.
My code shows you how to identify the location of the text you wish to highlight. It also shows how to set the font for that section of text. You may wish to alter the settings. It's easy with the setup I provide.

Last but not least, you may like to bear in mind that Excel starts hitting some limits when you have more than 255 characters in a single cell. To avoid possible problems in the future consider spreading your text over several rows if its total length exceeds 255 characters.

VBA String Characters Excel

1 Answers1