13 Replies Latest reply: May 2, 2015 12:34 AM by Robert Mika RSS

    related to macro for a combo chart

    yusuuf rawat

      I successfully created a combo chart using a macro

      both expressions are bars , how do i set one expression as a Bar and the other as a line in the same chart.

       

      Below is the code used

      Sub GenerateComboChart()

       

       

      Dim myChart

      ' Create a new Bar Chart

      Set myChart = _

      ActiveDocument.ActiveSheet().CreateComboChart

      ' Add a dimension of Country to the new chart

      myChart.AddDimension "MonthYear"

      myChart.AddDimension "Alias"

       

       

       

       

      myChart.AddExpression "=Avg([Saturation Index (%)])"

      myChart.AddExpression "=Max([Saturation Index (%)])"

      ' Get the properties object

      Dim cp

      Set cp = myChart.GetProperties()

      ' Set the title of the dimension

      cp.Dimensions(0).Title.v = "Alias"

      ' Set the Title-in-chart text

      cp.ChartProperties.Title.Title.v = sitearray(0)

      ' Set the Window title

      cp.GraphLayout.WindowTitle.v =  sitearray(0)

      ' Set sort by Y-Value

      cp.SortByYValue = -1

      ' Get the expression properties

      Dim expr, exprvis

      Set expr = _

      cp.Expressions.Item(0).Item(0).Data.ExpressionData

      Set exprvis = _

      cp.Expressions.Item(0).Item(0).Data.ExpressionVisual

      ' Set the Expression label

      exprvis.Label.v = "Sales $"

      ' Set the "Values on Data Point" option

      exprvis.NumbersOnBars = -1

      ' Set the number format for the expression

      exprvis.NumberPresentation.Dec = "."

      exprvis.NumberPresentation.Fmt = "#,##0.00"

      exprvis.NumberPresentation.nDec = 2

      exprvis.NumberPresentation.Thou = ","

      exprvis.NumberPresentation.Type = 11 'fixed

      exprvis.NumberPresentation.UseThou = 1

      ' Apply the modified properties

      myChart.SetProperties cp

      End Sub