Calculate string width in inches nzDec01

[Neal Zimm]

HI all,
The worksheets in my app must be printed on paper about 7 inches wide in a
font size of at least 18 or 20 to be read in very dim lighting. Column
widths are fixed.

If a macro generated text string does not 'fit' into the designated cells
in 1 row, I use more than 1 row to hold the text after spliting it into
'words'.

I'm trying to AVOID building a function to return the width of a character
string in inches, for a few font sizes and 1 to 3 font names.

.Shrinktofit on its own doesn't seem to help.

1. I can't find a way to test, If OneCell.Shrinktofit = true then Did
Excel actually shrink the cell contents ? This would help.

2. Is a call available to MSword that returns the width in inches of a
string, where its input is the font name and size of the characters? (It's
gotta be there, but is it available to mortals? I've also posted this on the
Word bulletin board.)

3. What might other 'stuff' be that I might be able to use?

Thanks.

 

[Jim Thomlinson]

Widths are tricky... Check out this link...

http://support.microsoft.com/default.aspx/kb/214123

 

[Neal Zimm]

Dear Jim,
Thanks for the article. I knew about most of it already. In the addin I'm
developing @ workbook open I set the Options standard font to Arial size 10
in order to get a certain quantity of characters into a 'standard' cell width
in my app.

A bloke from England responding to the same question in the MS Word bulletin
board actually came up with an idea that I've turned into the steps below
that WILL work for me WELL in Excel. The key is to AUTOFIT a column width.

Dim StringArray() As String
Dim RowCount as integer

Use off screen 'unused' column as test vehicle.
Set column width property to autofit.
value .screenupdating to false

set width of column to standard width

put entire character string of words into cell
rowcount =1
redim stringarray(rowcount)

see how col width has changed

current width <= standard is good, no additional rows needed
use stringarray(rowcount) to hold first row's words. rowcount ubound
of stringarray is 1 for needed rows.

current width > standard width
remove words from end of string and save in WordsToGo string
var until current col width <= standard col to get words in the row.
stringarray(rowcount) holds first row's group of words
rowcount = rowcount + 1
redim preserve stringarray(rowcount)

repeat above until WordsToGo string is null and the ubound of
stringarray is rowcount, what's needed to hold the entire string, and the
elements of stringarray are for each row's text holding cell.

Autofit the column, great idea as measuring tool.
Neal