In line -
wrote in message news
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