VBA为Excel表格指定列应用货币格式报错，求正确方法

Hey there! Let's break down what's going wrong here. The error pops up because you're trying to apply Style or NumberFormat directly to the ListColumn object—and that object doesn't expose those properties. Instead, you need to target the actual cell range associated with that table column, which you can access via the DataRange property of the ListColumn.

Here are the corrected versions of your two approaches:

Corrected Approach 1: Using Built-in Currency Style #

Sheet1.ListObjects("MyTable").ListColumns("Balance").DataRange.Style = "Currency"

This applies Excel's built-in "Currency" style to all data cells in the "Balance" column of your table.

Corrected Approach 2: Using Custom Euro Currency Format #

Sheet1.ListObjects("MyTable").ListColumns("Balance").DataRange.NumberFormat = _
"_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"

This sets your custom Euro currency formatting directly on the column's data cells.

Optional: Format the Header Too #

If you want the table's header cell for "Balance" to match the currency style, you can target the HeaderRowRange property separately:

With Sheet1.ListObjects("MyTable").ListColumns("Balance")
    .DataRange.Style = "Currency"
    .HeaderRowRange.Style = "Currency" ' Applies style to the header cell
End With

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