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 functionality lost between QV 8.5 and 10

In the process of upgrading from 8.5 to 10. An existing macro (in 8.5) which rebuilds a pivot table by adding/removing dimensions and enabling/disabling expressions, based on two list boxes populated by an inline load, has worked fine. Now, in 10, the macro fails, resulting in one of two errors; 1) Allocated memory exceeded, and 2) the pivot table grayed out with the diagonal lines stretching from corner to corner. Upon checking the properties of the pivot table after the macro has run (activated by a button via Run Macro), the dimensions and expressions both exhibit the correct characteristics as modified by the macro, and then, without altering anything, upon selecting OK in the properties dialogue box, the pivot table proceeds to refresh and display correctly. The macro is included below.

Any ideas as to why this might be? Maybe i need to refresh the chart in a new way?

Thanks for your help.

sub rebuild()
set vAllowCalc = ActiveDocument.Variables("vAllowCalc")
vAllowCalc.SetContent 0, true 'Display the "Please wait..." text object
ActiveDocument.GetApplication.WaitForIdle 'wait for QlikView to finish before modifying report
set selectedExpressions = ActiveDocument.getField("Expr").getSelectedValues
set selectedDimensions = ActiveDocument.getField("DimField").getSelectedValues

CollapseCht

selectedExpressionMax = selectedExpressions.Count - 1
selectedDimensionMax = selectedDimensions.Count - 1
if selectedExpressionMax + selectedDimensionMax > -2 then 'bypass if nothing is selected
'--------------------------- TABLE BOX ONLY? ---------------------------------------------------
if selectedExpressionMax = -1 then
set table = ActiveDocument.getSheetObject("TB01")
tableDimensionMax = table.GetColumnCount - 1
for i = tableDimensionMax to 0 step -1 'remove existing dimensions
table.removeField(i)
next
for j = 0 to selectedDimensionMax 'add selected dimensions
table.addField(selectedDimensions.Item(j).Text)
next

else
'--------------------------- EXPRESSIONS ------------------------------------------------------
set chart = ActiveDocument.getSheetObject("CH642")
set chartProperties = chart.GetProperties
set chartExpressions = chartProperties.Expressions
chartExpressionMax = chartExpressions.Count - 1
dim selectedExpression(100) 'runs slightly faster if first set up a simple array
for j = 0 to selectedExpressionMax
selectedExpression(j) = selectedExpressions.Item(j).Text
next
for i = 0 to chartExpressionMax 'for every possible expression defined to the chart
chartExpression = chartExpressions.Item(i).Item(0).Data.ExpressionVisual.Label.v
expression = chartExpressions.Item(i).Item(0).Data.ExpressionData
expression.Enable = false 'disable the expression
for j = 0 to selectedExpressionMax 'check all selected expressions
if selectedExpression(j) = chartExpression then 'if user selected the expression
expression.Enable = true 'enable it
j = 9999 'and exit the inner loop
end if
next
next
ActiveDocument.GetApplication.WaitForIdle
chart.SetProperties chartProperties
'--------------------------- DIMENSIONS --------------------------------------------------------
set vDimCnt = ActiveDocument.Variables("vChtDimCnt")
vDimCnt2 = vDimCnt.GetContent.String
chartDimensionMax = vDimCnt2
for i = chartDimensionMax to 0 step -1 'remove existing dimensions
chart.removeDimension(i)
next
for j = 0 to selectedDimensionMax 'add selected dimensions
chart.addDimension(selectedDimensions.Item(j).Text)
next
end if
end if

ActiveDocument.Variables("vChtDimCnt").SetContent selectedDimensionMax,true
vAllowCalc.SetContent 1, true 'allow dynamic report to recalculate
ActiveDocument.Variables("vRefrPrompt").SetContent "0",true
end sub

6 Replies
Not applicable
Author

One last shot here.... Any ideas?

Thanks

Not applicable
Author

One last shot here... Any ideas?

Thanks

jp_golay
Partner - Creator II
Partner - Creator II

I have same problem the addfield get the tablebox graxed with a cross.

Have you found a solution

Thanks

ebiexperts CTO
With WIP, Control everything!
Qlik Sense, QlikView and NPrinting Source control, Versioning and Deployment, Agile Lifecycle Management
Not applicable
Author

I called the Expand/Collapse function we already had coded, I just called them again as a part of the other macro at the end...

Sub ExpandCht
set chart = ActiveDocument.getSheetObject("CH642")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = true
chart.SetProperties gp

End Sub

Sub CollapseCht
set chart = ActiveDocument.getSheetObject("CH642")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = false
chart.SetProperties gp

End Sub

Does that make sense...?

Not applicable
Author

Hi Rob,

Do you have resolution for this yet ? I have the same problem.

And adding the expand sub macro in the original macro still does not work.

Not applicable
Author

Beyond the Expand/Collapse macro calls, I have no other solutions. The macros fixed my problems so I stopped troubleshooting. Sorry I can't be of more help.