Excel limits?

[Expiry]

In a discussion about file storage, I jokingly said to a colleague that we should just save every file as book1, book2 etc.

That got me wondering what Excel's limit is. If I open a new file, it's book1, the next is book2.

Is there a limit?

What's the furthest you've ever got to (ooh, err).

 

[number kruncher]

Would the limit be subject to the amount of RAM available?

 

[Jon von der Heyden]

Well I don't think there's a limit in terms of file / workbook names. But the # of workbooks you can open is limited by memory. As is the number of sheets you can have in a workbook.

 

[WaterGypsy]

If you open a new book you get Book1, the next new book you open is Book2, if you then close Book1 (to avoid memory limits) and open another new one you get Book3 .... probably ad infinitum - well at least until you get very bored

 

[Norie]

Why not find out?

All you should need to do is loop some simple code.

You could even try passing it off as 'research' or something.

 

[number kruncher]

And watch your computer slowly grind to a halt !!!

 

[Norie]

Or explode, spontaneously combust, or both.

I know my laptop would do the latter if given the chance.

 

[Jon von der Heyden]

I believe it should throw "Out of memory" error, or similar.

I don't fancy testing it, but if anyone wants to:

Public Sub SuperBored()
    Dim wkb As Workbook
    
    On Error GoTo err_exit
    Do
        Workbooks.Add
    Loop
    
err_exit:
    For Each wkb In Workbooks
        If Not wkb Is ThisWorkbook Then
            If Workbooks.Count = 2 Then
                MsgBox "Last workbook created: " & wkb.Name
            Else
                wkb.Close SaveChanges:=False
            End If
        End If
    Next wkb
End Sub

 

[taurean]

Or explode, spontaneously combust, or both.

I know my laptop would do the latter if given the chance.

Public Sub NoriesFieryLoop()
Dim SafetyLimit as Distance
Do
StepAway = StepAway + 1
'Place your Fiery Code here
While SafetyLimit < msoSafeDistance
End Sub

 

[number kruncher]

I'm not scared...

I ran the code and got to Book697, before, I got the following message.

"Excel cannot complete this task with available resources. Choose less data or close other applications."

The only Apps open was an RDP link to an external server.

Oh, and I have 12GB of RAM onboard.