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

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.

--
Alexei Baskakov

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

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" wrote in message
...
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.

--
Alexei Baskakov

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

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! :)


--
Alexei Baskakov

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

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

"loyso" wrote in message
...
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! :)


--
Alexei Baskakov

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

Why can't you use the .value property?




loyso wrote:

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.

--
Alexei Baskakov

--

Dave Peterson

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

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 wrote:

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

"loyso" wrote in message
...
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! :)


--
Alexei Baskakov



--

Dave Peterson

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

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

"Dave Peterson" wrote in message
...
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 wrote:

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

"loyso" wrote in message
...
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! :)


--
Alexei Baskakov



--

Dave Peterson

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

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.

--
Alexei Baskakov