Auto Select From Cyclic Expression

[#1]

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

LOOP

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

Cheers,

[stephencredmond]

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
 cp.Expressions.Item(0).Add()
 ' Copy from the first one
 cp.Expressions.Item(0).Item(iCount).CopyFrom(cp.Expressions.Item(0).Item(0))
 'TempExpr.CopyFrom(cp.Expressions.Item(0).Item(0))
 ' Loop Through and Copy from next one
 for i = 0 to iCount-1

 cp.Expressions.Item(0).Item(i).CopyFrom(cp.Expressions.Item(0).Item(i+1))

 Next
 ' Remove the last one
 cp.Expressions.Item(0).RemoveAt(iCount)

 ' 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:

cp.Expressions.Item(0).Item(i).Data.ExpressionVisual.Label.v

And then just swap with the one at postion 0.

Stephen

[#3]

Hi Stephen

That's great, I think I can work with that, will try something & let you know.

Cheers & have a great weekend.

[gerhard_jakubec]

Hi,

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
   next
   '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