Substitute worksheet function considered harmful: can createunopenable workbooks with cell values gr

A

Alok

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?
 
N

Niek Otten

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

| 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?
 
A

Alok

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


| 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?
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating identical print layout from copied worksheet 0
Consolidate Workbooks with Single header Row 7
Converting Cell Values Through Automation 0
Switch to diff workbook with file name LIKE based on value in a ce 7
New Workbooks for unique values 5
Resetting Data Validation with VBA 9
Combining two different subs w/ similar variables 10
Hlep with code referencing an addin file. 4

Top