有没有一种方法可以从Excel VBA中的轴导出图表? [英] Is there a way to derive the chart from an axis in Excel VBA?

问题描述

我有许多通用的VBA宏，用于处理Excel图表(例如，将一个图表叠加到另一个图表上；重新缩放轴；或通过以下方式向图表添加自定义曲线，例如"y = x ^ 2-1-1"在文本框中输入公式).这些宏是不带任何参数的子，我将它们存储在.xlam加载项中，并通过Excel功能区上的链接按钮运行它们.为了在特定图表上运行宏，请选择图表，然后单击功能区上的按钮.

I have a number of generic VBA macros for manipulating Excel charts (e.g. overlaying one chart on top of the other; rescaling the axes; or adding a custom curve such as "y = x^2 - 1" to a chart by typing the formula in a text box). These macros are subs which don't take any argument, I store them in a .xlam add-in and run them from linked buttons on the Excel ribbon. In order to run the macro on a specific chart, you select the chart and then click the button on the ribbon.

为了让宏知道它们在哪个图表上运行，我有一个像这样的函数:

In order for the macros to know which chart they are operating on, I have a function like this:

Function chart_from_selection() As Chart

  If TypeName(Selection) = "ChartArea" Or TypeName(Selection) = "PlotArea" Then
    Set chart_from_selection = Selection.Parent
  ElseIf TypeName(Selection) = "Series" Then
    Set chart_from_selection = Selection.Parent.Parent
  Else
    MsgBox ("Select a chart!")
  End If

End Function

所以每个宏的前几行是

Dim cht As Chart
Set cht = chart_from_selection()

并且宏可以识别图表，无论您选择了其图表区域，绘图区域还是其系列之一.

and the macro identifies the chart whether you have selected its chart area, plot area or one of its series.

如果您选择了图表轴之一，我也希望它可以工作，但是问题是轴对象的父对象是工作表而不是图表.有谁知道如何从其中一个轴导出图表对象本身?我能想到的唯一方法是记录轴的位置，然后将其与工作表中所有图表的位置进行比较，直到找到并重叠为止，但这似乎很令人费解，我想知道我是否忽略了更简单的方法...

I would also like it to work if you have selected one of the chart axes, but the problem is that the parent of the axis object is the worksheet not the chart. Does anyone know how to derive the chart object itself from one of its axes? The only way I can think of is by recording the position of the axis and then comparing it against the positions of all the charts in the worksheet until you find and overlap, but that seems quite convoluted and I'm wondering if I'm overlooking a simpler way ...

推荐答案

好，所以我想为您提供解决方案:

Ok, so I think I may have a solution for you:

Sub Find_Chart()

Dim C As ChartObject
Dim sAx As Axis
Dim Axs As Object

'Check if selection is axis
If TypeOf Selection Is Axis Then
    Set sAx = Selection
End If

'Loop through charts
For Each C In ActiveSheet.ChartObjects
    'Loop through axes
    For Each Axs In C.Chart.Axes
        If Axs.AxisTitle.Caption = sAx.AxisTitle.Caption Then
            Debug.Print C.Name
        End If
    Next Axs
Next C

End Sub

为使以上代码正常工作，您的图表轴必须全部带有标题...如果图表没有标题(并且您希望保持这种方式)，则可以添加标题并将字体更改为白色以保持您的图表干净整洁.每个标题也必须是唯一的.设计一个ID系统以确保所有标题都是唯一的(例如Chart1AxV，Chart1AxH，Chart2AxV等).如果您已有标题，并且其中一些标题重复，则可以在标题末尾添加一个唯一的ID，并将标签的ID部分格式化为白色.

For the above code to work, your chart axes must all have titles... If your charts don't have titles (and you'd prefer to keep it that way), you could add titles and change the font to white to keep your charts looking clean. Each title must also be unique. Devise an ID system to ensure all titles are unique (e.g. Chart1AxV, Chart1AxH, Chart2AxV, etc.). If you have preexisting titles and some are duplicated, you can add a unique ID to the end of the title and format the ID part of the label to be white.

以上代码循环遍历工作表中的每个图表，并检查图表中的每个轴.如果轴标题与所选轴的标题相同，则图表名称将显示在立即窗口中.

The above code loops through each chart in your sheet and checks each axis in the chart. If the axis title is the same as the selected axis's title, the name of the chart is printed to the immediate window.

希望这对您有所帮助！

这篇关于有没有一种方法可以从Excel VBA中的轴导出图表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！