VBA中能否像Excel公式一样引用表格列名（如@[Condition]）？

Absolutely! You can absolutely leverage structured references (the @[Column Name] style you love from Excel formulas) in VBA when working with Excel Tables (ListObjects). This is way cleaner than hardcoding cell addresses like $B$27, and it’ll keep your code robust even if you rearrange columns or expand the table later. Here’s how to implement your specific use case:

Step 1: Confirm Your Data is in an Excel Table #

First, make sure your data is formatted as an official Excel Table (not just a regular range). You can convert a range to a table by selecting it and pressing Ctrl+T, then checking "My table has headers". Note down the table’s name (you can find this in the Table Design tab on the ribbon).

Step 2: VBA Code to Use Structured References & Trigger the UserForm #

We’ll use a Worksheet Change Event to detect when the Condition column is updated to "Other", then show your custom user form. Here’s the code:

Worksheet Module Code #

Open the worksheet module (right-click the sheet tab > View Code) and paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Set tbl = Me.ListObjects("Table1") ' Replace "Table1" with your actual table name
    
    ' Check if the edited cell is in the Condition column of your table
    If Not Intersect(Target, tbl.ListColumns("Condition").DataBodyRange) Is Nothing Then
        If Target.Cells.Count = 1 Then
            ' Get the current row in the table
            Dim currentTableRow As ListRow
            Set currentTableRow = tbl.ListRows(Target.Row - tbl.HeaderRowRange.Row)
            
            ' Check if the Condition value is "Other" (case-insensitive)
            If UCase(currentTableRow.Range(tbl.ListColumns("Condition").Index).Value) = "OTHER" Then
                ' Disable events temporarily to avoid looping when we update the table
                Application.EnableEvents = False
                
                ' Show your user form modally
                frmReason.Show vbModal
                
                ' Update the Works Completed column based on user selection
                Select Case frmReason.SelectedOption
                    Case "Repair"
                        currentTableRow.Range(tbl.ListColumns("Works Completed").Index).Value = "For Repairs"
                    Case "Replace"
                        currentTableRow.Range(tbl.ListColumns("Works Completed").Index).Value = "For Replacement"
                    Case "Review"
                        currentTableRow.Range(tbl.ListColumns("Works Completed").Index).Value = "For Review"
                End Select
                
                ' Re-enable events and clean up
                Application.EnableEvents = True
                Unload frmReason
            End If
        End If
    End If
End Sub

User Form Code #

Assuming your user form is named frmReason with three buttons (e.g., cmdToBeRepaired, cmdForReplacement, cmdForReview), open the form’s module (right-click the form > View Code) and paste this:

' Public variable to store the user's selection
Public SelectedOption As String

Private Sub cmdToBeRepaired_Click()
    SelectedOption = "Repair"
    Me.Hide
End Sub

Private Sub cmdForReplacement_Click()
    SelectedOption = "Replace"
    Me.Hide
End Sub

Private Sub cmdForReview_Click()
    SelectedOption = "Review"
    Me.Hide
End Sub

' Prevent the user from closing the form without selecting an option
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        MsgBox "Please select an option from the buttons.", vbExclamation
    End If
End Sub

Key Notes #

• No Hardcoded Cells: Instead of $B$27, we reference columns by their names ("Condition", "Works Completed"), so your code won’t break if you move columns around.
• Current Row Only: The currentTableRow variable ensures we only modify the row where the Condition was set to "Other", just like the @[Column] behavior in formulas.
• Event Safety: We use Application.EnableEvents = False to stop the Worksheet_Change event from triggering again when we update the Works Completed cell.

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