Split text into multiple lines according to column width

Question

I have a report generator in Excel VBA that still works. It reads some input data and as a result it turns out as a formatted Excel sheet. The last column of this sheet is filled with some free text.

Now, sometimes free text does not fit into the last column, it is too wide. Since the row height of all rows in this report has been fixed, I cannot set range.WrapText=True for this column (to keep the text visible, I would have to increase the row height). The solution for the manual (not VBA) is simple: divide the text into several parts and distribute it on different lines. For instance:

 A | B |C content |This text is too wide for column B. here |This text fits. | is | | fixed | | 

should be converted to

 A | B |C content |This text is too | here |wide for column | is |B. | fixed |This text fits. | 

I could easily write this down if I could determine the actual width of the text (using a proportional font!) Of the contents in column B using VBA. range.ColumnWidth gives me the actual column width, but I have no idea how to determine the width . This text is too wide for column B. in VBA. Any suggestions?

(I am currently using Excel 2002 / XP).

Answer 1

This will work (in 2003 ... of course, in 2002).

 With Columns("B:B") oldWidth = .ColumnWidth .EntireColumn.AutoFit ' Aha! fitWidth = .ColumnWidth .ColumnWidth = oldWidth ' Restore original width If oldWidth < fitWidth Then ' Text is too wide for column. ' Do stuff. End If End With 

I would not get bogged down in this appraisal business if I were to you ... It just asked for unexpected things.

---

EDIT. Addressing points in your comment.

The above will automatically assign the column to the cell with the widest text. To view only the text in the current cell, copy the cell text into some empty column and execute AutoFit.

If you are concerned about performance, you will have to bite the bullet and compile a table of character widths. Loop through Chr(i) and measure the width of each character using the above technique. Save the results in an array, create a function to get the width of the string by looking at the width of the char in that array and adding up. The initial cost of creating a LUT will be started, but the search will be very fast, unnoticed. Of course, the LUT will only be valid for the current font, so I will recreate it every time I run the code. It is worth it if there are as many lines as you say.

NB: fitWidth above will return the width of the PLUS symbol a small value (0.29pt on my machine), which is a kind of thin “white box” automatically added on both sides of the cell for aesthetic purposes. Remember to subtract this from fitWidth to get the true character width. You can find out how wide it is by doing auto-tuning on “A” and “AA”. The difference between them will be the true width of one "A".

Answer 2

For an application that supposedly wysiwyg excel does a pretty poor job of translating fonts into a consistent estate. what you see depends on the font, font size, zoom level and differs from the screen and print. throw disproportionate fonts and it gets even worse.

Any solution for calculating the line width will be difficult.

As an alternative, consider merging some cells in column B and using wrapping. If there is space under the text, a couple of additional lines in the merge will not affect the result, so the calculation of the number of lines can be performed conservatively (err from the high side)