有没有办法测试Excel中的图表是否堆叠在一起

问题描述

对我来说，Chars将 ChartType作为XlChartType 属性，但这是枚举值的长列表。有没有办法测试图表是否使用堆叠系列而不列出所有？

我正在尝试避免以下情况：

 选择ActiveChart.ChartType 
案例xlAreaStacked 
 .... 
案例xlBarStacked 
 .... 
案例xlColumnStacked 
 .... 
 ... 1000更多案例.... 
结束选择
  

解决方案

下面的一些示例代码与请求的枚举成员一起生成一个字典对象。

代码改编自dlmille的答案：

 子测试者（）
 
 Dim dict 
 
设置dict = GetEnumLookup（Excel，XlChartType）
如果不是dic t is Nothing然后
'从数字值获取字符串，看看它是否包含堆栈
 Debug.Print UCase（dict（XlChartType.xl3DAreaStacked））像* STACKED *
调试。打印UCase（dict（XlChartType.xl3DArea））像* STACKED *
 Else 
 MsgBoxEnum not recognized！ 
如果
 
 
 End Sub 
 
'VB项目引用必需：
'Microsoft Visual Basic应用程序可扩展性
' TypeLib信息
函数GetEnumLookup（LibName As String，sEnumName As String）As Object 
 
 Dim rv As Object 
 Dim tl As TLI.TypeLibInfo 
 Dim mi As TLI。 MemberInfo 
 Dim tiEnum As TLI.TypeInfo 
 
 Dim vbProj作为VBProject，oVBProjRef As参考
设置vbProj = ThisWorkbook.VBProject 
对于每个oVBProjRef在vbProj.References 
 
'Debug.Print oVBProjRef.Name，oVBProjRef.FullPath 
如果oVBProjRef.Name = LibName然后
 
设置tl =新建TypeLibInfo 
 tl.ContainingFile = oVBProjRef .FullPath 
 
在错误恢复下一步
设置tiEnum = tl.GetTypeInfo（sEnumName）
错误GoTo 0 
 
如果不是tiEnum是没有$ 
设置rv = CreateOb ject（scripting.dictionary）
对于每个mi在tiEnum.Members 
 rv.Add mi.Value，mi.Name 
'或如果要映射另一个方向... 
'rv.Add mi.Name，mi.Value 
下一步mi 
结束如果
 
退出
结束如果
 
下一步oVBProjRef 
 
设置GetEnumLookup = rv 
结束函数
  

To my understanding Chars have the ChartType as XlChartType property that but that is a long list of Enumerated Values. Is there a way to test if chart is using stacked Series without listing them all?

I am trying to avoid following scenario:

Select ActiveChart.ChartType
    Case xlAreaStacked
        ....
    Case xlBarStacked
        ....
    Case xlColumnStacked
        ....
   ... 1000 more Cases ....
End Select

解决方案

Some sample code below to produce a dictionary object with the members of the requested Enum.

Code adapted from dlmille's answer here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27613392.html

Sub tester()

    Dim dict

    Set dict = GetEnumLookup("Excel", "XlChartType")
    If Not dict Is Nothing Then
        'get string from numeric value and see if it contains "stacked"
        Debug.Print UCase( dict(XlChartType.xl3DAreaStacked) ) Like "*STACKED*"
        Debug.Print UCase( dict(XlChartType.xl3DArea) ) Like "*STACKED*"
    Else
        MsgBox "Enum not recognised!"
    End If


End Sub

'VB Project References required:
' Microsoft Visual Basic for Applications Extensibility
' TypeLib Information
Function GetEnumLookup(LibName As String, sEnumName As String) As Object

    Dim rv As Object
    Dim tl As TLI.TypeLibInfo
    Dim mi As TLI.MemberInfo
    Dim tiEnum As TLI.TypeInfo

    Dim vbProj As VBProject, oVBProjRef As Reference
    Set vbProj = ThisWorkbook.VBProject
    For Each oVBProjRef In vbProj.References

        'Debug.Print oVBProjRef.Name, oVBProjRef.FullPath
        If oVBProjRef.Name = LibName Then

            Set tl = New TypeLibInfo
            tl.ContainingFile = oVBProjRef.FullPath

            On Error Resume Next
            Set tiEnum = tl.GetTypeInfo(sEnumName)
            On Error GoTo 0

            If Not tiEnum Is Nothing Then
                Set rv = CreateObject("scripting.dictionary")
                For Each mi In tiEnum.Members
                     rv.Add mi.Value, mi.Name
                     'or if you want to map the other direction...
                     'rv.Add mi.Name, mi.Value
                Next mi
            End If

            Exit For
        End If

    Next oVBProjRef

    Set GetEnumLookup = rv
End Function

这篇关于有没有办法测试Excel中的图表是否堆叠在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！