PDA

View Full Version : Sleeper: Kb entry to display long text



jungix
07-17-2006, 09:12 AM
I have a little problem with this KB article

http://vbaexpress.com/kb/getarticle.php?kb_id=421

Unfortunately I am unable to understand all of it. Has someone used it and tested it? Usually with a lineIncr of .Columnwidth the column is not filled entirely to the right. I put 1.05*.Columnwidth which is already better but still not enough to fill my column. But I can't put more because otherwise when a long word is at the end of one line, the feed is done before and then there is a long word starting a line but not being taken into account in the counting of the characters (I think) because sometimes the last word of the next line is then alone on a line after the feed.
Do you know what I could change about it (changing the number of characters in a line adding the last word after the last space and the line feed)?
I join the modify code I use (slightly modified) and that does not work perfectly well.



Option Explicit


Sub DisplayLongText()

'Adds line feed characters as required on cells in selection that are longer than 1024 characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
AddLineFeeds cel, col
Next
col = 0 'Force line length dialog to display the next time sub runs
End Sub


Sub AddLineFeeds(cel As Range, col As Long)
'Adds line feed characters at end of each line of text. Some experimentation may be required to set number
'of characters at the wrapping point.
'Code requires Excel 2000 or later because of Replace and InStrRev functions
Static lineIncr As Long
Dim i As Long, j As Long, pos As Long, StartPos As Long
Dim sLeft As String, str As String, sRight As String, sLineFeed As String

StartPos = 1 'Value should be between 1 and 1022. The first 1022 characters (plus ASCII 160 & line feed)
'will always fit correctly. The macro will add ASCII 160 space plus line feed characters
'to each line after character StartPos. You may notice that the beginning section of text
'uses a different "break" point than the end. If this bothers you, set StartPos=1;
'it will be reset to the chosen line length later in the sub.

With cel
col = 0
If Len(.Value) <= 1022 Then Exit Sub

'Remove line feed characters which may have been added previously. These always follow an ASCII 160 space.
sLineFeed = Chr(160) & Chr(10) 'Code puts an ASCII 160 space before every added line feed character
str = Replace(.Value, sLineFeed, " ")
ActiveCell.RowHeight = 20
'The maximum permitted number of characters on a line. User-specified up to a limit of 256 characters/line
If .Column <> col Then 'Use same value as last time if still working in same column
lineIncr = .ColumnWidth * 1.05
col = .Column
If StartPos < lineIncr Then StartPos = lineIncr
End If

sLeft = Left$(str, StartPos) 'Excel has no problem wrapping the first 1024 characters
pos = InStrRev(sLeft, " ") 'Find right-most space in first 1022 characters
If pos = 0 Then 'No space found, so force a break after 1022 characters
sLeft = sLeft & sLineFeed
sRight = Mid$(str, StartPos + 1)
ActiveCell.RowHeight = ActiveCell.RowHeight + 12.5
Else 'Put ASCII 160 plus line feed characters in place of this right-most space
sLeft = Left$(str, pos - 1) & sLineFeed
sRight = Mid$(str, pos + 1)
ActiveCell.RowHeight = ActiveCell.RowHeight + 12.5
End If

pos = 1 'Loop through remainder of text, looking for places to put ASCII 160 plus line feed characters
Do
j = InStr(pos, sRight, Chr(10))
If j > 0 And j - pos <= lineIncr Then
pos = j + 1
Else
i = InStrRev(sRight, " ", pos + lineIncr) 'Find right-most space in next lineIncr characters
If i > pos Then 'Put ASCII 160 plus line feed characters in place of this right-most space
sRight = Left$(sRight, i - 1) & sLineFeed & Mid$(sRight, i + 1)
pos = i + 2
Else 'Didn't find a good place to break the line, so force the break in middle of a word
sRight = Left$(sRight, pos + lineIncr) & sLineFeed & Mid$(sRight, pos + lineIncr + 1)
pos = pos + lineIncr + 3

End If
End If
If ActiveCell.RowHeight < 397 Then
ActiveCell.RowHeight = ActiveCell.RowHeight + 12.5
End If
If Len(sRight) - pos < lineIncr Then Exit Do 'Not enough text left for a full line
Loop

.Value = sLeft & sRight 'Put the rebuilt string in place of the original
End With
End Sub





Thank you

jungix
07-19-2006, 05:34 AM
Up.

Someone already used this KB article? Is it working fine on your computer?

jungix
07-24-2006, 10:53 AM
I'm still looking for a way to display long text properly (justified and without blanks at the end). Is it possible to copy the whole content of cells in a Word document and keeping the presentation of the cells?

I'm quite desperate with this. Any help would be appreciated.

Jungix

malik641
07-24-2006, 04:31 PM
Hey Jungix

Could you post a workbook example of the text you are trying to fit correctly?

jungix
07-25-2006, 06:19 AM
I join a workbook.

Range A1 is the result of the macro with lineIncr = .Columnwidth*1.05
You can see that the column is still not filled.

Range A4 is the result of the macro with lineIncr = .Columnwidth*1.1
You can see that the column is still not filled.

Range A6 is the result with lineIncr = .Columnwidth*1.2
The column is still not filled everywhere, and one time the linefeed is too late already. So the number of characters in each line may vary before the linefeed, or maybe it is because all characters don't have the same width. Anyway it looks really ugly in Range A1 and I'd like to find a way to arrange the text to fill the entire length of a line before adding the line feed.

Sometimes the error occurs with .Columnwidth*1.1 already depending on the text

malik641
07-25-2006, 05:53 PM
Hey Jungix,

I tried a different approach. I'm not sure if it's the best one...but at least it's something.

I did a series of tests of what number was best to place in the Input Box with the original KB entry. I found an average of 2.75 worked best with most column widths. Maybe you can incorporate this into your code.

Check out the attached.

HTH :)

byundt
07-25-2006, 09:59 PM
Jungix,
I don't disagree with your assertions at all. The text does look a little cobby after my macro has been run. You can do a better job setting the line length if you use a monospace font (like Courier), but such a strategy also adds ugliness.

I had regarded displayed text truncation as an unsolved problem until I saw someone else using ALT + Enter to break the 1024 character barrier in an Experts-Exchange question last year. That gave me the idea to write the macro.

If I could accurately determine where Excel will wrap the text, then the code could be modified for pretty output. Unfortunately, I don't know how to determine it (short of using eyeball power).
Brad

Aaron Blood
07-26-2006, 11:16 AM
Is there no way to bend a TextBox to your whims for this one?

jungix
07-26-2006, 11:21 AM
I wouldn't know how to do this. I have a lot of cells and the presentation is important for the print area. I don't know if it is possible to put a textbox just instead of the cell for every cell which has more than 1024 characters

Aaron Blood
07-26-2006, 11:40 AM
I cringe at the thought of even a couple hundred characters in a cell.

...and the 1024 workaround (clever as it may be) just sends chills down the spine. I would view any solutions dependent upon such things as future trainwrecks.

How bout this macro that dumps to cells. Can it be tweaked to a more manageable import of one line per cell?