Number Of Worksheets in an Excel workbook

User avatar
Argus
GoldLounger
Posts: 3020
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

ChrisGreaves wrote:
14 Jul 2021, 16:57
I have assumed that for this (or any other BBS/Lounge) that I really want only one email notification between visits.
If I visit a Lounge on Monday, and not again until Friday, I want just one notification per thread during that interval. In particular I do NOT want an email notification for every new post or reply in a single thread.
Well I can certainly see that side of the question, and have seen it for a long time, Chris. And if only the reply was quoted in the notification, it would be great. One reason why it can be good: in (old) times it could be that you were away, and mostly used your email client, not browsing the web. You will also find that you can start a thread, be away from the lounge, get one notification, eventually return, and find that it really took off after that first reply with two pages or more, and you lost the whole thing.
ChrisGreaves wrote:
14 Jul 2021, 16:57
I can see that someone else might want to have an email notification for every post/reply in a thread; a barrage of email notifications for the one thread would indicate a flurry of activity which, presumably, would be of interest to someone. Not, perhaps, to me.
Now that you mention it; it's true that if a certain Bonavistian participates in the thread you might experience something like that. :smile: Didn't think of that. :evilgrin:

The really unwanted feature, in my opinion, is to get an email notification (that doesn't tell you what was in the reply) for each updated thread while you are logged in and using the lounge ... It's not like there are other clues, icons changing colour, threads moving to the top of forums and the lounge Portal, and with recent versions of the forum software: a dedicated online notification service. Leaving the lounge, and then check your email (if they haven't bothered you during your lounge visit) will be: already read it, read it, read it, read it, replied, read it and laughed, read it, ignored, read it, read it ... :smile:
ChrisGreaves wrote:
14 Jul 2021, 16:57
Note the first and third arrowed entries, "New posts in the last day" and "New Posts last day".
Now what do you suppose is the difference between these two, apart from the fact that an Administrator has suggested that I use the latter ...
One "Rolling 24 hours" and one "on this day"; or one being an old feature going to be removed? But as Leif said ...

By the way, we'll be two on the plank, at least once.
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Number Of Worksheets in an Excel workbook

Post by rory »

ChrisGreaves wrote:
14 Jul 2021, 16:43
Hi Rory; if it's not a rude question (or answer!), why is it taking so long?
If you are issuing a Workbook.Save after every added worksheet, disabling that Save, might deliver your maximum within about five minutes or so :scratch:
Cheers
Chris
Because it's not exactly stable. Any attempt to open the VBE crashes Excel (I suspect the project explorer can't cope) and any error seems to simply cause excel to restart, so really I'm just carrying on (slowly) out of sheer stubbornness. :) 8773 currently.
Regards,
Rory

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

rory wrote:
15 Jul 2021, 08:06
Because it's not exactly stable. Any attempt to open the VBE crashes Excel (I suspect the project explorer can't cope) and any error seems to simply cause excel to restart, so really I'm just carrying on (slowly) out of sheer stubbornness. :) 8773 currently.
Hi Rory, and thanks for this reply.
I have explored the Help files in Excel2003 and taken a cursory glance at a web search.
Documentation on the allowable values for the .Add Count= is meagre.

I essayed with Count-255, but Count=500 and upwards is rejected.

Code: Select all

Sub MaxWorksheets()
    Dim dt As Date
    dt = Now
    While True
        If Worksheets.Count < 5200 Then
            Worksheets.Add , Count:=255
        Else
            Worksheets.Add
        End If
        Application.Caption = Format(Worksheets.Count, "#,##0") & Format(Now() - dt, " hh:mm:ss")
        DoEvents
    Wend
    Application.Caption = Format(Now() - dt, "hh:mm:ss")
End Sub
Untitled.png
I reduced my build time to 1m8s, with Application.Caption, DoEvents, and Screen Updating as overhead.

At the point of 5,448 sheets, Excel2003 crashes and reloads itself with a recovered copy of the workbook.

How are you "carrying on (slowly) out of sheer stubbornness"? That is, once Excel has reached 5,448 (in my case) and refused to budge, how do i squeeze "just one more worksheet" of of the miscreant?
Thanks
Chris
You do not have the required permissions to view the files attached to this post.
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
Argus
GoldLounger
Posts: 3020
Joined: 24 Jan 2010, 19:07

Re: Number Of Worksheets in an Excel workbook

Post by Argus »

ChrisGreaves wrote:
15 Jul 2021, 12:01
That is, once Excel has reached 5,448 (in my case) and refused to budge, how do i squeeze "just one more worksheet" of of the miscreant?
Not Rory, :smile: but some other input from MSFT.

https://support.microsoft.com/en-us/off ... 9d656771c3
Maximum limits of memory storage and file size for Data Model workbooks

32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.

64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources.

Beginning with Excel 2016, Large Address Aware functionality lets 32-bit Excel consume twice the memory when users work on a 64-bit Windows operating system. For more information, see Large Address Aware capability change for Excel.

Note: Adding tables to the Data Model increases the file size. If you don’t plan to create complex Data Model relationships using many data sources and data types in your workbook, uncheck the Add this data to the Data Model box when you import or create tables, pivot tables, or data connections.
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Number Of Worksheets in an Excel workbook

Post by rory »

ChrisGreaves wrote:
15 Jul 2021, 12:01

How are you "carrying on (slowly) out of sheer stubbornness"? That is, once Excel has reached 5,448 (in my case) and refused to budge, how do i squeeze "just one more worksheet" of of the miscreant?
Thanks
Chris
I don't know whether excel 2003 will allow you to carry on. My 64bit M365 allows me to keep going as long as the VBE isn't opened. Currently at 10,939 sheets.
Regards,
Rory

User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Worksheets in an Excel workbook

Post by Leif »

Well I read what Uncle Rory wrote...

I added the following macro:

Code: Select all

Sub MaxWorksheets()
    While True
        If Worksheets.Count < 10000 Then
            Worksheets.Add , Count:=200
        Else
            Worksheets.Add
        End If
        Application.Caption = Worksheets.Count
        ThisWorkbook.Save
        DoEvents
    Wend
End Sub
Then added a button to run the macro, closed the VBE, and clicked on the button.

I must admit Excel did become quite stodgy after 6,000-7,000, but the 5,448 barrier can be broken:
    
x.jpg
    
It only took a few minutes to get to the 10K mark, but by this point, I was beginning to lose the will to live.

(Office 2019 32-bit with 16GB on W10 64-bit)
You do not have the required permissions to view the files attached to this post.
Leif.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

Argus wrote:
15 Jul 2021, 12:21
... but some other input from MSFT. https://support.microsoft.com/en-us/off ... 9d656771c3
I note with disgust that they still haven't got around to documentation for Excel2003 :evilgrin:
... and add-ins that run in the same process.
Untitleda.png
I shall try again without the two Addins!
Cheers
Chris
You do not have the required permissions to view the files attached to this post.
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

rory wrote:
15 Jul 2021, 14:03
I don't know whether excel 2003 will allow you to carry on. My 64bit M365 allows me to keep going as long as the VBE isn't opened. Currently at 10,939 sheets.
Why is yours running so slowly?
Untitled.png
I reach just shy of 2^16 in a mere 16m 38s.
Are you using Workbook.Save in an attempt to free up used memory?
Cheers
Chris
You do not have the required permissions to view the files attached to this post.
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

Leif wrote:
15 Jul 2021, 15:42
... Then added a button to run the macro, closed the VBE, and clicked on the button.
Why add a button? Isn't that just a bit of extra flab in memory?
Why not Tools, Macro, Macros?
It only took a few minutes to get to the 10K mark, but by this point, I was beginning to lose the will to live.
Please see my figures of a few minutes ago, 65K worksheets in under twenty minutes.

Might this be because Excel2003 is much "leaner" than a more complicated Excel 2019 or 365?
Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

Argus wrote:
14 Jul 2021, 20:42
By the way, we'll be two on the plank, at least once.
Maybe we should split off a new thread "Number of pirate loungers that can fit on a plank"?
Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Number Of Worksheets in an Excel workbook

Post by jstevens »

I lost track. Do we have a table that tells us who generated how many sheets?
Regards,
John

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

jstevens wrote:
15 Jul 2021, 20:37
I lost track. Do we have a table that tells us who generated how many sheets?
I was thinking of building such a tabulation in my 65,532nd sheet, but alas ...
Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

jstevens
SilverLounger
Posts: 2432
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Number Of Worksheets in an Excel workbook

Post by jstevens »

Chris,

Is it a coincidence that you’re approaching 65536 which is the row limitation of a sheet in Excel 2003?

Just adding to the mystery. :scratch:
Regards,
John

User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Worksheets in an Excel workbook

Post by Leif »

ChrisGreaves wrote:
15 Jul 2021, 18:12
Why add a button? Isn't that just a bit of extra flab in memory?
Why not Tools, Macro, Macros?
The button allowed me to save fractions of a second by only having to click on the button re-running under different scenarios.
ChrisGreaves wrote:
15 Jul 2021, 18:12
Might this be because Excel2003 is much "leaner" than a more complicated Excel 2019 or 365?
Might be! I had originally tested using .xlsm format - testing with .xls format increased the speed.
Leif.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

jstevens wrote:
16 Jul 2021, 00:26
Is it a coincidence that you’re approaching 65536 which is the row limitation of a sheet in Excel 2003?
It is definitely a coincidence in that it is incident with another fact.
That said, I suppose if I started the test with only one sheet (instead of my default of three) and arrived two sheets closer to the magic power of ten we might learn something.
As we might if I added sheets two at a time but deleted one sheet each time, 2 steps forward 1 back. If that reduced the maximum we might suppose that Excel's garbage collection of memory had a fault. But then if we learned that some of us wouldn't be surprised.

I have long moaned about that default file-limit of 9 in the File menu system. That still looks to me as if some programmer set aside a 1-character (rather than 1-byte) storage area for the character-form of a digit. A legacy of 80-column punched cards, imposed on transistor technology.

In 1978 I took money off the Brits just for knowing that £83,886.07 was one penny less than two raised to the twenty-fourth power and that the mainframe was an ICL 1903 with a 24-bit word. The payroll would be out by £83,886.07 each week in a different field. All I had to do was work out which of the COBOL 1-word COMPUTATIONAL fields was overflowing this week and change it to a 2-word COMPUTATIONAL while trying to make it appear as intensely deep sleuthing. Since then I have long been impressed by powers-of-two, so you could be on to something here!

Just adding to the mystery. :scratch:
Well, YES! And the mystery may well grow deeper while we wait for your tabulated history of this thread.

Me? I blame it all on certain Lounge Members fondness of free beer! :evilgrin: :flee: :flee:

Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

Leif wrote:
16 Jul 2021, 06:45
The button allowed me to save fractions of a second by only having to click on the button re-running under different scenarios.
Oh, I see. I have been exiting Excel, and then reloading it for each test, on the grounds that I had eliminated lingering garbage in memory. I figured that the maximum might be a design factor, or a poor programming limitation, or even a combination of both.
... Might this be because Excel2003 is much "leaner" than a more complicated Excel 2019 or 365?
... Might be! I had originally tested using .xlsm format - testing with .xls format increased the speed.
Increasing the speed but not affecting the maximum is a factor that concerns us as users, but not Excel as a workbook.
If I were saving the workbook after each Worksheets.Add, I would have to run overnight on this steam-driven laptop; but a run that takes undet twenty minutes means I can dash outside and check on my blackcurrant cuttings and fold some grass clippings bags and the 20-minte delay doesn't faze me at all!

If a format changes the maximum, that suggests that the maximum is a design limitation ("I'll set aside a 16-bit word to hold the number of worksheets) rather than a system configuration ("Now, do I have enough RAM memory free for another worksheet?")

I think that John (Stevens) should summarize not only maximums reached (and basic system configurations), but the various variable factors that might have an effect on maximums:-
(1) reload vs. not at start of run
(2) 2 forward 1 back
(3) a piece of user-data in each added sheet
etc.

Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
BobArch2
5StarLounger
Posts: 1145
Joined: 25 Jan 2010, 22:25
Location: Pickering, Ontario, Canada

Re: Number Of Worksheets in an Excel workbook

Post by BobArch2 »

ChrisGreaves wrote:
15 Jul 2021, 18:08
rory wrote:
15 Jul 2021, 14:03
I don't know whether excel 2003 will allow you to carry on. My 64bit M365 allows me to keep going as long as the VBE isn't opened. Currently at 10,939 sheets.
Why is yours running so slowly?Untitled.pngI reach just shy of 2^16 in a mere 16m 38s.
Are you using Workbook.Save in an attempt to free up used memory?
Cheers
Chris
I have been following this thread with amusement. Now that you have a workbook with over 65,500 worksheets, I have a few questions…
1) where are you going to find the time to fill them?
2) what are to going to fill each and every one of the worksheets with?
3) how are you going to remember what worksheet you used for project “x”?

I await your answers with bated breath.

(All said with TIC humour… except the last comment :hairout: )
Regards,
Bob

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Worksheets in an Excel workbook

Post by ChrisGreaves »

BobArch2 wrote:
16 Jul 2021, 16:02
u have a workbook with over 65,500 worksheets, I have a few questions…
1) where are you going to find the time to fill them?
2) what are to going to fill each and every one of the worksheets with?
3) how are you going to remember what worksheet you used for project “x”?
Dear Bob,
First off thanks for sending The Unbearable Heat of Summer this way. 26c as I type this inside while I wait for the air outside to cool enough to light my barbecue fire.

1) I have MADE the time (just for YOU) to fill the worksheets with TLC(1), as you will see in the VBA code in the attached zipped workbook.
2) The cells will be filled with a six-character string which is, I feel, well above the average cell length for most workSHEETS.
3) Each worksheet will be assigned a unique ascending number by Excel comme l'habitude; there is no need for me to do useless work :evilgrin:

The first worksheet bears my very rough calculations.
4) Addition of a sheet appears to take 24 seconds and the increase in saved file size is about 100,000 bytes per worksheet.
5) If I reach my previous benchmark of 65,531 worksheets, I shall be able to report back in eighteen days, give or take.
6) If I delete the previously saved workbook immediately after the current workbook is saved, I will have plenty of space on my data drive.
7) If I maintain an audit-trail of workbooks, I shall run out of drive space after 1994(2) worksheets have been added (and hence 1994 workbooks saved).
8) In this case I should be able to report back to you tomorrow about 4:55, which will be three in the morning your time. I will phone you directly, so as not to wake up other members of The Lounge.

I hope that this helps.
Cheers
Chris

[Next Morning]
214324.png
Well, how was I supposed to know that Microsoft would decide to update my machine and invite me to use Microsoft Edge. I am, naturally, blaming Microsoft for my inability to complete the run, although Excel2003 might just have baulked at 1 1.5 GB workbook.

(1) Tender Loving Code
(2) Back in the day when I was driving past your place to Belleville, Brockville, and Montreal, he wrote, studiously avoiding alliterative text.
You do not have the required permissions to view the files attached to this post.
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt