Center a string VBA

P

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
 
B

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)
 
P

Pete

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

Thanks Bob that works better then my function.
 
D

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
 
G

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
 
Top