none
Using Characters collection in cells with more than 256 characters RRS feed

  • Question

  • I want to format separate words in a cell, but the text's cell has more than 256 characters and I can't do this by using Characters collection. Do you know a workaround?

    I was wondering: maybe I can generate a HTML string, send to clipoard and use ActiveSheet.Paste, so the active cell will get the formatting, and that supports more than 256 characters.

    Edit: by viewing this link, I remembered about Range.Value(xlRangeValueXMLSpreadsheet) and will also try that way.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br


    Tuesday, June 23, 2015 12:19 PM

Answers

  • Re:  Keep those colors he said

    One more time...
    '---
    Sub RetainCharacterColors()
     'Jim Cone - Portland, Oregon USA - June 2015
      Dim N As Long
      Dim arr() As Long
      Dim rng As Range
     
      Set rng = ActiveCell
      If rng.HasFormula = True Or IsEmpty(rng.Value) Then
        MsgBox "Cell contents are suspect  "
        Exit Sub
      End If
        
      ReDim arr(1 To Len(rng.Value))
     'Save colors
      For N = 1 To UBound(arr)
        arr(N) = rng.Characters(N, 1).Font.Color
      Next
     
      On Error Resume Next
      rng.Value = Mid(rng.Value, 2, 999)
      If Err.Number <> 0 Then
        MsgBox "Oops"
        Exit Sub
      End If
      On Error GoTo 0
     
     'Restore colors
      For N = 1 To Len(rng.Value)
        rng.Characters(N, 1).Font.Color = arr(N + 1)
      Next
    End Sub
    '---

    Jim Cone

               

    Wednesday, June 24, 2015 5:17 PM

All replies

  • Felipe, bom dia,

    Nunca utilizei, mas pesquisando na internet descobri que o VB6 tem um dicionário de objetos, que talvez atenda a sua necessidade.

    https://support.microsoft.com/en-us/kb/187234/en-us

    Você o conhece ?

    Abaixo um exemplo de formatação utilizando - o.

    http://stackoverflow.com/questions/27382297/vba-conditional-format-cell-based-on-whether-value-is-in-list-of-text

    Quanto ao limite eu não sei, teria que testar

    Tuesday, June 23, 2015 1:13 PM
  • Olá André, obrigado.

    Não seria isso. A coleção Characters não tem relação com a coleção VBA.Collection ou Scripting.Dictionary. Ela é uma coleção de caracteres que representa o texto e formatação de uma célula.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Tuesday, June 23, 2015 1:19 PM
  • Hi Felipe,

    >>I want to format separate words in a cell, but the text's cell has more than 256 characters and I can't do this by using Characters collection. Do you know a workaround?<<

    Would you mind sharing the problem more detail you were handling? As far as I test, I can format the text using Excel 2013 successfully which more than 256 characters.

    Here is an example that format the chartacters from 257 and length is 5:

    Sub initString()
    Dim aString As String
    For i = 1 To 255
    aString = aString + "A"
    Next i
    
    aString = aString + " Hello Word!"
    
    Range("A1").Value = aString
    Range("A1").Characters(257, 5).Font.Color = -16776961
    End Sub
    Here is the fingure for the result:

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 24, 2015 5:30 AM
    Moderator
  • Thank you Fei.

    My issue is that when I have more then 256 characters, the method Range.Characters.Delete doesn't delete any character. Please take a look in this sample file: https://onedrive.live.com/redir?resid=FB206A2D510E0661%21200816

    You see, the macro works only for the latter case:


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Wednesday, June 24, 2015 11:01 AM
  • Re:  remove first character
    HelloFCG,

    This works...
      targetRange.Value = Mid(targetRange.Value, 2, 999)

    Note: the above uses the Mid function, which is not  the same as the VBA.Mid statement.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 6:12 PM
    Wednesday, June 24, 2015 2:41 PM
  • Thanks Jim,

    But I'm using Characters.Delete instead of Mid because Characters retains the text formatting of partially-formatted cells. If I use Mid, I lost all internal formatting.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Wednesday, June 24, 2015 3:01 PM
  • Re:  Keep those colors he said

    One more time...
    '---
    Sub RetainCharacterColors()
     'Jim Cone - Portland, Oregon USA - June 2015
      Dim N As Long
      Dim arr() As Long
      Dim rng As Range
     
      Set rng = ActiveCell
      If rng.HasFormula = True Or IsEmpty(rng.Value) Then
        MsgBox "Cell contents are suspect  "
        Exit Sub
      End If
        
      ReDim arr(1 To Len(rng.Value))
     'Save colors
      For N = 1 To UBound(arr)
        arr(N) = rng.Characters(N, 1).Font.Color
      Next
     
      On Error Resume Next
      rng.Value = Mid(rng.Value, 2, 999)
      If Err.Number <> 0 Then
        MsgBox "Oops"
        Exit Sub
      End If
      On Error GoTo 0
     
     'Restore colors
      For N = 1 To Len(rng.Value)
        rng.Characters(N, 1).Font.Color = arr(N + 1)
      Next
    End Sub
    '---

    Jim Cone

               

    Wednesday, June 24, 2015 5:17 PM
  • Same error here.

    Office Professional Plus 2013 64 bits.

    I tested in office 2007 and obtain the same error.

    Wednesday, June 24, 2015 5:22 PM
  • Excellent idea, James. You saved the formatting in an array, processed the text and then recreated the formatting. Thank you.

    ---

    Edit: I still think that Characters.Delete 256 characters limitation should be fixed by Microsoft.



    Felipe Costa Gualberto - http://www.ambienteoffice.com.br


    Wednesday, June 24, 2015 5:23 PM
  • Hi Felipe Costa Gualberto,

    Thanks for the feedback and I am able to reproduce this issue in Excel 2013 too.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.
     
    Sorry for any inconvenience and have a nice day!

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 25, 2015 2:22 AM
    Moderator
  • Thank you Fei, I appreciate that.

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Thursday, June 25, 2015 1:16 PM