Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit

[loyso]

Hi!

My case: I have an xls file with the only one worksheet.
The first cell of this worksheet contains a text more then 4 kb length.

The .Text property for that Cell returns only first 1024 characters. I see
this in the following example:

For Each ws In Worksheets
MsgBox ws.Cells(1, 1).Value
MsgBox ws.Cells(1, 1).Text
Next ws

Q: How can I get whole text from this cell?

Thanks in advance!


Bye.

 

[Peter T]

Cells can contain text with length up to 32k. But a msgbox can only display
1024 characters.

Sub test()
Dim s As String
Dim i As Long
Const SomeText As String = "This text is 32 characters long "

s = SomeText
For i = 1 To 1000
s = s & SomeText
Next

Range("a1") = s

s = ""
s = Range("a1")

MsgBox Len(s) ' 32032

MsgBox Mid(s, 32000, 32)

End Sub

Regards,
Peter T

 

[loyso]

Cells can contain text with length up to 32k. But a msgbox can only
display

1024 characters.

No! The problem isn't msgbox-related!
I see in integrated excel debugger that Len function returns 1024 for my
cell with 4k of text!

For Each ws In Worksheets
MsgBox Len(ws.Cells(1, 1).Text)
Next ws

Also displayes 1024 in message box.

Somebody - help me, please!

 

[Peter T]

Is the cell a formula beginning with =. If so you can neither Read nor Write
a formula with length > 1024 including the "=".

Otherwise, as I said previously a cell can contain text up to the 32K
character length limit, as my example routine demonstrated.

Regards,
Peter T

 

[Dave Peterson]

Why can't you use the .value property?

Hi!

My case: I have an xls file with the only one worksheet.
The first cell of this worksheet contains a text more then 4 kb length.

The .Text property for that Cell returns only first 1024 characters. I see
this in the following example:

For Each ws In Worksheets
MsgBox ws.Cells(1, 1).Value
MsgBox ws.Cells(1, 1).Text
Next ws

Q: How can I get whole text from this cell?

Thanks in advance!

Bye.

 

[Dave Peterson]

This may illustrate loyso's problem:


Option Explicit
Sub testme()
Dim myCell As Range
Set myCell = ActiveSheet.Range("a1")
myCell.Formula = "=rept(""asdf "",500)"
With myCell
Debug.Print "Value length: " & Len(myCell.Value)
Debug.Print "Text Length: " & Len(myCell.Text)
End With
End Sub

Returned:

Value length: 2500
Text Length: 1024

 

[Peter T]

Perhaps I misunderstood loyso's problem, and loyso - apologies if I did.

As you say, why not return myCell.Value, or as Value is the default property
simply:

myVar = myCell

Regards,
Peter T

 

[loyso]

This may illustrate loyso's problem:

Thank you, friends! I've found the problem.
The following repro illustrates my issue:

Option Explicit
Sub test()
Dim s As String
Dim i As Long
Const SomeText As String = "This text is 32 characters long "
Dim myCell As Range

Set myCell = ActiveSheet.Range("a1")

s = SomeText
For i = 1 To 1000
s = s & SomeText
Next

Debug.Print "Original length: " & Len(s)

ActiveSheet.Cells(1, 1).Value = s

s = ""
s = ActiveSheet.Cells(1, 1).Text

With ActiveSheet.Cells(1, 1)
Debug.Print "Value length: " & Len(myCell.Value)
Debug.Print "Text Length: " & Len(myCell.Text)
End With

End Sub

Output:
Original length: 32032
Value length: 32032
Text Length: 1024

Resolution: I'm using .Value everywhere instead of .Text and all is ok.