CODEWITHSUNDEEP
vbaexcel
sgp667
sgp667

Reputation: 1875

Is there a Way to test if a Chart in Excel has its series stacked or not

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

Upvotes: 4

Views: 358

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

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

Upvotes: 1

Related Questions