3 Replies Latest reply: Jan 22, 2015 10:45 AM by Gerhard Jakubec RSS

    Auto Select From Cyclic Expression

      Hi Folks

      I have a macro that allows me to select whatever dimension from a cyclic dimension for a chart and this works really well, I am now looking at doing the same for a cyclic expression but I'm not really sure where to start.

      My Macro for the dimension is:

      SET grp1 = ActiveDocument.GetGroup("StdListA")

      WHILE grp1.GetActiveField.Name <> "DealAggName" DO

      grp1.Cycle 1


      If anybody has any clues as to how I can do this I'd appreciate it.


        • Auto Select From Cyclic Expression
          Stephen Redmond

          Hi Nigel,

          This may help - code to cycle through the expressions for a chart


          Sub CycleExpression

          set chart = ActiveDocument.GetSheetObject("CH01")
          set cp = chart.GetProperties

          ' Get a count of the current expressions
          iCount = cp.Expressions.Item(0).Count

          ' N.B. Item(0) in this case is the first Expression group - there may be more than one

          ' Add a new Expression
          ' Copy from the first one
          ' Loop Through and Copy from next one
          for i = 0 to iCount-1


          ' Remove the last one

          ' save back to the chart
          chart.SetProperties cp

          End Sub

          You might want to do something like loop through the expressions and query the name using:



          And then just swap with the one at postion 0.





          • Re: Auto Select From Cyclic Expression
            Gerhard Jakubec


            i implemented Nigels approach to set a cycle group by macro code. But his method steps thru all the cycle group elements causing all sheet objects to render over and over again until the right item is finally reached. This is not desirable so after some hefty elaboration with different API features i finally got a working solution. The macro needs two variables to be set. vMacroParam1 expects name of cycle group as string, vMacroParam2 expects name of group member to be selected. I have created a button with 3 Actions (set vMacroParam1 , set vMacroParam2 , trigger SetDimGroupSel) which works as expected.


            Sub SetDimGroupSel
            ' fetch name of group to be selected ------------------
            Dim strGrp
            strGrp = ActiveDocument.Variables("vMacroParam1").GetContent().String

            ' set instance pointers
            set grp = ActiveDocument.GetGroup(strGrp) 'returns pointer to Group instance
            set grpProps = grp.GetProperties ' returns pointer to IGroupProperties instance
            set fldDefs = grpProps.FieldDefs ' returns pointer to IArrayOfFieldDevEx instance
            'set fldItem0 = fldDefs.Item(0) ' returns pointer to IFieldDevEx instance
            'msgbox("Item0.Name = " & fldItem0.Name)

            ' fetch name of currently selected field
            Dim strActiveField
            strActiveField = grp.GetActiveField.Name

            ' fetch name of field to be selecetd ------------------
            Dim strNewField
            strNewField = ActiveDocument.Variables("vMacroParam2").GetContent().String

            'msgbox("strActiveField = "&strActiveField)
            'msgbox("strNewField = "&strNewField)

            ' variable item name for loop
            Dim fldName
            'msgbox("fldName = " & fldName)

            ' number of field items
            Dim fldCount
            fldCount = fldDefs.Count
            'msgbox(fldCount&" field items in group")

            If (true = grpProps.IsCyclic) then
              'msgbox("IsCyclic = "&grpProps.IsCyclic)
              if (true = grpProps.Present) then
               'msgbox("Present = "&grpProps.Present)
               'index pointers
               Dim ixCurrent, ixNew, cycleSteps
               'loop throug all items of group to find the index positions of strActiveField and strNewField
               for i=0 to fldCount-1 Step 1
                'msgbox("fetch name of item("& i &") for comparison")
                fldName = fldDefs.Item(i).Name
                'msgbox("fldName = " & fldName)
                if (fldName = strActiveField) then
                 ixCurrent = i
                end if
                if (fldName = strNewField) then
                 ixNew = i
                end if
               'msgbox("ixCurrent = " & ixCurrent & " ixNew =" & ixNew )
               'derive number of cycle steps
               cycleSteps = fldCount + (ixNew - ixCurrent)
               'msgbox("cycleSteps = " & cycleSteps)
               'cycle derived number of steps
               grp.Cycle cycleSteps
              end if
            end if

            'msgbox("ActiveField = "&grp.GetActiveField.Name)
            End Sub