3 Replies Latest reply: Feb 13, 2013 12:16 AM by Dmitry Lapchenkov

change calculated dimension by macro

Hi, people!

I have macro with some code that build my expression. This is part of code:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

formula_n = ""

formula_n_kon = ""

for M = 0 to fv.Count - 1

my_f1 = "if(column(4)+0.00001  >= "

my_f2 = "n_3"&M

my_f3 = " and mes = "

my_f4 = "mes_"&M

my_f5 = ", '   3', if(column(4)+0.00001  >= "

my_f6 = "n_2"&M

my_f7 = " and column(4)+0.00001  < "

my_f8 = "n_3"&M

my_f9 = " and mes = "

my_f10 = "mes_"&M

my_f11 = ", '  2', if(column(4)+0.00001 < "

my_f12 = "n_2"&M

my_f13 = " and mes = "

my_f14 = "mes_"&M

my_f15 = ", ' 1' ,"

my_f16 = " )))"

my_f = my_f1+my_f2+my_f3+my_f4+my_f5+my_f6+my_f7+my_f8+my_f9+my_f10+my_f11+my_f12+my_f13+my_f14+my_f15

formula_n = formula_n + my_f

formula_n_kon = formula_n_kon + my_f16

next

formula_n = formula_n + formula_n_kon

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

after this part of code my expression contained in formula_n. is looks like:

formula_n =

if(column(2)+0.01>= vA0 and mes = mes_0, 'A', if(column(2)+0.01>= vB0 and column(2)+0.01 < vA0 and mes = mes_0, 'B', if(column(2)+0.01>= vC0 and column(2)+0.01 < vB0 and mes = mes_0, 'C', if(column(2)+0.01< vC0 and mes = mes_0, 'D' ,if(column(2)+0.01>= vA1 and mes = mes_1, 'A', if(column(2)+0.01>= vB1 and column(2)+0.01 < vA1 and mes = mes_1, 'B', if(column(2)+0.01>= vC1 and column(2)+0.01 < vB1 and mes = mes_1, 'C', if(column(2)+0.01< vC1 and mes = mes_1, 'D' , )))) ))))

Now i need to put this expression to chart DIMENSION.  I know how to put it into expression, but how to do it whis dimension i don't understand.

• Re: change calculated dimension by macro

SUB Test

set chart = ActiveDocument.GetSheetObject("CH01")

chart.AddDimension "if(column(2)+0.01>= vA0 and mes = mes_0, 'A', if(column(2)+0.01>= vB0 and column(2)+0.01 < vA0 and mes = mes_0, 'B', if(column(2)+0.01>= vC0 and column(2)+0.01 < vB0 and mes = mes_0, 'C', if(column(2)+0.01< vC0 and mes = mes_0, 'D' ,if(column(2)+0.01>= vA1 and mes = mes_1, 'A', if(column(2)+0.01>= vB1 and column(2)+0.01 < vA1 and mes = mes_1, 'B', if(column(2)+0.01>= vC1 and column(2)+0.01 < vB1 and mes = mes_1, 'C', if(column(2)+0.01< vC1 and mes = mes_1, 'D' , )))) ))))"

END SUB

?

• Re: change calculated dimension by macro

hi! thank you for help! yes this way is working. but my problem that i fill  formula_n  by cycle. for example if i shoose 1 month formula_n will be:

"if(column(2)+0.01>= vA0 and mes = mes_0, 'A', if(column(2)+0.01>= vB0 and column(2)+0.01 < vA0 and mes = mes_0, 'B', if(column(2)+0.01>= vC0 and column(2)+0.01 < vB0 and mes = mes_0, 'C', if(column(2)+0.01< vC0 and mes = mes_0, 'D' , ))))"

each month add to this formula_n more component:

vA0 for 1 month

vB0 for 1 month

vC0 for 1 month

vA1 for 2 month

vB1 for 2 month

vC1 for 2 month

vA2 for 3 month

vB2 for 3 month

vC2 for 3 month

......................

• Re: change calculated dimension by macro

Ok. i solve this problem with using variable.