VBA ListObject inconsistent naming rules when usin

Question

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.

Answer 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.