How do you find WHERE Excel splits your wrapped string?

[baobob]

I should state what I really want first:

I've got a long sentence in a wrapped cell. (10 to 15 in Sing Sing.
Send money.)

The 2nd & succeeding lines of text must be indented. (I'll gladly
settle for leading spaces.) E.g.:
This is a long string which resides in a single
wrapped cell of that wonderful application which
we all know and love called Lotu--I mean Excel.
Has anyone coded this functionality?

***

If not, then I need an intermediate UDF to return either:

- The string formatted with, say, vbLF(s) demarcating Excel's splits
(based on the width of the cell where the string lives, natch).

- The string position of the first split. (Since I may have to
reiterate the function each time I indent, thereby altering string
length a tad, that's maybe all I need.)

- Cell width translated into even a *semblance* of a number with which
to take a substring. Many threads have been devoted to this--few
seemingly to any avail.

And yes, I've played around a fair amount, futilely, with
GetTextExtentPoint32--with unending thanks to Peter T. See Jan. 2008
thread, "How do you x-late GetTextExtentPoint32's units into the real
world?"

Also see May 2006 thread, "How to determine whether text in cell needs
to be wrapped?". A question that could not be more clearly stated--yet
went unanswered as posed.

So how do you hook into Excel's wrap method?

Thanks much.

***

 

[Peter T]

Many threads have been devoted to this--few

seemingly to any avail.

I'm surprised you say that, there must be dozens if not hundreds of threads
that suggest autofit column width, I suggested same in the other thread you
referred to so there's one at least! Thereafter it depends on the overall
objective.

As I also mentioned in the other thread, you can also use an "autosize"
textbox which I think has some advantages over autofit'ing column widths.
Have a go with the following -


Sub test()
Dim bDeleteTextBox As Boolean
Dim c As Long
Dim s As String
Dim sIndentExtraLines As String
Dim shp As Shape

Dim cel As Range

Rows(1).Clear
Rows(1).RowHeight = ActiveSheet.StandardHeight

s = "This bunch of words should wrap to cell width such that"
s = s & " second and subsequent lines are indented, or even"
s = s & " include (say) a dash or some other character"

sIndentExtraLines = " - "

For c = 1 To 10
Set cel = Cells(1, c)

Columns(c).ColumnWidth = c * 3 + 20

With cel.Font
.Size = c + 5
.Bold = c Mod 2
End With

bDeleteTextBox = CBool(c = 10) ' if don't need the textbox again

' if might need the tbx in future leave it invisible on the sheet
' and ensure bDeleteTextBox is passed = false

TextLFtoCellWd cel, s, sIndentExtraLines, shp, bDeleteTextBox

Next

Rows(1).EntireRow.AutoFit

End Sub


Function TextLFtoCellWd(cel As Range, _
ByVal sText, _
Optional sIndent As String, _
Optional shpTB As Shape, _
Optional bDelTB As Boolean)
Dim i As Long, c As Long
Dim s As String
Dim s1 As String, s2 As String, s3 As String
Dim w As Single
Dim v
Dim shp As Shape
Dim fnt As Font


If shpTB Is Nothing Then
getTB shpTB
End If
Set fnt = shpTB.TextFrame.Characters.Font

'cel.Clear ' to avoid potential error below with mixed fonts

With cel.Font
fnt.Name = .Name
fnt.Size = .Size
fnt.Bold = .Bold
fnt.Italic = .Italic
End With

' remove any carriage returns & line feeds
sText = Replace(sText, vbCr, " ")
sText = Replace(sText, vbLf, " ")

v = Split(sText, " ")

' s1 = "": s2 = "": s3 = ""

w = cel.Width - 5

For i = 0 To UBound(v)
s1 = s1 & v(i)
shpTB.TextFrame.Characters.Text = s1
If shpTB.Width > w Then
s3 = s3 & s2 & vbLf
s1 = sIndent & v(i)
s2 = s1
Else
s2 = s1
End If
If i < UBound(v) Then s1 = s1 & " "
Next

s3 = s3 & s2

cel.WrapText = True
cel.Value = s3

If bDelTB Then
shpTB.Delete
End If

End Function

Function getTB(shp As Shape) As Boolean

On Error Resume Next
Set shp = ActiveSheet.Shapes("TextLen")
On Error GoTo 0

If shp Is Nothing Then
Set shp = ActiveSheet.Shapes.AddTextbox(1, 0, 0, 1, 20)
shp.Name = "TextLen"
End If

With shp.TextFrame
.AutoMargins = False
.AutoSize = True
.MarginLeft = 0
.MarginRight = 0
End With

shp.Visible = msoFalse ' uncomment for testing

End Function

'''''''''''''''

If not, then I need an intermediate UDF to return either:

Can't use this approach in a UDF as UDFs can only return values (can't do
the intermediary fit stuff). In any case, a UDF would not help if you need
to adjust if user re-sizes column width (format changes do not trigger
recalc / UDF).

It might be possible to develop the GetTextExtentPoint32 approach in a UDF
that I started in the other thread. I didn't pursue it at the time as I
assumed with no follow-up it was not of any interest.

we all know and love called Lotu--I mean Excel.

Amusing, and FWIW my preferred app a long time ago was Quatro Pro. However
one of the reasons Excel won over both was the infinitely superior VB/A,
particularly from XL95, vs the respective macro systems.

Regards,
Peter T

 

[baobob]

Peter T:

Again thanks MUCH for your tremendous effort & reply.

I don't think I fully grasped your first reply, back in Jan., for
several reasons.

One, altho' I've been a programmer for some years, I'm not the sort
who can read dozens of lines of code and immediately grasp the gestalt
of what it's doing, the way a musician can read a score and hear
everything in his head.

Two, every time I see the term "autofit" in relation to my question,
it makes me uncomfortable, to the extent that autofitting a column
width TO some text is the very converse of what I want. I don't want a
column to change its width. Rather, in a column whose width NEVER
changes, tell me where Excel splits up my string. And/but it appears
that your code is doing that.

Three, I am totally amazed that, what Excel surely does with very
simple internal logic--determine where it should split a string--
you're telling me that it takes dozens of lines of code, and in
addition creating a visual component, put text in it, then look at the
result. All of which of course I'm willing to do. ANYTHING to get the
result.

***

Just like you, back during the text computing era my preferred
spreadsheet software was Quattro as well.

***

So thanks much again. I'll execute your implementation. And report on
the results.

***

 

[baobob]

Peter T:

Started playing with your code.

I executed it and it inputted the successfully wrapped-'n-indented
long strings into the sample 10 cells w/ differing widths. Neat.

But what I really want is a function, Wrap(S), which returns the
wrapped and indented string, in place. Because, in most of my cells,
the string is in fact a formula returning a string.

No need to reply yet...I think I am on the way to finally
understanding that by adapting your code I can do that.

And will report result.

Thanks again.

***

 

[baobob]

Peter T:

OK, I’ve played around a lot with your code, but I have so many
questions I must stop for the moment.

(Answer only if inclined

1. First and foremost, what I really need is, not code but a simple
statement of whether what I want can be done or not.

Ideally I want a formula "=MyWrap(X)" where X can be a string *or* a
cell address of a string, which returns the string wrapped 'n
indented.

Your code’s great & does this in a Sub (which I executed via Tools /
Macros--I assume that was correct). But a Sub doesn’t “return” the
result; it “calculates” it in real time inputting it anew in a cell.
After which the original string--however you passed it into the Sub--
is gone, right? But I’ve got hundreds of cell references to strings,
and those strings can change.

2. I’ve been a heavy Excel user for years, and I’d never heard of a
TextBox. However, after making yours visible I certainly grasped it,
and why you’re using it--i.e. as a buffer to measure when my string
exceeds its width.

But if a TextBox is required because a cell or range *itself* has no
useful property that you can apply to string length calculation, then:
Why? Is Redmond’s answer “42”?

3. On a similar score, I must beg your indulgence by asking, possibly
again and possibly dumbly: how does "column autofit", which you've
mentioned at times, enter into my problem? I need to wrap strings in a
column whose width is fixed. I'm not trying to determine what "column
width" a string "requires".

If on the other hand you're suggesting column autofit to somehow help
determine where Excel splits a string when wrapping, then...well, how
would that work? But at this point it might be tangential to the
current thread.

4. In trying to run your code as a UDF, the TextBox seemed to get
created OK, but any attempt to set any TB properties failed and was
totally ignored. E.g. if TB.TextFrame.Characters.Text was, say, "abc",
then command TB.TextFrame.Characters.Text = "xyz" did nothing and the
value remained "abc". And the compiler or interpreter (whatever the
term is) failed to warn me about it.

Is this failure by any chance related to the use of a Function instead
of a Sub? I’ve often seen the statement that some things just can’t be
done in a function.

5. You’re sizing the TB 5 units narrower than the cell. Is that some
magical number? Or a fudge factor of some sort?

Thanks much again if you read this.

***

 

[Peter T]

In line -

Peter T:

1. First and foremost, what I really need is, not code
but a simple statement of whether what I want can be
done or not.

Yes and No!
No - the approach will not work in a UDF, as I explained last time. cannnot
be called in a cell formula to return the modified string. Reason - the
method requires text to be applied to an autosize textbox until a given
width is exceeded (my method) or dumped into some cell and its column
'autofit'. No-can-do in a UDF.

Yes - Instead of the cell formula doing building some sort of string, then
a UDF to split into requisite lines, similar could be done in a change event
or calculation event. That implies trapping changes in the dependant cells
of the original dependant cells then writing updates to what were the
formula cells.
Disadvantage - potential loss of Undo

Ideally I want a formula "=MyWrap(X)" where X can be a string *or*
a cell address of a string, which returns the string wrapped 'n
indented.

Change
Function TextLFtoCellWd(cel As Range, _
ByVal sText, _
Optional sIndent As String, _
Optional shpTB As Shape, _
Optional bDelTB As Boolean)
to
Function TextLFtoCellWd(cel As Range, _
ByVal sText, _
Optional sIndent As String, _
Optional shpTB As Shape, _
Optional bDelTB As Boolean) As String
and at the end of the function

comment
' cel.WrapText = True
' cel.Value = s3
and add
TextLFtoCellWd = s3

Your code’s great & does this in a Sub (which I executed via Tools
/ Macros--I assume that was correct).

You can run the demo "test" like that, or put the cursor in the Test() proc
and press F5 or F8. However the main code is the function TextLFtoCellWd()
and its helper getTB(). You just need to call TextLFtoCellWd with its
arguments from within your own code. Of course adapt the functions to your
needs.

But a Sub doesn’t “return” the
result; it “calculates” it in real time inputting it anew in a cell.
After which the original string--however you passed it into the Sub--
is gone, right? But I’ve got hundreds of cell references to strings,
and those strings can change.

I think I've already explaing this. Note in the function I passed the
string like this
ByVal sText
this means that although sText can be modified within the function (eg to
remove any line breaks) changes to sText will not be returned to th ecalling
function. As an alternative to having hte function return the string, you
could change "ByVal" to the default "ByRef" and in the function
sText = s3
Back in the calling function the string will return modified.

If you are calling the function multiple times pass the shpTB object to
avoid recreating it each time in the function (see the demo).

2. I’ve been a heavy Excel user for years, and I’d never heard
of a TextBox. However, after making yours visible I certainly
grasped it, and why you’re using it--i.e. as a buffer to
measure when my string exceeds its width.

The Textbox is a helper, taking advantage of its autosize "feature" to
return its width and hence that of the string it contains.

But if a TextBox is required because a cell or range *itself*
has no useful property that you can apply to string length
calculation, then: Why? Is Redmond’s answer “42”?

3. On a similar score, I must beg your indulgence by
asking, possibly again and possibly dumbly: how does
"column autofit", which you've mentioned at times, enter into
my problem? I need to wrap strings in a column whose
width is fixed. I'm not trying to determine what "column
width" a string "requires".

If on the other hand you're suggesting column autofit to
somehow help determine where Excel splits a string when
wrapping, then...well, how would that work? But at this
point it might be tangential to the current thread.

I think you missed the point that all the suggestions of the autofit column
method are to use a helper column. This can be on a hidden sheet or in (say)
your addin. The column should be entirely empty excel for the cell that
contains the text to be sized.

The basic principle of the 'autofit column' and 'autosize textbox' methods
are similar, to reurn the width of the autofit column or autosize'd tb.
Personally I think there are many advantages with the textbox (afaik I'm the
only one that has ever suggested that in preference).

However one small disadvantage, depending on overall context, is autofit
columns have some "padding". This varies slightly between xl versions and
more so depending on Font. Did you notice the line
w = cel.Width - 5
That's to cater approximately for the padding. With larger fonts you might
need to increase that, typically 10 should be enough.

4. In trying to run your code as a UDF, the TextBox seemed to
get created OK,

Doubt it, probably already existed

but any attempt to set any TB properties
failed and was totally ignored. E.g. if
TB.TextFrame.Characters.Text was, say, "abc",
then command TB.TextFrame.Characters.Text = "xyz" did nothing
and the value remained "abc". And the compiler or interpreter
(whatever the term is) failed to warn me about it.

Is this failure by any chance related to the use of a Function
instead of a Sub? I’ve often seen the statement that some
things just can’t be done in a function.

Already explained, a UDF cannot change the interface. FYI, a function when
used in a cell formula is termed a "User Defined Function". The exact same
function can work fine when called from (say) a change event but fail as a
UDF.

5. You’re sizing the TB 5 units narrower than the cell.
Is that some magical number? Or a fudge factor of some sort?

Yes a fudge factor, see above re 'padding' (written before I saw your Q5)

In summary, my approach or any similar involving autofit is not going to
work in a cell formula, ie UDF. There might be an alternative API approach
but probably not GetTextExtentPoint32. I'm not optimistic, I have never seen
such a solution in this group; intuitively I feel there might be some other
API approach.

For your purposes consider re-working your methods entirely and get the
updates done in an event change, or get user to press a button to replicate
what was done in formulas and split strings to suit cell widths.

Regards,
Peter T

 

[baobob]

Peter:

Again, thanks much for gargantuan help. I believe I'm now completely
clarified, for example:

- I cannot indeed do this in a UDF.

- Re. my autofit Q, just awhile ago I too had a "Duh" moment wherein I
realized that you were suggesting autofit by way of a HELPER column. I
had indeed missed the point (thinking you were somehow talking about
resizing the cell where the string itself lived).

- I didn't know (or didn't pick up before) about the autofit fudge
factor.

So yes, I'll see if I can't work your code into an event. That sounds
good.

(And, I'm not worried about the user resizing the text afterward. The
user is me, and if I ever need to resize--which will likely happen--
I'll just go thru them all and re-fire the event.)

Again: I Am Not Worthy; I Bow Down Before You; etc. etc.

***