Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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