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:
This works. However if I include variables, it does strange things. excel restarts, fatal error, send error to Microsoft, etc...
The code for updating the Series:
The code for updating the chart titles:
I am using the following code when changing chart series in all the charts on a sheet:
Code:
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...
Code:
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:
Code:
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:
Code:
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