Unable to get the text property of the characters class

Question

This is an extract form an excel 2010 macro I’m working on in VBA, the function I’m having issues with is DelStrikethroughs

Whenever the value in a cell is "TRUE", "FALSE", or "#N/A" (when its "#N/A" the macro crashes) the function returns a null string. Upon further investigation it looks like the variable x.text is always blank and has the error "Unable to get the text property of the characters class" when i try to debug it.

Any ideas on how to fix this? (I'm happy for the function to return the original text if it cant remove the strike through text, but a proper solution is preferred)

Below is the code sample:

Sub testx()
    Dim testRange As Range
    Set testRange = selection
    Call DelStrikethroughs(testRange.Cells(1, 1))
End Sub


Function DelStrikethroughs(Cell As Range) As String
    'Returns the text value of a cell with strikethrough characters removed
    Dim NewText As String
    Dim iCh As Integer
    For iCh = 1 To Len(Cell)
        Dim x As Characters
        Set x = Cell.Characters(iCh, 1)
        On Error Resume Next '"On Error" is here to deal with blank characters
            If x.Font.Strikethrough = False Then
                NewText = NewText & x.text
            End If
        If Err.Number = 0 Then
            NewText = NewText
        Else
            NewText = NewText & x.text
        End If
    Next iCh
    DelStrikethroughs = NewText
End Function

A `Function` returns a value. You should be using a `Sub` because you don't want anything returned you just want to modify the cell you pass to the new `Sub`... — Chrismas007, Jan 04 '16 at 21:44
Your problem occurs when `Cell.Value` does not consist of characters. *Characters: Represents characters in an object that contains text* Errors and Booleans do not result in the cell containing text. One thing you could do is, using the 'MID' function, iterate through each character in the `Cell.Text` property. You might also test for errors in the cell, and Booleans, and decide what you want to do in that event. You could also just return the .TEXT property. EG: range("A1").text --> the contents without the strikethrough. — Ron Rosenfeld, Jan 04 '16 at 21:59
That is just an extract of the code, I'm not attempting to alter the workbook at all. the reason I'm returning the string is because there is further processing done based on business needs. — zaza, Jan 04 '16 at 22:07

score 1 · Accepted Answer · answered Jan 04 '16 at 22:19

Try this:

Sub testx()
    Dim testRange As Range, c As Range
    Set testRange = Selection
    For Each c In testRange
        c.Offset(0, 1).Value = DelStrikethroughs(c)
    Next c
End Sub


Function DelStrikethroughs(Cell As Range) As String
    'Returns the text value of a cell with strikethrough characters removed
    Dim NewText As String
    Dim iCh As Long, l As Long, ch As Characters

    On Error Resume Next
    l = Cell.Characters.Count
    On Error GoTo 0

    If l = 0 Then
        NewText = Cell.Text
    Else
        For iCh = 1 To l
            Set ch = Cell.Characters(iCh, 1)
            NewText = NewText & IIf(ch.Font.Strikethrough, "", ch.Text)
        Next iCh
    End If
    DelStrikethroughs = NewText
End Function

This will work for my purposes, interesting to see that there isn't a solution for the actual issue tho. — zaza, Jan 05 '16 at 23:19

score 0 · Answer 2 · answered Jan 05 '16 at 03:48

0

If all you want to do is return the text in the cell without any strikethrough, then try:

Function DelStrikethroughs(Cell As Range) As String
   DelStrikethroughs = Cell.Text
End Function

answered Jan 05 '16 at 03:48

Ron Rosenfeld

53,870
7
28
60

Unable to get the text property of the characters class

2 Answers2