serious problem when mass changing chart series

A

akos084

New Member
Joined
Oct 8, 2011
Messages
10
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:

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
 
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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:

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...
 
Upvote 0
Sometimes VBA gets ahead of itself, and tries to do one step before the previous step has completed. Try one of these:

Code: 
ActiveWorkbook.Save
DoEvents
ActiveWorkbook.Close

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

Code: 
ActiveWorkbook.Close Save:=True

which saves the workbook before closing it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
409
dilshod_k
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top