3

I have a VBA code that first deletes any tables that are in the workbook and creates new tables in excel in a loop and right now it is making about 1000s of them at once Currently I have the following table no.Table59860171 as the name of a table.

Question is will excel at any point run out of unique names for a table ?

This question arose because I'll be running this same process again and again so can this be a problem ?

If yes how can I avoid it ?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Stupid_Intern
  • 3,382
  • 8
  • 37
  • 74
  • When you say "Table" do you mean "Worksheet" or ["Excel table"](https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c)? – lc. Feb 05 '16 at 04:10
  • Excel Table ofcourse – Stupid_Intern Feb 05 '16 at 04:10
  • My apologies. Ignore my answer then. – lc. Feb 05 '16 at 04:10
  • 1
    The max size of table name is 255. http://stackoverflow.com/questions/3681868/is-there-a-limit-on-an-excel-worksheets-name-length. That should be more than enough. – Jules Feb 05 '16 at 04:35
  • Sorry I linked the wrong article Should be this. http://www.k2e.com/tech-update/tips/551-tips-naming-and-renaming-excel-tables. If I understand your question correctly, my original comment still valid. If not, please ignore the comment. – Jules Feb 05 '16 at 04:48
  • So what happens when the Table Name reaches Table9999999....(20 x 9's) ? – Stupid_Intern Feb 05 '16 at 04:56
  • 5
    Lets say you create 10000 tables each day of the year for 50 years. 10000*365*50 = 182500000. Total length Table182500000 is only 14. Don't worry, let the next generation handle the problem :) – Jules Feb 05 '16 at 05:09

1 Answers1

6

Yes, Excel will run out of table names. As Jules notes (and can be confirmed by trying long names in Excel), Excel table names are limited to 255 characters (as of the time of writing- I'm using Excel 2010). So, there is a finite number of table names available.

However, the number of possibilities is very, very high. It is not quite every combination of letters and numbers up to a 255 character string since some combinations are not allowed, such as names that could also be a cell reference (the table name "T100" is not allowed as it could also mean cell T100).

Assuming that you continue naming tables from "Table1" to "TableN": Your largest table name would be

Table9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999

If you added 100 Billion tables every second, you could add tables for

1,157,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407,407 days.

According to Sydney Observatory, the Earth will be engulfed by the Sun long before you ever run out of table names.

Feel free to check my math- correct me if I've made a mistake:

9,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999 / (100,000,000,000 * 60 * 60 * 24)

elmer007
  • 1,412
  • 14
  • 27
  • "the Earth will be engulfed by the Sun long before you ever run out of table names." Gee, what a lame excuse for bad coding practices .. :P (heh) – Ditto Nov 04 '16 at 16:24