addDimension in Macro

[#1]

Hello Experts,

I have a question for macro.

My customer is developing a macro like the followings. 

sub sortingEquipTrendChart(equips)
chartX = 639
chartY = 128
orignX = 0
orignY = chartY
flg = 0
objs = ActiveDocument.ActiveSheet.GetSheetObjects
for i=lbound(objs) to ubound(objs)
  if objs(i).GetObjectType = 12 then  .
   dimen =  "=if(EQ_ID='" & equips(flg) & "',EQ_ID)"
   objs(i).RemoveDimension 0
   objs(i).RemoveDimension 0
   objs(i).AddDimension(dimen)   --> It doesn't work.
   objs(i).AddDimension("STS2_SEQ")  --> OK, It works fine.
   set pos = objs(i).GetRect
   if flg<>0 then
    orignY = orignY + (pos.Height - 1)
   end if
   pos.Top = orignY
   objs(i).SetRect pos
      set pos = nothing
      flg = flg + 1
  end if
next
end sub

But he want to know it is possible to use 'addDimension' with a parameter including expression like "=if(EQ_ID='" & equips(flg) & "',EQ_ID)".

Is it possible ?

Need your recommendation.

Thanks ,

WanKi,

[pljsoftware]

Hello Wanki,

try to put in your dimension a differente field name and test your macro, if your sub is correct the problem is the correct syntax in "=if(EQ_ID='" & equips(flg) & "',EQ_ID)".

I suggest you to verify the text for dimension and user for this a variable where you compone your dimension and a message box msgbox v_dimension to see output.

User the function CHR( number ASCII Character ) to concatenate char " and '

I hope this help you.

Regards

Luca Jonathan Panetta

[marcus_sommer]

Generally it is possible to create calculated dimensions per macro. In your case the syntax with the quotes looks not correct. Use outside from your expression double-quotes an inside single-quotes. The expression should look if you would write inside the chart and then wrapped it in double-quotes.

- Marcus

[#4]

Hi Luca Jonathan Panetta,

Thank you for your reply.

I tested to see v_dimension ( v_dimension = vTest = "=if(EQ_ID='" &  "TEST" & "',EQ_ID)"), it works fine.

But if I change the string like v_dimension = vTest = "=if(EQ_ID='" &  equips(flg) & "',EQ_ID)", it doesn't work.

Maybe the variable can not accept  to call a function in the string.

Thanks in advance,

WanKi,

[#5]

Hi Marcus,

I tried to test that you mentioned.

If I put the function call in the string, it doesn't work.

v_dimension = vTest = "=if(EQ_ID='" &  equips(flg) & "',EQ_ID)

Is it impossible to call a function in string to assign to variable ?

Thank you so much,

WanKi,

[#6]

Sorry about my mail.

The equips(flg) is not a function but a array.

So, it works fine.

Very sorry.

WanKi,

[#7]

Sorry about my mail.

The equips(flg) is not a function but a array.

So, it works fine.

Very sorry.

WanKi,

[pljsoftware]

Ok Wanky, but what is the solution?

Have you solved your problem?

Regards

Luca Jonathan Panetta

[#9]

LUca Jonathan  Panetta,

I didn't solve yet.

I am looking for a solution. 

But I donn't know the answer.

Thank you so much for your help.

Regards,

WAnKi,

[marcus_sommer]

You could include other variables or functions in this variable but the result should return:

"=YourExpression()"

This meant it's not enough that it is a string it needed the outside double-qoutes as string-part. Therefore you need something like this:

dimen =  """"=if(EQ_ID='" & equips(flg) & "',EQ_ID)""""

to create additionally quotes. Further with '" & equips(flg) & "' you would check the field EQ_ID against a string, correct?

- Marcus