Maximunm # of Spreadsheets in any Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Folks:


What is the maximum number of spreadsheets that can be opened in any
workbook.
I am using the Excel 2000 platform



Thanks,
John.
 
Hi John,

All these and similar limits can always be found in Help>Specifications

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Depends on what your PC hardware will support.
|
| JoJo wrote:
|
| > Folks:
| >
| >
| > What is the maximum number of spreadsheets that can be opened in any
| > workbook.
| > I am using the Excel 2000 platform
| >
| >
| >
| > Thanks,
| > John.
| >
| >
|
 
hi
in xl help type the word specification. lists all xl specs
max number of worksheets is limited by systme resorces i.e. memory, hardrive
ect.

Regards
FSt1
 
Just a silly addition.
I haven't done this is years, but out of educational curiosity only, and
with Excel 2007...
From vba, and with immediate window displayed...

Sub HowManyWorksheets()
Do
Worksheets.Add
Debug.Print Worksheets.Count
Loop
End Sub

I keep getting 5,448. (Before crashing I might add!!)
Well, that's interesting to me! That's the same number I had around Excel
97+ era.
However, I now have 2 Gb of memory. However, the sheets are bigger now
also.
Hmm. That number seems very strange to me. I am sure it is just a
coincidence that it is close to array limit 5461. ??

Someone mentioned years back that they broke the 5,400 limit by running the
macro from the worksheet using Tools | Macro Menu. Once, and only once, I
too broke the 5,400 limit by doing the same thing. However,...I never did
get that feat to repeat.

I lost my notes, but I think the stack calls were limited to just over 2,000
also.
Looks like they are more now.

Option Explicit
Dim k

Sub Test()
k = k + 1
Debug.Print k
Call Test
End Sub

I run out of Stack now at 5,571.

Useless info I know. I was just curious with Excel 2007. :>~
 
Don Guillett said:
Memory
Click to expand...
....

That may be what the docs say, but it's limited by available system
resources, which could encompass less than available memory.
 
Dana DeLouis said:
Just a silly addition. ....
I keep getting 5,448. (Before crashing I might add!!) ....
I run out of Stack now at 5,571.
Click to expand...
....

FWIW, I launch Excel 2003 with a few other programs running and type

=INFO("memavail")

in the formula bar and press [F9] rather than [Enter]. Excel returns
1048576, i.e., 2^20, so 1MB. So it looks like this INFO attribute is
so much rubbish.

Using a variation on your test, with an internal static accumulator,
Excel 2003 runs out of stack at 5572.

As for sheets, my default workbooks start with 4 worksheets. Adding
more by macro, Excel 2003 also dies at 5448. Also crashes. So looks
like 5448 is an effective upper limit that hasn't changed with Excel
2007.
 
Excel 2003 also dies at 5448. Also crashes.

Thank you very much for that feedback. That is interesting!!
So it looks like this INFO attribute is so much rubbish.
Click to expand...

It appears that Microsoft agrees with you.
I just noticed this documentation in Excel 2007.

Important In previous versions of Microsoft Office Excel, the "memavail",
"memused", and "totmem" type_text values, returned memory information. These
type_text values are no longer supported and now return a #N/A error value.

Hmmm. :>~
--
Dana DeLouis
Windows XP & reluctantly using Excel 2007


Harlan Grove said:
Dana DeLouis said:
Just a silly addition. ...
I keep getting 5,448. (Before crashing I might add!!) ...
I run out of Stack now at 5,571.
Click to expand...
...

FWIW, I launch Excel 2003 with a few other programs running and type

=INFO("memavail")

in the formula bar and press [F9] rather than [Enter]. Excel returns
1048576, i.e., 2^20, so 1MB. So it looks like this INFO attribute is
so much rubbish.

Using a variation on your test, with an internal static accumulator,
Excel 2003 runs out of stack at 5572.

As for sheets, my default workbooks start with 4 worksheets. Adding
more by macro, Excel 2003 also dies at 5448. Also crashes. So looks
like 5448 is an effective upper limit that hasn't changed with Excel
2007.
Click to expand...
 
Back
Top