CODEWITHSUNDEEP
excelvbastring
Holly
Holly

Reputation: 71

VBA String Characters Excel

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

Upvotes: 0

Views: 344

Answers (1)

Variatus
Variatus

Reputation: 14373

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.

  1. 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.
  2. 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.
  3. 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).
  4. 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.
  5. 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.

Upvotes: 1

Related Questions