topic Re: Macro problem with naming convention in QlikView

Macro problem with naming convention

Wed, 05 Jun 2013 15:58:07 GMT

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

Re: Macro problem with naming convention

marcus_sommer — Thu, 06 Jun 2013 09:32:55 GMT

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)

chart.SetProperties p

end sub

- Marcus

Re: Macro problem with naming convention

marcus_sommer — Thu, 06 Jun 2013 09:39:06 GMT

Perhaps this is also helpful:

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

- Marcus

Re: Macro problem with naming convention

Mon, 10 Jun 2013 19:05:38 GMT

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.

Re: Macro problem with naming convention

Mon, 10 Jun 2013 21:07:05 GMT

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