VBA Code Corruption

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning everybody,

I am using object variables in an Access module to control automation in
Excel and Word. I noticed that periodically, code (specifically macro code in
Excel) that was working flawlessly in the past suddenly starts to error out.
For example, in making a line-graph, macro code will add data series to a
chart and assign values but suddenly it draws an error when it hits code to
name the data series. But if I move that line of code above the line in front
of it and rerun the procedure that line works fine but then it errors out on
the data series a couple lines down so I don't think the problem is the code.
This is some example code.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("TRAD FEES EMPLOYEE
STATS").Range( _
"B2"), PlotBy:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = _
"={""JAN"","" FEB"","" MAR"","" APR"","" MAY"","" JUN"","" JUL"",""
AUG"","" SEP"","" OCT"","" NOV"","" DEC""}"
ActiveChart.SeriesCollection(3).Name = "=""Riverside"""
ActiveChart.SeriesCollection(4).Values = _
"='TRAD EXPENSES_EMPLOYEE_STATS'!R3C39:R3C50"
ActiveChart.SeriesCollection(2).Name = "=""Corona"""
ActiveChart.SeriesCollection(1).Values = "='TRAD
EXPENSES_EMPLOYEE_STATS'!R3C2:R3C13"
ActiveChart.SeriesCollection(1).Name = "=""Anaheim Hills"""
ActiveChart.SeriesCollection(2).XValues = _
"={""JAN"","" FEB"","" MAR"","" APR"","" MAY"","" JUN"","" JUL"",""
AUG"","" SEP"","" OCT"","" NOV"","" DEC""}"
ActiveChart.SeriesCollection(4).Name = "=""Moreno Valley"""
ActiveChart.SeriesCollection(2).Values = _
"='TRAD EXPENSES_EMPLOYEE_STATS'!R3C51:R3C62"
ActiveChart.SeriesCollection(5).Name = "=""Yorba Linda"""

ActiveChart.SeriesCollection(3).Values = _
"='TRAD EXPENSES_EMPLOYEE_STATS'!R3C27:R3C38"

These data series were in sequential order mostly except when the errors
started occuring I moved some of them around to see if that effected the
error
so now they aren't in order. I currently get an error on line:

ActiveChart.SeriesCollection(4).Name = "=""Moreno Valley"""

but this error was occuring on this line before I moved this up a few lines:
ActiveChart.SeriesCollection(2).Name = "=""Corona"""


Why is this erroring out? I believe this is because of some sort of data
corruption, but I am not sure what is causing the data corruptions, and is
there any way to rebuild the flawed data or recompile so that I don't have to
rewrite the entire thing or record a macro all over again to rebuild? Thanks.

Cordially,
 
I'd suggest Rob Bovey's free code cleaner:
http://www.appspro.com/Utilities/CodeCleaner.htm

Excel can get confused, especially if its a big project that has had a lot
of code revisions. Think of this utility as a similar to Access' "compact &
repair" but applied to Excel code.

There may well be some other problem. However, I've found that doing a code
cleanup eliminates "goofy" behaviour a lot of the time.

Sometimes the behaviour remains but it at least you've eliminated the
possibility of code corruption and can direct your energies accordingly.

HTH & Good Luck,
 
Back
Top