VBA ListObject inconsistent naming rules when usin

问题:

You can name your table "my000" but you can't name it "my001". You also can't name it "bab002", "lal0101" or anything similar.

When using macros to create and rename tables I noticed that sometimes it puts underscore at the end of the table name (like myt001) with no valid reason. Excel prompts that a name should not be in conflict with existing name, start with number or include space or not allowed character. The names that I try seem completely correct, by Excel changes them to for instance to "my001_", disrupting other parts of my spreadsheet.

To reproduce: open blank Excel, create an empty table with Insert -> table then try to name it "ba001" or something similar. When trying to do the same with vba, it puts underscore at the end ("ba001_"), having no reason to do this.

I expect vba to give the (supposedly) correct names to ListObjects. Instead it puts underscores at the endings in unexpected situations.

回答1:

The conclusion based on what Mikku & Maeaex1 said, the table name is named in a convention that makes vba reference a cell "BAB001" due to ignoring the "00" within the name string.

Solution: change naming convention that is not consistent with Excel's cell naming convention, such as starting the table name string with an underscore " _ " which will also give the added benefit of when you reference it within excel when you start and type "=_" a list of all your tables and named range will appear instead of other functions that start with the same letters.