serious problem when mass changing chart series

[akos084]

I have a chat where I summarize the results from a series of files. The file names are dynamic, there is a need to constantly update the summary workbook. The idea was to use a code from peltiertiertech utility to do the job. when assigning values directly to the old and new strings I want to swap. It works. When using variables, It stops.

I am using the following code when changing chart series in all the charts on a sheet:

Sub UpdatePath()
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    
    ''' Do all charts in sheet
    Dim ChartObj As ChartObject
    Dim mySrs As Series
    Dim myChart As ChartObject


    
    For Each myChart In ActiveSheet.ChartObjects
        For Each mySrs In myChart.Chart.SeriesCollection
                
                'ChangeOneSeriesFormula mySrs, ThisWorkbook.Path & "\", ""
                
                ChangeOneSeriesFormula mySrs, "X iteration", "10 iteration"
                ChangeOneSeriesFormula mySrs, "X rings", "2500 rings"
                
                ChangeOneSeriesFormula mySrs, " A measurement", " 0 measurement"
                ChangeOneSeriesFormula mySrs, " B measurement", " 10 measurement"
                ChangeOneSeriesFormula mySrs, " C measurement", " 15 measurement"
                ChangeOneSeriesFormula mySrs, " D measurement", " 20 measurement"
                
                ChangeOneSeriesFormula mySrs, " A % data", " 0 % data"
                ChangeOneSeriesFormula mySrs, " B % data", " 5 % data"
                ChangeOneSeriesFormula mySrs, " C % data", " 10 % data"
        Next
    Next


Application.Calculate


Application.Calculation = xlCalculationAutomatic


Application.ScreenUpdating = True


End Sub


Sub UpdateTitles()


    ActiveSheet.Range("B1").Value = DataSetname
    
    For Each myChart In Sheets("Summary").ChartObjects
            
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, "X iteration", "10 iteration")
        
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " A error", " 0 error")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " B error", " 10 error")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " C error", " 15 error")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " D error", " 20 error")


        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " A % trim", " 0 % trim")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " B % trim", " 5 % trim")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, " C % trim", " 10 % trim")
    
    Next
    
Application.Calculate


Application.Calculation = xlCalculationAutomatic


Application.ScreenUpdating = True


ActiveWorkbook.SaveAs Path & "Summary.xlsm", FileFormat:=52


End Sub

This works. However if I include variables, it does strange things. excel restarts, fatal error, send error to Microsoft, etc...

Sub Test()


'pass variables to summary chart


Workbooks("Resampling w full iteration.xlsm").Sheets("Resampling parameters").Activate


    NumberOfIteration = Sheets("Resampling parameters").Range("NumberOfIteration")


    NumberOfRings = Sheets("Resampling parameters").Range("NumberOfRings")


    NumberOfErrors = Sheets("Resampling parameters").Cells(Rows.Count, 16).End(xlUp).Row - 3


    NumberOfTrims = Sheets("Resampling parameters").Cells(Rows.Count, 17).End(xlUp).Row - 3


    DataSetNumber = Sheets("Resampling parameters").Range("InputDataset")
    
    DataSetname = Sheets("Resampling parameters").Range("S" & 3 + DataSetNumber)


    ReDim ErrorVar(1 To NumberOfErrors, 1 To 2)


    ReDim TrimVaR(1 To NumberOfTrims, 1 To 2)


    For MeasurementError = 1 To NumberOfErrors


        ErrorVar(MeasurementError, 2) = Sheets("Resampling parameters").Cells(MeasurementError + 3, 16).Value


    Next MeasurementError


    For TrimValue = 1 To NumberOfTrims
    
        TrimVaR(TrimValue, 2) = Sheets("Resampling parameters").Cells(TrimValue + 3, 17).Value


    Next TrimValue


    ErrorVar(1, 1) = "A"
    ErrorVar(2, 1) = "B"
    ErrorVar(3, 1) = "C"
    ErrorVar(4, 1) = "D"


    TrimVaR(1, 1) = "A"
    TrimVaR(2, 1) = "B"
    TrimVaR(3, 1) = "C"

'call the two update procedures


UpdateSummaryLinks


UpdateSummaryTitles

The code for updating the Series:

Sub UpdateSummaryLinks()


    Dim ChartObj As ChartObject
    Dim mySrs As Series
    Dim myChart As ChartObject


Application.Workbooks.Open (ThisWorkbook.Path & "\Summary Bianco Sheet.xlsm")


ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Summary.xlsm", FileFormat:=52


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


    On Error GoTo FatalError:
    
    For Each myChart In ActiveSheet.ChartObjects
        For Each mySrs In myChart.Chart.SeriesCollection
                
            ChangeOneSeriesFormula mySrs, "X iteration", NumberOfIteration & " iteration"
            ChangeOneSeriesFormula mySrs, "X rings", NumberOfRings & " rings"
                
            ChangeOneSeriesFormula mySrs, ErrorVar(1, 1) & " measurement", ErrorVar(1, 2) * 100 & " measurement"
            ChangeOneSeriesFormula mySrs, ErrorVar(2, 1) & " measurement", ErrorVar(2, 2) * 100 & " measurement"
            ChangeOneSeriesFormula mySrs, ErrorVar(3, 1) & " measurement", ErrorVar(3, 2) * 100 & " measurement"
            ChangeOneSeriesFormula mySrs, ErrorVar(4, 1) & " measurement", ErrorVar(4, 2) * 100 & " measurement"
                
            ChangeOneSeriesFormula mySrs, TrimVaR(1, 1) & " % data", TrimVaR(1, 2) * 100 & " % data"
            ChangeOneSeriesFormula mySrs, TrimVaR(2, 1) & " % data", TrimVaR(2, 2) * 100 & " % data"
            ChangeOneSeriesFormula mySrs, TrimVaR(3, 1) & " % data", TrimVaR(3, 2) * 100 & " % data"
        
        Next
    Next


Application.Calculate


Application.Calculation = xlCalculationAutomatic


Application.ScreenUpdating = True


FatalError:


ActiveWorkbook.Save


ActiveWorkbook.Close


End Sub

The code for updating the chart titles:

Sub UpdateSummaryTitles()


    Dim ChartObj As ChartObject
    Dim mySrs As Series
    Dim myChart As ChartObject


Application.Workbooks.Open (ThisWorkbook.Path & "\Summary.xlsm")


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


    On Error GoTo FatalError:


    ActiveSheet.Range("B1").Value = DataSetname
    
    For Each myChart In Workbooks("Summary.xlsm").Sheets("Summary").ChartObjects
            
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, "X iteration", NumberOfIteration & " iteration")
        
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, ErrorVar(1, 1) & " error", ErrorVar(1, 2) * 100 & " error")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, ErrorVar(2, 1) & " error", ErrorVar(2, 2) * 100 & " error")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, ErrorVar(3, 1) & " error", ErrorVar(3, 2) * 100 & " error")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, ErrorVar(4, 1) & " error", ErrorVar(4, 2) * 100 & " error")


        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, TrimVaR(1, 1) & " % trim", TrimVaR(1, 2) * 100 & " % trim")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, TrimVaR(2, 1) & " % trim", TrimVaR(2, 2) * 100 & " % trim")
        myChart.Chart.ChartTitle.Text = WorksheetFunction.Substitute(myChart.Chart.ChartTitle.Text, TrimVaR(3, 1) & " % trim", TrimVaR(3, 2) * 100 & " % trim")
    
    Next
    
Application.Calculate


Application.Calculation = xlCalculationAutomatic


Application.ScreenUpdating = True


ActiveWorkbook.Save


ActiveWorkbook.Close


FatalError:
End Sub

 

[Jon Peltier]

1. Do you have "Option Explicit" as the first line of your module? If not, you should put it there, then in the VB editor, go to Tools > Options and check "Require Variable Declaration" (and whily you're at it, uncheck "Auto Syntax Check").

2. With that in mind, are NumberOfIteration and related variables supposed to be ranges or values? If values, use this:

NumberOfIteration = Sheets("Resampling parameters").Range("NumberOfIteration").Value

(just in case).

3. Do you know how far you get before Excel detonates?

4. This looks like a discrepancy, but I don't know why it was quite so destructive:

Your formulas that work look like:
ChangeOneSeriesFormula mySrs, " A measurement", " 0 measurement"

The formulas in your third block of code look like:
ChangeOneSeriesFormula mySrs, ErrorVar(1, 1) & " measurement", ErrorVar(1, 2) * 100 & " measurement"

Taking ErrorVar(1,1) = "A" and ErrorVal(1,2) = 0, as you've defined them, this changes from the successful formula to this:
ChangeOneSeriesFormula mySrs, "A measurement", "0 measurement"

Note the lack of a leading blank within each quoted string.

 

[akos084]

Thanks for the answer.

I have figured out the problem. It was simple, trivial, but took time. the path I was referring to was longer, than the max size a string can pass in a VBA code.

When I put the file in the in the C:/Temp, I worked without problem.

 

[akos084]

Other issue regarding this project.

I create and update a file with 144 graph on it. It takes values for several other files, and summarize the results.

the links are updated, as in previous post.

then i want to save the file and update the page to show the results with the following code:

.
.
.

Application.Calculation = xlCalculationAutomatic


ActiveWorkbook.Save


ActiveWorkbook.Close


Application.Workbooks.Open (Path & "Summary.xlsm"), UpdateLinks:=True


ActiveWindow.Zoom = 20


Application.ScreenUpdating = True

The code saves the file, but crashes at "ActiveWorkbook.Close"

Any ideas? Probably there is also a simpler way to update the linked data as well. I am open to any suggestion...

 

[Jon Peltier]

Sometimes VBA gets ahead of itself, and tries to do one step before the previous step has completed. Try one of these:

ActiveWorkbook.Save
DoEvents
ActiveWorkbook.Close

where DoEvents tells VBA to wait for the system to handle any events in its queue, or

ActiveWorkbook.Close Save:=True

which saves the workbook before closing it.