Macro for loop not working without a msgbox

diffeyj — Tue, 28 Jun 2011 01:52:14 GMT

Hi All

I have a function which appears to be all ok, except for one little hitch. As soon as I take out the msgbox "test" the for loop and subsequent if statement don't work. The msgbox is the bit of code in red (it doesn't matter if I put it at the start of the for loop or at the end, same dealio).

The function is there to find a chart (input variable chartName), remove the dimensions, then add upto 2 new dimensions (input variables), and then if it is meant to show periods (months) then it will include a 3rd dimension called periodDesc.

So if I have the msgbox, then this is what it looks like once the macro has completed (I have changed the company name for confidentiality reasons)

If I remove the msgbox, then this is what it looks like once the macro has completed

here is the code

function setDimensions2(chartName, newChartName, dimensionName1, dimensionName1Title, dimensionName2, dimensionName2Title, hasPeriod)
     set chart                           = ActiveDocument.getSheetObject(chartName)
     set chartDimensions                 = chart.GetProperties.Dimensions
     chartDimensionMax                     = chartDimensions.Count - 1
     
     ' Remove the dimensions
     for i = chartDimensionMax to 0 step -1
          chart.removeDimension i
     next
     
     if dimensionName1 <> "" then
          chart.addDimension dimensionName1
     end if
     if dimensionName2 <> "" then
          chart.addDimension dimensionName2
     end if
     if hasPeriod then
          chart.addDimension "periodDesc"
     end if
     
     set chartProperties  = chart.GetProperties
     set chartDimensions2 = chartProperties.Dimensions
     chartDimensionMax = chartDimensions2.Count - 1
     
     for i = 0 to chartDimensionMax
          msgbox "test"
          dimName = chartDimensions2(i).PseudoDef.Name
          if dimName = "periodDesc" then
               chartDimensions2(i).NullSuppression = true
               chartDimensions2(i).ShowPartialSums = true
               chartDimensions2(i).Title.v = "Period"
               chartDimensions2(i).TextAdjust = 1
               chartDimensions2(i).SortCriteria.SortByAscii=0
               chartDimensions2(i).SortCriteria.SortByExpression=0
               chartDimensions2(i).SortCriteria.SortByFrequency=0
               chartDimensions2(i).SortCriteria.SortByLoadOrder=1
               chartDimensions2(i).SortCriteria.SortByState=0
               chartProperties.TableProperties.NumberOfLeftDimensions = chartDimensionMax
          elseif dimName = dimensionName1 then
               chartDimensions2(i).NullSuppression = true
               chartDimensions2(i).ShowPartialSums = true
               if dimensionName1Title <> "" then
                    chartDimensions2(i).Title.v = dimensionName1Title
               end if
               chartDimensions2(i).LabelAdjust = 0
               chartDimensions2(i).TextAdjust = 0
               chartDimensions2(i).NumAdjust = 0
          elseif dimName = dimensionName2 then
               chartDimensions2(i).NullSuppression = true
               if dimensionName2Title <> "" then
                    chartDimensions2(i).Title.v = dimensionName2Title
               end if
               chartDimensions2(i).LabelAdjust = 0
               chartDimensions2(i).TextAdjust = 0
               chartDimensions2(i).NumAdjust = 0
          end if
     next
     if newChartName <> "" then
          chartProperties.GraphLayout.WindowTitle.v = newChartName
     end if
     chart.SetProperties chartProperties
end function

is there any reason why the for loop doesn't work, and why the chart's window title does not change. It seems quite odd...

Macro for loop not working without a msgbox

Anonymous — Tue, 28 Jun 2011 02:00:45 GMT

Hm, without reading through your macro completely, I could imagine it's a timing issue where the charts are re-calculating based on selection changes, while the macro pushes forward with new selections etc.

I would try adding ActiveDocument.GetApplication.WaitForIdle in some strategic places to see if that makes it work alright.

Macro for loop not working without a msgbox

diffeyj — Tue, 28 Jun 2011 02:04:53 GMT

thanx for the help, I just put that one liner in the same spot I have been putting the msgbox, and it works a treat...

topic Macro for loop not working without a msgbox in QlikView

Macro for loop not working without a msgbox

Macro for loop not working without a msgbox

Macro for loop not working without a msgbox