Syntax Question in Macro

[Anonymous]

I have a macro that hides or shows Expressions in a chart depending on what options a user selects from an InputBox. It works fine but I need to know if it's possible to reference the expressions by name rather than by "Item(3)". My users may change the order of the Expressions in the chart and I need to make sure the proper Expressions are acted upon regardless of their order.

.

sub CasesorPounds

vSelection = ActiveDocument.Variables("vCasesPounds").GetContent.String

set vChart1 = ActiveDocument.GetSheetObject("CH04")
set vCP1 = vChart1.GetProperties
set vCP1Expression3 = vCP1.Expressions.Item(3).Item(0).Data.ExpressionData
set vCP1Expression4 = vCP1.Expressions.Item(4).Item(0).Data.ExpressionData
set vCP1Expression5 = vCP1.Expressions.Item(5).Item(0).Data.ExpressionData
set vCP1Expression6 = vCP1.Expressions.Item(6).Item(0).Data.ExpressionData
set vCP1Expression7 = vCP1.Expressions.Item(7).Item(0).Data.ExpressionData
set vCP1Expression8 = vCP1.Expressions.Item(8).Item(0).Data.ExpressionData

select case vSelection
 case "Cases"
 vCP1Expression3.Enable=true
 vCP1Expression4.Enable=true
 vCP1Expression5.Enable=false
 vCP1Expression6.Enable=false
 vCP1Expression7.Enable=true
 vCP1Expression8.Enable=false
 case "Pounds"
 vCP1Expression3.Enable=false
 vCP1Expression4.Enable=false
 vCP1Expression5.Enable=true
 vCP1Expression6.Enable=true
 vCP1Expression7.Enable=false
 vCP1Expression8.Enable=true
 case "Both"
 vCP1Expression3.Enable=true
 vCP1Expression4.Enable=true
 vCP1Expression5.Enable=true
 vCP1Expression6.Enable=true
 vCP1Expression7.Enable=true
 vCP1Expression8.Enable=true
end select

vChart1.SetProperties vCP1

end sub

[#2]

I don't believe there is a way to reference expressions by name. I agree that referencing them by order is severly limited. I've pretty much given up on expression based macros like this because of it.

[#3]

Consider referring to the variable inside your expression to make the value different depending on your unit-of-measure variable.

For example, something like: if(vCasesPounds='Pounds' ,qty * wt_per_case, qty)

You might want to also reference the variable in an expression for the column title so the that the title says "Quantity in Pounds" or "Quantity in Cases" depending on the value of your variable.

[Anonymous]

Thanks Tim. I would've done it that way except that the users may want to see both Pounds and Cases at the same time. So hiding/showing expressions was the way I went.

[#5]

You may already think about this (a bit crude and may affect performance). What if you create a procedure which loops thru the expression and disabling or enabling it as necessary. Something like

public sub EnableDisableExpression(pExpression, pFlag)
set vChart1 = ActiveDocument.GetSheetObject("CH01")
set vCP1 = vChart1.GetProperties
iNumExpr = vCP1.Expressions.count

for iCtr = 0 to iNumExpr - 1
if vCP1.Expressions.Item(iCtr).Item(0).Data.ExpressionData.Definition.v = pExpression then
vCP1.Expressions.Item(iCtr).Item(0).Data.ExpressionData.Enable = pFlag
exit for
end if
next
vChart1.SetProperties vCP1
end sub

Then on your if then statement you call the routing like

if Cases then

EnableDisableExpression "=sum(field1)", "false"

EnableDisableExpression "=sum(field2)", "true"

end if

if Pounds then

EnableDisableExpression "=sum(field1)", "true"

EnableDisableExpression "=sum(field1)", "false"

end if

i hope this helps.