Center a string VBA

[Pete]

I have created this excel VBA function to center a string.
It works but is there a better way?

Sub Test()
MsgBox CenterString("Center This String", 50)
End Sub

Function CenterString(xInput As String, xLength As Long)
xM = Space(((xLength / 2) - (Len(xInput) / 2) + 1)) + xInput
CenterString = xM + Space(xLength - Len(xM))
End Function

 

[Bob Phillips]

An alterntive

Function CenterString(xInput As String, xLength As Long)
Dim xM As Long
xM = Int((xLength - Len(xInput)) / 2)
CenterString = Space(xM) & xInput & Space(xLength - Len(xInput) - xM)
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Pete]

Dim xM As Long
xM = Int((xLength - Len(xInput)) / 2)
CenterString = Space(xM) & xInput & Space(xLength - Len(xInput) - xM)

Thanks Bob that works better then my function.

 

[Dana DeLouis]

Just a different way...

Function CenterString(xInput As String, xLength As Long) As String
CenterString = Space(xLength)
Mid$(CenterString, 1 + (xLength - Len(xInput)) \ 2) = xInput
End Function

 

[Guest]

This may note be applicable, but if you want to center it
in a cell that you are writing to, then use:
Selection.HorizontalAlignmment = xlCenter