Substitute worksheet function considered harmful: can createunopenable workbooks with cell values greater than 32k
 

The following macro will create a value in cell A2 that has more than
32k characters.

Sub foo()
Range("A1").Value = String$(2 ^ 15 - 1, "a")
Range("A2").Formula = "=Substitute(A1, ""a"", ""ab"")"
Range("A3").Formula = "=len(A2)"
End Sub

If you now save this workbook, it will not be openable by excel. We
discovered this behavior quite by accident when replacing the tab
character in a cell with spaces. Unfortunately, the cell contained a
large text fragment, and when expanded it was 32k chars. Because the
workbook is fine until it is saved and reopened, and because the
worksheet had nearly 1 million cells, it took a while to track down.
Are there other ways to make seemingly valid workbooks that cannot be
opened?

Substitute worksheet function considered harmful: can create unopenable workbooks with cell values greater than 32k
 

In Excel 2003 I do get a message on opening "Data may be lost", but it opens and A3 is 65534

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Alok" wrote in message ...
| The following macro will create a value in cell A2 that has more than
| 32k characters.
|
| Sub foo()
| Range("A1").Value = String$(2 ^ 15 - 1, "a")
| Range("A2").Formula = "=Substitute(A1, ""a"", ""ab"")"
| Range("A3").Formula = "=len(A2)"
| End Sub
|
| If you now save this workbook, it will not be openable by excel. We
| discovered this behavior quite by accident when replacing the tab
| character in a cell with spaces. Unfortunately, the cell contained a
| large text fragment, and when expanded it was 32k chars. Because the
| workbook is fine until it is saved and reopened, and because the
| worksheet had nearly 1 million cells, it took a while to track down.
| Are there other ways to make seemingly valid workbooks that cannot be
| opened?

Substitute worksheet function considered harmful: can create unopenable workbooks with cell values greater than 32k
 

Very interesting!!!

It opens fine for me in Excel 2000 (SP-3).

--
Regards,
Bill Renaud

Substitute worksheet function considered harmful: can create unopenable workbooks with cell values greater than 32k
 

Cell A3 is 32767.

--
Regards,
Bill Renaud

Substitute worksheet function considered harmful: can createunopenable workbooks with cell values greater than 32k
 

On Dec 5, 11:28 am, "Niek Otten" wrote:
In Excel 2003 I do get a message on opening "Data may be lost", but it opens and A3 is 65534

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Alok" wrote in ...

| The following macro will create a value in cell A2 that has more than
| 32k characters.
|
| Sub foo()
| Range("A1").Value = String$(2 ^ 15 - 1, "a")
| Range("A2").Formula = "=Substitute(A1, ""a"", ""ab"")"
| Range("A3").Formula = "=len(A2)"
| End Sub
|
| If you now save this workbook, it will not be openable by excel. We
| discovered this behavior quite by accident when replacing the tab
| character in a cell with spaces. Unfortunately, the cell contained a
| large text fragment, and when expanded it was 32k chars. Because the
| workbook is fine until it is saved and reopened, and because the
| worksheet had nearly 1 million cells, it took a while to track down.
| Are there other ways to make seemingly valid workbooks that cannot be
| opened?

Ah! I ran windows update, and it was able to open it. Apparently, one
of the updates fixed it. Thanks for pointing this out!