Excel VBA复制箱线图后数值格式异常问题求助

I feel your pain—dealing with 4800 sheets manually is impossible, and messing up charts with a failed decimal-formatting macro just adds to the frustration. Let’s break down how to fix this weird number formatting shift where your Cmin value shows as ,320.0 instead of the correct 0,32 when copying box plots via VBA.

Why This Happens #

This glitch usually occurs because VBA’s chart copy-paste operation doesn’t properly inherit the source data’s number formatting, or the pasted chart triggers an incorrect regional format inference. Manual fixes work because they force a re-sync between the chart and its data source—we just need to automate that sync.

Solution 1: Force Data Refresh After Pasting #

Instead of relying on raw Select/Paste (which is prone to formatting loss), grab the pasted chart object and force it to re-read the source data. Update your macro like this:

' Copy the source chart
Dim sourceChart As ChartObject
Set sourceChart = Sheets(s + 4).ChartObjects("Gráfico 9")
sourceChart.Chart.ChartArea.Copy

' Paste and position the chart in the Print sheet
Sheets("Print").Select
Dim pastedChart As ChartObject
Set pastedChart = ActiveSheet.ChartObjects.Paste
' Align to your target cell
pastedChart.Top = ActiveSheet.Cells(l + 22, 3).Top
pastedChart.Left = ActiveSheet.Cells(l + 22, 3).Left

' Force the chart to refresh its data link
pastedChart.Chart.Refresh
' Optional: Re-bind to source data to ensure format consistency
' Replace "YourDataSourceRange" with the actual range for your chart
pastedChart.Chart.SetSourceData Source:=Sheets(s + 4).Range("YourDataSourceRange")

This avoids unstable Select calls and ensures the chart re-syncs with the correctly formatted source data.

Solution 2: Lock Number Format Before Copying #

If refreshing doesn’t work, explicitly set the number format for the Cmin series in the source chart before copying. This locks the format so it carries over during paste:

' Set the correct number format for Cmin data labels first
Sheets(s + 4).ChartObjects("Gráfico 9").Chart _
    .SeriesCollection("Cmin").DataLabels.NumberFormat = "0,00"

' Copy and paste as usual (using the improved object-based method)
Dim sourceChart As ChartObject
Set sourceChart = Sheets(s + 4).ChartObjects("Gráfico 9")
sourceChart.Chart.ChartArea.Copy

Sheets("Print").Select
Dim pastedChart As ChartObject
Set pastedChart = ActiveSheet.ChartObjects.Paste
pastedChart.Top = ActiveSheet.Cells(l + 22, 3).Top
pastedChart.Left = ActiveSheet.Cells(l + 22, 3).Left

Adjust the NumberFormat string ("0,00") to match your regional decimal separator and desired precision.

Solution 3: Copy the Entire ChartObject Instead of ChartArea #

Sometimes copying just the ChartArea strips out formatting metadata. Try copying the full chart control object directly:

Dim sourceChart As ChartObject
Set sourceChart = Sheets(s + 4).ChartObjects("Gráfico 9")

' Copy the entire chart object to the target cell
sourceChart.Copy
Sheets("Print").Paste Destination:=Sheets("Print").Cells(l + 22, 3)

' Refresh the pasted chart to confirm format
Sheets("Print").ChartObjects(Sheets("Print").ChartObjects.Count).Chart.Refresh

This method preserves more of the chart’s original properties, including formatting.

Quick Tips for Reliability #

• Ditch Select and Activate wherever possible—they’re slow and prone to errors. Using object variables (like pastedChart) makes your macro more stable.
• Double-check that your source data cells have the correct number format set (e.g., 0,00 for two decimal places with a comma separator).
• Test these macros on a small set of sheets first before running them on all 4800 to avoid unexpected issues.

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