Unable to set the Text Property of the Characters class

[Dan]

I have the following code in a VB6 program:

Function BoldMarkup(R As Range) As String

Dim N As Long
Dim S As String
Dim InBold As Boolean

If R.Cells.Count > 1 Then
Exit Function
End If
If R.HasFormula = True Then
Exit Function
End If
If Len(R.Text) = 0 Then
Exit Function
End If

If Len(R.Text) = 1 Then
If R.Characters(1, 1).Font.Bold Then
BoldMarkup = "<b>" & R.Text & "</b>"
Exit Function
End If
End If

For N = 1 To Len(R.Text)
If R.Characters(N, 1).Font.Bold = True Then
If InBold = False Then
S = S & "<b>" & R.Characters(N, 1).Text
InBold = True
Else
S = S & R.Characters(N, 1).Text
If N = Len(R.Text) Then
S = S & "</b>"
End If
End If
Else
If InBold = True Then
S = S & "</b>" & R.Characters(N, 1).Text
InBold = False
Else
S = S & R.Characters(N, 1).Text
End If
End If
Next N
BoldMarkup = S

End Function

Sub Test()

Dim CellRange As Excel.Range

For I = 1 To 200
For J = 1 To 11
Set CellRange = ExcelWorksheet.Cells(I, J)
TextStr = BoldMarkup(CellRange)
Next J
Next I

End Sub

If I set the upper limit of the "For J" loop to 1 (instead of 11), the code
works. However, when I have it loop on the first 11 columns in the worksheet,
it gives me the following error:

Unable to set the Text Property of the Characters class

The error occurs the first time that "R.Characters(N, 1).Text" is referenced
in the BoldMarkup function. I cannot figure out why I'm getting the error or
what to do to get around it. Help! Thanks!

 

[JLGWhiz]

If any of your cells in the (i, j) matrix are blank, you will get that
message because, unlike setting a cell font property, it cannot set the font
on a non-existent character. You are calling the Function which uses the
Characters function to change the font in a string. When VBA does not find
a string, it sends you the message. You might be able to fix that by
inserting On Error Resume Next into your macro that calls the BoldMarkup
function.

Sub Test()

Dim CellRange As Excel.Range
On Error Resume Next
For I = 1 To 200
For J = 1 To 11
Set CellRange = ExcelWorksheet.Cells(I, J)
TextStr = BoldMarkup(CellRange)
Next J
Next I
On Error GoTo 0

End Sub

 

[Dan]

Thank you for your reply. I was hopeful that that was my problem, but I added
some extra checks to make sure that the Text property is not null before
calling the BoldMarkup function. The error is occuring on a cell that Text is
3 characters long, so it's not null.

What is the difference between the Text property and the Value property?
Should I be referencing one or the other in this context?

Any other ideas for why I'm getting this error message?

Thanks!

 

[JLGWhiz]

One simple difference between Text and Value is that Text will work with
string data types but not numeric data types, whereas Value will generally
work with both. But that is another thing that the Characters function
looks for is the String data type, it will balk if the data type is numeric,
for instance a formula. Characters has to have a String value to work.

 

[Dan]

Thank you! That was the key I needed. The particular cell that was giving the
error contained a 3-digit number. I assumed, since I was accessing the Text
property, that this would be converted to a String data type. Instead, it
gave me the error. When I changed my code to use CStr with the Value
property, it works fine.

Thanks again for your help!