Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Macro problem with naming convention

I need to reference a growing list of charts to hide or display various expressions within those charts.  Most of the expressions reference each other in some way, so I cannot proceed by simply changing the expression as needed.  Most of them need to exist for the other expressions on the chart to work properly.  I have tried repeatedly to make an array to simply hold the chart name (such as CH161) and then insert it in the proper spot, but I can't seem to get the naming convention correct and I always get an error of some sort.  Can someone show me how to do this properly so I can use a single list of chart names and only have to add the chart name to a list rather than add a whole new section of code for each chart each time I have to add a new chart?  I would appreciate it tremendously.

 

SUB ShowRecBuy
set chart = ActiveDocument.GetSheetObject("CH161")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH160")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH162")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH167")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH168")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH171")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH179")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH173")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH195")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
END SUB

SUB ShowPODz
set chart = ActiveDocument.GetSheetObject("CH161")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH160")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH162")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH167")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH168")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH171")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH179")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH173")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
chart.SetProperties p
set chart = ActiveDocument.GetSheetObject("CH195")
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(1).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(2).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(3).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
set expr = p.Expressions.Item(4).Item(0).Data.ExpressionVisual
expr.ColWidth = 170
chart.SetProperties p
set expr = p.Expressions.Item(5).Item(0).Data.ExpressionVisual
expr.ColWidth = 0
chart.SetProperties p
END SUB

Thanks in advance,

Scott Gammons

1 Solution

Accepted Solutions
marcus_sommer

Hi Scott,

you should use loops through arrays and perhaps additionally switches for the direction - see this example:

option explicit

sub ColumnWidth

dim arrObj(), arrColWidth()

dim i, ii

dim chart, p, expr

redim arrObj(1)

redim arrColWidth(5)

arrObj = array("CH161", "CH160")

if ActiveDocument.Variables("Direction").GetContent.String = "small" then

    arrColWidth = array(0, 0, 0, 170, 0, 0)

else

    arrColWidth = array(170, 170, 170, 0, 170, 170)

end if

for i = 0 to ubound(arrObj)

    set chart = ActiveDocument.GetSheetObject(arrObj(i))

    set p = chart.GetProperties

    for ii = 0 to ubound(arrColWidth)

      set expr = p.Expressions.Item(ii).Item(0).Data.ExpressionVisual

      expr.ColWidth = arrColWidth(ii)

    next

    chart.SetProperties p

next

end sub

- Marcus

View solution in original post

4 Replies
marcus_sommer

Hi Scott,

you should use loops through arrays and perhaps additionally switches for the direction - see this example:

option explicit

sub ColumnWidth

dim arrObj(), arrColWidth()

dim i, ii

dim chart, p, expr

redim arrObj(1)

redim arrColWidth(5)

arrObj = array("CH161", "CH160")

if ActiveDocument.Variables("Direction").GetContent.String = "small" then

    arrColWidth = array(0, 0, 0, 170, 0, 0)

else

    arrColWidth = array(170, 170, 170, 0, 170, 170)

end if

for i = 0 to ubound(arrObj)

    set chart = ActiveDocument.GetSheetObject(arrObj(i))

    set p = chart.GetProperties

    for ii = 0 to ubound(arrColWidth)

      set expr = p.Expressions.Item(ii).Item(0).Data.ExpressionVisual

      expr.ColWidth = arrColWidth(ii)

    next

    chart.SetProperties p

next

end sub

- Marcus

marcus_sommer

Perhaps this is also helpful:

http://community.qlik.com/message/353618#353618

- Marcus

Not applicable
Author

I am getting a type mismatch error on the line that follows.

arrObj = array("CH161", "CH160")

This is the type of error that I always seem to get when attempting to use an array.

Not applicable
Author

I altered your original format by changing the arrays of arrays to a more repetitive, but functional version as follows that adds each element of the array one at a time.  It's not as compact or elegant as your original code, but it does get around the type mismatch error.  Here it is in it's more or less final form.  Thanks for your invaluable help in getting me there.

 

sub ColumnWidth
dim arrObj(), arrColWidth()
dim i, ii
dim chart, p, expr
redim arrObj(18)
     arrObj(0)= "CH160"
     arrObj(1)= "CH161"
     arrObj(2)= "CH162"
     arrObj(3)= "CH167"
     arrObj(4)= "CH168"
     arrObj(5)= "CH171"
     arrObj(6)= "CH173"
     arrObj(7)= "CH179"
     arrObj(8)= "CH195"
     arrObj(9)= "CH196"
     arrObj(10)= "CH197"
     arrObj(11)= "CH198"
     arrObj(12)= "CH199"
     arrObj(13)= "CH203"
     arrObj(14)= "CH207"
     arrObj(15)= "CH215"
     arrObj(16)= "CH216"
     arrObj(17)= "CH217"
     arrObj(18)= "CH218"
redim arrColWidth(5)
if ActiveDocument.Variables("v_Direction").GetContent.String = "WOS" then
     arrColWidth(0) = 170
     arrColWidth(1) = 0
     arrColWidth(2) = 0
     arrColWidth(3) = 0
     arrColWidth(4) = 0
     arrColWidth(5) = 0
else if ActiveDocument.Variables("v_Direction").GetContent.String = "RNP" then
     arrColWidth(0) = 0
     arrColWidth(1) = 170
     arrColWidth(2) = 0
     arrColWidth(3) = 0
     arrColWidth(4) = 0
     arrColWidth(5) = 0
else if ActiveDocument.Variables("v_Direction").GetContent.String = "Demand" then
     arrColWidth(0) = 0
     arrColWidth(1) = 0
     arrColWidth(2) = 170
     arrColWidth(3) = 0
     arrColWidth(4) = 0
     arrColWidth(5) = 0
else if ActiveDocument.Variables("v_Direction").GetContent.String = "RecBuy" then
     arrColWidth(0) = 0
     arrColWidth(1) = 0
     arrColWidth(2) = 0
     arrColWidth(3) = 170
     arrColWidth(4) = 0
     arrColWidth(5) = 0
else if ActiveDocument.Variables("v_Direction").GetContent.String = "POs" then
     arrColWidth(0) = 0
     arrColWidth(1) = 0
     arrColWidth(2) = 0
     arrColWidth(3) = 0
     arrColWidth(4) = 170
     arrColWidth(5) = 0
else if ActiveDocument.Variables("v_Direction").GetContent.String = "UA" then
     arrColWidth(0) = 0
     arrColWidth(1) = 0
     arrColWidth(2) = 0
     arrColWidth(3) = 0
     arrColWidth(4) = 0
     arrColWidth(5) = 170
else if ActiveDocument.Variables("v_Direction").GetContent.String = "All" then
     arrColWidth(0) = 170
     arrColWidth(1) = 170
     arrColWidth(2) = 170
     arrColWidth(3) = 170
     arrColWidth(4) = 170
     arrColWidth(5) = 170
end if
end if
end if
end if
end if
end if
end if
for i = 0 to ubound(arrObj)
set chart = ActiveDocument.GetSheetObject(arrObj(i))
set p = chart.GetProperties
for ii = 0 to ubound(arrColWidth)
set expr = p.Expressions.Item(ii).Item(0).Data.ExpressionVisual
expr.ColWidth = arrColWidth(ii)
next
chart.SetProperties p
next
end sub