I know this gets asked regularly, but:

I've got a couple of text-centering and -wrapping demands that Excel
can't do automatically.

Exactly what do the units returned by GetTextExtentPoint32, and how do
you translate, convert, or otherwise use them to determine whether a
string fits horizontally inside a cell--taking into account font size
and attributes, screen resolution, etc.?

The best I can do now is to use an arbitrary conversion factor (which
varies between about 7.5-9.0, depending on column width)--and it STILL
returns the wrong result when I port the workbook to another system.

***

(One of my applications is: I have rows holding text in the first cell
of each row, with no blank rows separating each row of text. So I need
to wrap the text in each first cell, and indent the left side of the
second-and-on wrapped lines by say 3 spaces, leaving only the first
line out-dented. Maybe there's an easier way to do that than
programmatically?)

Thanks much.

***

P.S.: After all, Excel is capable of centering and wrapping text.

So how does Excel do it, and how do we do the same thing?

***

The short, simple and relatively accurate way is simply to copy the cell
with text & requisite formats to a cell in an empty column (say in a hidden
sheet or in the addin), Autofit and return the column width. You can get
most of the code by recording a macro and adapting to your needs.

Slightly more accurate (actually very accurate) is to write the text to an a
textbox with Autosize = true and no margins. Apply cell font attributes to
the tb, if necessary with mixed fonts apply individually to each character
(Name, Size, Bold, Italic, etc) and return the tb's width. Also account for
indents. I posted code elsewhere to do all that, though for most purposes
the column-autofit should suffice.

I had a quick go with GetTextExtentPoint32, at first I thought it was giving
promising results but not correct at all. If you are into API's and/or
interested maybe you can develop the following:

' in a Userform with a button
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function GetTextExtentPoint32 Lib "gdi32" _
Alias "GetTextExtentPoint32A" ( _
ByVal hDC As Long, ByVal lpsz As String, _
ByVal cbString As Long, lpSize As RECT) As Long

Private Declare Function GetDC Lib "user32" ( _
ByVal hWnd As Long) As Long

Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hWnd As Long, ByVal hDC As Long) As Long

Private Declare Function GetSystemMetrics& Lib "user32" ( _
ByVal nIndex As Long) ' will need to get pnts/pxl

Private Const SM_CXDLGFRAME = 7
Private Const SM_CYDLGFRAME = 8
Private Const SM_CXFIXEDFRAME = SM_CXDLGFRAME
Private Const SM_CYFIXEDFRAME = SM_CYDLGFRAME

Private Const SM_CYBORDER = 6

Private Const WM_GETFONT = &H31

Private Type RECT
cx As Long
cy As Long
End Type

Private Sub TextSize(sText As String, ByRef nx As Long, ByRef ny As Long)
Dim tSize As RECT
Dim hWnd As Long, hDC As Long

hWnd = FindWindow("ThunderDFrame", Me.Caption)

hDC = GetDC(hWnd)

GetTextExtentPoint32 hDC, sText, Len(sText), tSize
nx = tSize.cx

ReleaseDC hWnd, hDC
End Sub
Private Sub CommandButton1_Click()
Dim s$, sa$
Dim x As Long, y As Long
Dim fnt As Font

With Range("A1")
.EntireColumn.ClearContents
.Copy .EntireRow
Set fnt = .Font
End With

With Me.Font
.Name = fnt.Name
.SIZE = fnt.SIZE
.Bold = fnt.Bold
.Italic = fnt.Italic
End With

sa = "aa" '

For i = 1 To 10
s = s & sa
TextSize s, x, y
Cells(1, i) = s
Cells(1, i).Columns(1).AutoFit
Cells(2, i) = Cells(1, i).Width ' points
Cells(3, i) = x ' pixels, typically 0.75 pnt/pxl
Next

End Sub

Not sure if this is on the right track or not, but definitely not right as
written. Seem to get same width irrespective of Font and particular
character, though it does return something relative to character-count.
Eventually would need to convert pixels to points, I assume.

Regards,
Peter T


wrote in message
...
I know this gets asked regularly, but:

I've got a couple of text-centering and -wrapping demands that Excel
can't do automatically.

Exactly what do the units returned by GetTextExtentPoint32, and how do
you translate, convert, or otherwise use them to determine whether a
string fits horizontally inside a cell--taking into account font size
and attributes, screen resolution, etc.?

The best I can do now is to use an arbitrary conversion factor (which
varies between about 7.5-9.0, depending on column width)--and it STILL
returns the wrong result when I port the workbook to another system.

***

(One of my applications is: I have rows holding text in the first cell
of each row, with no blank rows separating each row of text. So I need
to wrap the text in each first cell, and indent the left side of the
second-and-on wrapped lines by say 3 spaces, leaving only the first
line out-dented. Maybe there's an easier way to do that than
programmatically?)

Thanks much.

***