center text in a fixed string space

[sal21]

i have a free space of 60 charachter.
now i need to center a text in this string...

note:
the text to center Have a variable lenght, but max 60 charachter

[HansV]

If you use a fixed-width font such as Courier New or Consolas, you can use:

Code: Select all

    Dim strShort As String
    Dim strLong As String
    Dim lngSpace As Long
    strShort = "Salvatore"
    lngSpace = (60 - Len(strShort) \ 2
    strLong = Space(lngSpace) & strShort & Space(60 - lngSpace)

[sal21]

If you use a fixed-width font such as Courier New or Consolas, you can use:

Code: Select all

    Dim strShort As String
    Dim strLong As String
    Dim lngSpace As Long
    strShort = "Salvatore"
    lngSpace = (60 - Len(strShort) \ 2
    strLong = Space(lngSpace) & strShort & Space(60 - lngSpace)

WORK!
Now i use Consolas, for tabular text in my project!

[mirfield]

If you use a fixed-width font such as Courier New or Consolas, you can use:

Code: Select all

    strLong = Space(lngSpace) & strShort & Space(60 - lngSpace)

Wouldn't this add 25 spaces before and 35 after? You need to deduct the length of the string too

Code: Select all

    strLong = Space(lngSpace) & strShort & Space(60 - lngSpace - Len(strShort))

or just trim to the right length

Code: Select all

    strLong = Left(Space(lngSpace) & strShort & Space(60), 60)

[HansV]

Aargh - yes!

I like the last one!

[Doc.AElstein]

Hello
I expect VB is wanted here, not VBA?
No matter, here’s one that often seems to get forgotten sometimes, using the Mid Statement

Code: Select all

 Let strLong = Space(60)
 Mid(strLong, lngSpace, Len(strShort)) = strShort

Alan

( the VBA equivalent of that would be, I think

Code: Select all

Let strLong = Application.WorksheetFunction.Replace(Space(60), lngSpace, Len(strShort), strShort)

)