Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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. *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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 wrote in message ... 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. *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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. *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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. *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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. *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you find WHERE Excel splits your wrapped string?
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. *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should allow at least 2 vertical and horizontal splits. | Excel Worksheet Functions | |||
Multiple screen splits for same worksheet in Excel | Excel Discussion (Misc queries) | |||
Excel should have multiple window splits . | Excel Worksheet Functions | |||
Find String Excel VBA | Excel Programming | |||
Find a string in excel | Excel Programming |