Macro for loop not working without a msgbox

[diffeyj]

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...

[Anonymous · Accepted solution]

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.

[diffeyj]

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...