Excel宏录制中表名单下划线变双下划线的原因及解决方法

Great question! Let me break down why this happens and how you can fix it, especially since you need tables named after their parent worksheets across multiple sheets.

Why the underscores are doubled #

Excel uses structured references when generating VBA code for table operations, and single underscores (_) have a special meaning here—they act as a wildcard that matches any single character (similar to how _ works in SQL). To avoid confusion between the wildcard and actual underscores in your table name, Excel automatically escapes single underscores by converting them to double underscores (__) in the recorded macro code.

This is just Excel's way of making sure the parser doesn't misinterpret your table name as a pattern with wildcards.

How to fix it #

You have two solid options here, depending on your workflow:

1. Manually correct the recorded macro code #

If you're working with a one-off macro, simply replace the double underscores back to single underscores in the Range reference. For example:
Change this:

Range("abc__xyz__0.5s_tbl[[#All],[Users]]").Select

To this:

Range("abc_xyz_0.5s_tbl[[#All],[Users]]").Select

This will make the code target your actual table name correctly.

2. Use ListObject references (more robust for multiple sheets) #

Since you have multiple worksheets with tables named after their parent sheet, using Excel's ListObject object model is a far better approach—it avoids the underscore escaping issue entirely and makes your code more maintainable.

Here’s an example of how to reference the table and set conditional formatting without relying on structured reference strings:

Sub SetTableConditionalFormatting()
    Dim ws As Worksheet
    Dim targetTable As ListObject
    Dim usersColumn As ListColumn
    
    ' Loop through every worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Get the table that matches the worksheet name
        Set targetTable = ws.ListObjects(ws.Name)
        ' Reference the "Users" column in the table
        Set usersColumn = targetTable.ListColumns("Users")
        
        ' Select the column (if you need to, though you don't always need to select for formatting)
        usersColumn.Range.Select
        
        ' Add your conditional formatting here
        ' Example: Highlight cells with value greater than 100 in red
        With usersColumn.Range.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="100")
            .Interior.Color = RGB(255, 0, 0)
            .Font.Bold = True
        End With
    Next ws
End Sub

This method directly interacts with the table object, so you don’t have to worry about Excel modifying your table name in the code. It’s also more flexible if you ever need to adjust the formatting later.

内容的提问来源于stack exchange，提问作者Sanjeev