4

I have a report generator in Excel VBA, which works so far. It reads some input data and produces a formatted Excel sheet as result. The last column of this sheet is filled with some kind of free text.

Now, sometimes the free text does not fit into the last column, it is just too wide. Since the row height of all rows of this report is fixed, I cannot set range.WrapText=True for this column (to let the text stay visible, one would have to increase the row height). A manual (non-VBA) solution is easy: split the text into several parts and spread it over different rows. For example:

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

should be transformed into

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

I could easily code that if I would be able to determine the real text width (using a proportional font!) of the content in column B using VBA. range.ColumnWidth gives me the actual width of the column, but I have no idea how I determine the width of "This text is too wide for column B." in VBA. Any suggestions?

(I am currently using Excel 2002 / XP).

Doc Brown
  • 19,739
  • 7
  • 52
  • 88
  • What would happen if a single word is greater than the column size? – Tiago Cardoso Mar 21 '11 at 14:33
  • @Tiago: for real world data, this is very unlikely. However, if this rare case happens, I could show the user a warning, so he can deal with it manually. – Doc Brown Mar 21 '11 at 15:04
  • If you have multiple rows, will you need to shift rows down so the wrapped text doesn't end up overwriting content for the rows below ? – Tim Williams Mar 21 '11 at 16:48
  • @Tim: the column (B in this example) is filled in top-to-bottom order, using different data sources (the row title is "Remarks", and it is used to store different kind of things). The output is organized in blocks of at least 5 rows, and if that is not enough, I can add new rows below the current block. – Doc Brown Mar 21 '11 at 18:48

2 Answers2

5

This will work (in 2003... surely in 2002 as well).

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 wouldn't get bogged down in this estimation business if I were you... That's just asking for unexpected stuff to happen.

EDIT: Addressing points in your comment.

The above will AutoFit the column to the cell with the widest text. To consider only the text in the current cell, copy cell text into some blank column and do the AutoFit there.

If you're concerned about performance, then you'll have to bite the bullet and make a look-up table of character widths. Cycle through Chr(i) and measure the width of each character, using the technique above. Store results in an array, make a function to get width of a string by looking up char widths in that array and summing. There will be an initial cost for making the LUT, but look-ups will be very quick, unnoticeably so. Of course, the LUT will only be valid for the current font, so I'd re-create it every time the code is run. Worth the cost if there are as many rows as you say.

NB: fitWidth above will return the width of the character PLUS some small value (0.29pt on my machine) which is a kind of thin "white margin" automatically added on both sides of the cell for aesthetic purposes. Remember to subtract that from fitWidth to get the true character width. You can figure out how wide that is by doing an autofit on "A" and "AA". The difference between these will be the true width of a single "A".

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • +1, Seems to be an interesting approach which might work in my case, I will give it a try soon. I see 2 possible drawbacks: 1. performance (my reporting sheet can have a lot of rows) 2. this approach takes the whole column content into account, not only the current cell. – Doc Brown Mar 21 '11 at 13:26
  • Well, go ahead and create that fake account, then :) – Jean-François Corbett Mar 22 '11 at 00:12
1

For an app that is supposedly wysiwyg excel does a pretty poor job rendering fonts in a consistent manor. what you see varies by font, font size, zoom level and differs between on screen and printed. throw in non-proportional fonts and it gets even worse.

Any solution to calculating string width will be fraught with complexity.

As an alternative, consider Merging some cells in column B and use wrapping. If there is space below the text a couple of extra rows in the merge won't affect the result so calculating the number of rows can be done conservatively (err on the high side)

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • I cannot merge arbitrarily, see my changed example above. So a merge leaves me in a situation where I have to determine when to merge, and how many rows to merge, which is essentially the same. – Doc Brown Mar 21 '11 at 11:06
  • the point i was making is that you can make an approximate calc of string width from string length, merging and wrapping will be much more tollerant than trying to split the string yourself – chris neilsen Mar 21 '11 at 11:12
  • If you have an idea how to make a good *approximate calc of string width from string length* (or even better: know a way how Excel can do this for me), you are welcome. – Doc Brown Mar 21 '11 at 11:56
  • Excel can't do it for you (unfortunately). You have to do some analysis on the font and either take an average, or the worst case in your situation since it shouldn't EVER overflow on the report. If you aren't using a fixed width font (which makes it easy) I believe an upper case W is the widest character in most fonts. See how many of them fit in a known width and there is your string length divisor. – ktharsis Mar 21 '11 at 13:09