如何引用同名的图表，但是在不同的工作表上？ [英] How do I reference charts with the same name, but on different worksheets?

问题描述

我有两个包含图表的工作表，并使用一个宏来运行所有图表，并更新图表中的值。

然而，我碰到尝试在第一个工作表之后参考工作表中的图表时出现问题 - 虽然工作表的引用更改，但是对图表的引用不会。

循环看起来像这样：

 对于每个ws在ThisWorkbook.Worksheets 
 Debug.Print ws.Name 
调试。打印ws.ChartObjects（Kortsone）。Chart.Name 
带有ws.ChartObjects（Kortsone）。图
 ... 
结束
下一个ws 
  

我到达直接窗口的输出如下：

  Grafar ovn 3 
 Grafar ovn 3 Kortsone 
 Grafar ovn 4 
 Grafar ovn 3 Kortsone 
  

正如您可以看到工作表的引用更改，但图表引用不会。

我正在使用Excel 2013

- 编辑 - 我已经根据评论中的建议进行了一些测试，似乎打印到即时窗口的内容取决于当前活动工作表。

尝试为每个chartobject 使用遇到与我之前相同的问题：

  Sub test2（）
 Dim ws As Worksheet 
 Dim ch As ChartObject 
 
对于每个ws在ThisWorkbook.Worksheets 
对于每个ch在ws.ChartObjects 
如果ws.CodeName =Graf4然后
 Debug.Print ws.Name 
 Debug.Print ch.Name 
 Debug.Print ch.Chart.Name 
结束如果
下一个ch 
下一个ws 
 End Sub 
  

Gave：

  Grafar ovn 4 
 Kortsone 
 Grafar ovn 3 Kortsone 
 Grafar ovn 4 
 Langsone 
 Grafar ovn 3 Lan gsone 
 ... 
  




解决方案

ve发现， Workheet.ChartObjects 方法将找到正确的ChartObject，但访问 Chartobject.Chart 属性将返回ActiveSheet的图表。如果您按名称或索引号来引用ChartObject，这并不重要。

如果您使用 Worksheet，行为是一样的。形状方法来查找ChartObject。

此行为与早期版本的Excel不同。我确认该代码在Excel XP / 2002中工作，并且在2016年不工作。我不确定行为是否改变。这可能是2013年，或者可能是2013和2016的补丁？ Office for mac 2016中的行为是一样的（即不起作用）

在Microsoft提出修复之前，您必须激活该表，或激活ChartObject，之前，您访问图表属性。

 子测试（）
 Dim ws As Worksheet 
 Dim co As ChartObject 
对于每个ws在ThisWorkbook.Worksheets 
 Debug.Print ws.Name 
设置co = ws.ChartObjects（Kortsone）
 
 ws.Activate 
'或
 co.Activate 
 
 Debug.Print co.Chart.Name 
使用ws.ChartObjects（Kortsone ）.Chart 
 End with 
 Next ws 
 End Sub 
  

我建议您暂时禁用ScreenUpdating，并在完成后重新激活原始的活动表单。

I have two worksheets containing charts, and use a macro to run over all the sheets with charts in them and update the values charted.

However, I run into a problem when attempting to reference the charts in the sheets after the first one - although the reference for the worksheet changes, the reference to the chart does not.

The loop looks like this:

For Each ws In ThisWorkbook.Worksheets
  Debug.Print ws.Name
  Debug.Print ws.ChartObjects("Kortsone").Chart.Name
  With ws.ChartObjects("Kortsone").Chart
    ...
  End With
Next ws

And the output I get to the immediate window is the following:

Grafar ovn 3
Grafar ovn 3 Kortsone
Grafar ovn 4
Grafar ovn 3 Kortsone

As you can see the reference to the worksheet changes, but the chart reference does not.

Is there any way to fix this, or will I have to rename all my charts with unique names?

I'm using Excel 2013

- edit - I have now done some testing based on suggestions in the comments, and it seems that what is printed to the immediate window depends on what is currently the active sheet.

Trying to use for each chartobject ran into the same issues as I had earlier:

Sub test2()
  Dim ws As Worksheet
  Dim ch As ChartObject

  For Each ws In ThisWorkbook.Worksheets
    For Each ch In ws.ChartObjects
      If ws.CodeName = "Graf4" Then
      Debug.Print ws.Name
      Debug.Print ch.Name
      Debug.Print ch.Chart.Name
      End If
    Next ch
  Next ws
End Sub

Gave:

Grafar ovn 4
Kortsone
Grafar ovn 3 Kortsone
Grafar ovn 4
Langsone
Grafar ovn 3 Langsone
...

解决方案

As you've discovered, the Workheet.ChartObjects method will find the correct ChartObject, but accessing the Chartobject.Chart property will return the Chart of the ActiveSheet. It doesn't matter if you refer to the ChartObject by name or by Index number.

The behavior is the same if you use the Worksheet.Shapes method to find the ChartObject.

This behavior is different to earlier versions of Excel. I confirmed that the code worked in Excel XP/2002, and doesn't work in 2016. I'm unsure when the behavior changed. It might have been 2013, or it might have been a patch to 2013 and 2016? The behavior in Office for mac 2016 is the same (ie. doesn't work)

Until Microsoft comes up with a fix, you'll have to activate the sheet, or activate the ChartObject, before you access the Chart property.

Sub test()
  Dim ws As Worksheet
  Dim co As ChartObject
  For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
    Set co = ws.ChartObjects("Kortsone")

    ws.Activate
    'or
    co.Activate

    Debug.Print co.Chart.Name
    With ws.ChartObjects("Kortsone").Chart
    End With
  Next ws
End Sub

I suggest that you temporarily disable ScreenUpdating, and reactivate the originally activesheet once you're done.

这篇关于如何引用同名的图表，但是在不同的工作表上？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！