[Excel VBA] How to Auto-Set Table Formulas Using Structured References ([@ColumnName])

2025年12月26日

In Excel Tables (ListObject), using “Structured References” allows you to set formulas that automatically apply calculations to every row.

You can also use this feature from VBA to apply formulas like =[@Price]*[@Quantity] to an entire table column at once.

In this article, I will explain how to add a new column and set a formula using structured references via VBA.

Use Cases

Automatically adding a “Subtotal” column calculated as Price × Quantity.
Adding calculation columns to data entered via Excel forms.
Writing VBA code that adapts flexibly to the table structure.

Sample Code: Adding a Calculated Column

This code targets a table named “SalesData” and adds a “Subtotal” column that multiplies the Price by the Quantity.

Sub AddCalculatedColumn()

    Dim salesTable As ListObject
    Dim calcField As ListColumn

    ' Target the table named "SalesData"
    Set salesTable = ActiveSheet.ListObjects("SalesData")

    ' Add a new column named "Subtotal" to the end
    Set calcField = salesTable.ListColumns.Add
    calcField.Name = "Subtotal"

    ' Set formula using structured reference (@ refers to the current row)
    calcField.DataBodyRange.Formula = "=[@Price]*[@Quantity]"

End Sub

Explanation: What are Structured References?

Structured references are a way to specify cells based on the table structure rather than cell addresses (like A1 or B2).

Syntax	Description
`[@ColumnName]`	Refers to the specific column in the current row (e.g., `[@Price]`).
`[ColumnName]`	Refers to the entire column (used in functions like SUM).
`TableName[@ColumnName]`	A reference that includes the table name.

The [@ColumnName] format is specialized for row-by-row calculations and can be used directly in VBA formulas.

Tips to Avoid Errors

Special Characters: If the column name contains spaces or symbols, you must enclose it in extra brackets, like [@[Column Name]].
Duplicate Columns: Running .Add repeatedly will cause an error if the column already exists. It is safer to add a check to see if the column exists before adding it.

Advanced: Calculating the Total

You can also use structured references to calculate the sum of the entire column.

' Calculate the total of the Subtotal column (using entire column reference)
ActiveSheet.Range("G1").Formula = "=SUM(SalesData[Subtotal])"

Summary

Structured references are a robust and readable way to manage formulas in Excel tables.

You can easily handle the [@ColumnName] format from VBA.
It is very convenient for automatically adding calculation columns to business spreadsheets.

By combining “Adding Columns” and “Setting Formulas with Structured References,” you can create flexible and reliable automation tools. Please try this to improve your work efficiency.

よかったらシェアしてね！