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

    change calculated dimension by macro

    Dmitry Lapchenkov

      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.

       

      Have any ideas how it is posseble to do?  Please help

        • Re: change calculated dimension by macro
          Wojciech Parzyszek

          did You try with chart.AddDimension

           

          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
              Dmitry Lapchenkov

              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
              Dmitry Lapchenkov

              Ok. i solve this problem with using variable.