Testing Concatenating with styles

[DocAElstein]

This Thread is related to these forum Threads:
' ' https://www.mrexcel.com/board/thread.../#post-3954687 https://excelfox.com/forum/showthrea...ll=1#post15170




The Excel VBA Character.Text Property seems to have a Bug or possibly a feature not documented or known about, ( at least not known about by me until recently ( https://excelfox.com/forum/showthrea...ll=1#post15174 ) )
All is well and as expected until your number of characters in a cell goes over 255. After that it doesn’t work ( in “write” mode). It doesn’t error, but it seems to do nothing.
If you already have a long text string in a cell with more than 255 characters , then the other things to do with Character seems to work. For example you can change .Font styles on individual characters within the long text string. It seems to be just the .Text Property ( in “write” mode ) that does not work when applied to text in a cell that has more than 255 characters.
( Note also, the .Text Property in “read” mode also works OK for a cell with more than 255 characters )

In the demo below you can see that I have a test range with various texts in column B, (the character length, ( number of characters) of the text is given in column A) . The demo macro below copies that test range to the right and then attempts to use .Text to put the text of “Test” in to some cells in column B.
( Also in demo code section '2b we attempt to use “in reverse” the .Text Property to retrieve ( “read” ), the text at various parts of various cell text strings. This does not appear to have the same Bug/Characteristic: it does not seem to stop working when a cell has more than 255 characters )
CharacterTextPropertyTests.JPG : http://i.imgur.com/Qt03lL8.jpg

Sub ChrTextLimitTests()  '   https://excelfox.com/forum/showthread.php/2688-Testing-Concatenating-with-styles?p=15173&viewfull=1#post15173
Rem 0 Worksheets data info
Dim WsTest As Worksheet: Set WsTest = ThisWorkbook.Worksheets("ChrTextLimit")
Rem 1(a) Copy test range
 WsTest.Range("A9:B25").Copy
 WsTest.Range("D9:E25").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
 Let Application.CutCopyMode = False
Rem 2 tests on copied test range
    With WsTest
  ' 2a Putting text in
    ' Cell of spaces  256 ; 255  Attempt to put  Text  at start
    .Range("E10").Characters(1, 4).Text = "Test": .Range("E10").Characters(1, 4).Font.Color = vbGreen    '  Doesn't work - no  Text  put in, so not text to color either
    .Range("E11").Characters(1, 4).Text = "Test": .Range("E11").Characters(1, 4).Font.Color = vbGreen    '; Works, puts text in and colors it
    
    ' Cell is empty initially  Attempt to put  Text  at
    .Range("E12").Characters(1, 4).Text = "Test": .Range("E12").Characters(1, 4).Font.Color = vbGreen    '  Works
    
    ' Cell of Text    250 ; 250 ; 256 ; 255
    .Range("E13").Characters(1, 4).Text = "Test": .Range("E13").Characters(1, 4).Font.Color = vbGreen    '; Works
    .Range("E14").Characters(1, 4).Text = "Test": .Range("E14").Characters(1, 4).Font.Color = vbGreen    '; half works... No  Text  is put in , but the existing text gets colored
    .Range("E15").Characters(1, 4).Text = "Test": .Range("E15").Characters(1, 4).Font.Color = vbGreen    '; Works
    .Range("E16").Characters(250, 4).Text = "Test": .Range("E16").Characters(250, 4).Font.Color = vbGreen '; Works
    
    ' Cell is empty initially  Attempt to put  Text  at some point at a high start character
    .Range("E17").Characters(250, 4).Text = "Test": .Range("E17").Characters(250, 4).Font.Color = vbGreen '  Text  is put in at the start instead of the referred to place,  no coloring done at the referred to place which is not there
    .Range("E18").Characters(251, 4).Text = "Test": .Range("E18").Characters(251, 4).Font.Color = vbGreen '  Text  is put in at the start instead of the referred to place,  no coloring done at the referred to place which is not there
    .Range("E19").Characters(252, 4).Text = "Test": .Range("E19").Characters(252, 4).Font.Color = vbGreen '  Text  is put in at the start instead of the referred to place,  no coloring done at the referred to place which is not there
    .Range("E20").Characters(1000, 4).Text = "Test": .Range("E20").Characters(1000, 4).Font.Color = vbGreen 'Text  is put in at the start instead of the referred to place,  no coloring done at the referred to place which is not there
    
    ' Cell of text  256 ; 270
    .Range("E21").Characters(251, 4).Text = "Test": .Range("E21").Characters(251, 4).Font.Color = vbGreen '  half works... No  Text  is put in , but the existing text gets colored
    .Range("E22").Characters(254, 7).Text = "Test": .Range("E22").Characters(254, 7).Font.Color = vbGreen '  half works... No  Text  is put in , but the existing text gets colored
    
    ' Workaround to add text at a specific place in a cell
    Dim StrTemp As String: Let StrTemp = .Range("E23").Value
     Mid(StrTemp, 1, 4) = "Test"
    .Range("E23").Value = StrTemp: .Range("E23").Characters(1, 4).Font.Color = vbGreen                    '  Works - well it would, wouldn't it - my workarounds usually do work, that's wot they are for
     Let StrTemp = .Range("E24").Value
     Mid(StrTemp, 1, 4) = "Test"
    .Range("E24").Value = StrTemp: .Range("E24").Characters(249, 4).Font.Color = vbGreen                  '  half Works - we lose the
    End With ' WsTest
Stop
'( 1(b)  recopy test range )
  '2b  reading the text .....   all work!
    With WsTest
    Debug.Print "B10   |" & .Range("B10").Characters(1, 4).Text & "|"  '  Cell info and pipe added for demo purposes so that we can see easilly text that is just spaces
    Debug.Print "B11   |" & .Range("B11").Characters(1, 4).Text & "|"
    
    Debug.Print "B13   |" & .Range("B13").Characters(1, 4).Text & "|"
    Debug.Print "B14   |" & .Range("B14").Characters(1, 4).Text & "|"
    Debug.Print "B15   |" & .Range("B15").Characters(1, 4).Text & "|"
    Debug.Print "B16   |" & .Range("B16").Characters(250, 4).Text & "|"

    
    Debug.Print "B21   |" & .Range("B21").Characters(251, 4).Text & "|"
    Debug.Print "B22   |" & .Range("B22").Characters(254, 7).Text & "|"
   
    End With ' WsTest
End Sub

The conclusion seems to be that Character.Text in “write mode” doesn’t work if the cell has more than 255 characters

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA