Some of this stuff, while I can see Jon's point about not having to fuss
about using Active objects for small scale stuff, some of this just comes
from experience and this Active stuff was one of the first things that bit
me hard in VBA coding, which meant I had to go back and redo enough things
with the Active object codes. On the other hand, there are most definitely
exceptions to this rule.
This Active stuff was originally created by the macro recorder, which is
also to make a point, while the macro recorder does help in the learning
process, you must be able to go back and make the necessary adjustments as
needed to be able to avoid issues down the road.
In some sense, this is also true about prequalifications of objects and
variables. After many of the issues that I ran into in Access coding, I now
by default in most cases prequalify my objects/variables. I know in the
case of Excel, there may not be many places where such issues may come into
play, but I could definitely see it coming into play when dealing with Chart
coding, given the various Left, Top, Width, and Height properties.
Main thing though to be careful about, while something may start out small,
but as you get to building it up, it no longer is small, and that's when you
may be bound to run into some of these issues more often. The larger these
projects gets, the more time it takes to convert them, and that's basically
how I ended up dealing with this issue in my Production Reporting System,
which started out with a very small amount of VBA coding, but as things
progressed, that VBA code got bigger and bigger. I now by default look for
these things, that deals with good programming practices, even with the fact
that I'm a lone developer, not working on a team of developers. However,
even though others may not be looking at my code, I still need to have it
setup for readability purposes while trying to keep the code as efficient as
possible so as when I go back to the code, I only spend minimal time
reviewing to see what its purpose is and what it's doing. There's a lot of
different rules and guidelines I use as I develop stuff.
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
"Jon Peltier" wrote in message
...
John -
Recorded macros aren't always perfect. One issue that often occurs is that
the recorded macro shows the chart type applied before the data, and in
general if you don't get the chart type you intended, you should apply it
after the chart has data. This minor variation on your procedure works
fine (note the new position of the ApplyCustomType statement):
Sub DoChart()
Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
With Selection.Interior
.ColorIndex = 36
.Pattern = 1
End With
End Sub
I almost never use these built-in custom types, because if you add a
series, it may not be the type you expected, and it may change the types
of existing series. I apply one regular type to the entire chart, then
apply a different type to the particular series, and if necessary change
the axes.
Sub DoChart()
Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
With Selection.Interior
.ColorIndex = 36
.Pattern = 1
End With
End Sub
In general Ronald is correct about using ActiveChart or any other selected
object. This macro is pretty simple, so it's probably not worth fussing
with. However, the way to avoid dealing with an active chart is as
follows. For a more detailed discussion, see this page:
http://peltiertech.com/Excel/ChartsH...kChartVBA.html
Sub DoChart()
Dim wksht As Worksheet
Dim cht As Chart
Dim width As Double
Dim height As Double
width = ActiveWindow.UsableWidth
height = ActiveWindow.UsableHeight
Set wksht = ActiveSheet
Set cht = wksht.ChartObjects.Add(width / 4, height / 4, width / 2, height
/ 2).Chart
With cht
.SetSourceData _
Source:=wksht.Range("B37:D61"), PlotBy:=xlColumns
.ChartType = xlColumnClustered
With .SeriesCollection(2)
.ChartType = xlLineMarkers
.AxisGroup = xlSecondary
End With
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
.HasLegend = True
With .Legend
.Position = xlBottom
With .Interior
.ColorIndex = 36
.Pattern = 1
End With
End With
End With
End Sub
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Ronald Dodge" wrote in message
...
The first thing that I would do is avoid using Active<object in general.
There will be exceptions to this rule, but it should be rare in nature as
you can run into a lot of issues otherwise to be using these active
objects.
Now onto chart coding. This is one area all to itself, even within VBA.
Charts are easy to learn on the spreadsheet side, but much more complex
on the VBA side as it's spread out a lot more so than you would typically
think. I actually used the combination of the recorder and the watches
to help me learn the charting code a lot more easily, but even then, it's
still has some oddities to it. For instance, you want to line one chart
directly over the top of another chart, and you want to setup your own
code for being able to adjust the scales as the code may need to.
Thirdly, you want to have a label to the right of the charts but still
within the chart area and on top. This label is to move with the goal
line as the scales are adjusted. Well one oddity I found, setting the
left, top, width and height of the 2 charts don't necessarily place one
directly over the top of the other even though you would think that would
have to happen. This can be off by just enough to be visible when this
is printed. This is what I had to face in regards to my production
charts and get them to look just how people within the company wanted
them to look for easy to read purposes. Top chart being a line chart
while the bottom chart being an area chart. On the area chart, goal line
had to stretch from the left edge to the right edge of the plot area with
it being one color above it and another color below it. On the line
chart, it had to track performances with the marks in the middle of the
columns.
Bare in mind, you not only have the chart area, but you also have the
plot area too as you will notice in the code below. Given the various
oddities and different rules that must be followed, I'm still not done
setting up my own codes for the production charts, but I have made some
headway with it. First, setup the charts by hand, and then use code to
make adjustments to those charts along the way on an as needed basis.
This means it would also be wise to name those charts for readability
purposes. In this example, I used this code to be able to line up the
area chart with the line chart, which was easier said than done as
WYSIWYG doesn't apply too easily in the case of working with charts
overlaid on top of each other. I ended up using larger border weight
around the outside of the plot area on the line chart to hide this fact
when the charts prints out.
--------BEGIN---CODE--------------
Sub ChartMaintenance()
Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long,
lngFCHTCOL As Long
Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long,
lngLCHTCOL As Long
Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double,
dblCHTWID As Double
Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As
ChartObject
strSFN = "0271321_Production-2.xls"
lngFCHTCOL = 2
lngLCHTCOL = 11
lngFROWSET = 41
lngLROWSET = 55
lngFROWRUN = 58
lngLROWRUN = 72
lngFROWPRD = 75
lngLROWPRD = 89
With Workbooks(strSFN).Worksheets("Charts")
'With .ChartObject("chtSetupLine")
' .Left = 54
'End With
Set chtOBJ = .ChartObjects("chtRunArea")
With chtOBJ
Set rngTOPL =
Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL)
Set rngBTMR =
Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL)
.Top = rngTOPL.Top
.Height = rngBTMR.Top - .Top + rngBTMR.Height
.Left = rngTOPL.Left + 1
.Visible = True
.Width = rngBTMR.Left - .Left + rngBTMR.Width
Set cht = .Chart
With cht
With .ChartArea
.AutoScaleFont = False
.Fill.BackColor.SchemeColor = 70
'.Fill.BackColor.Type = 2
.Fill.Visible = msoFalse
'.Left = 4
'.Top = 4
End With
.HasDataTable = False
.HasLegend = False
'.HasPivotFields = False
.HasTitle = False
cht.PlotArea.Left =
Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2,
rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left +
cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2
cht.PlotArea.Width = cht.PlotArea.Width -
cht.PlotArea.InsideWidth + 388
cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top -
cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13
cht.PlotArea.Height = cht.PlotArea.Height -
cht.PlotArea.InsideHeight + 137
End With
End With
'With .ChartObject("chtProdLine")
' .Left = 54
'End With
End With
End Sub
---------END---CODE---------------
Hope this is of help.
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
"John" wrote in message
...
I use excel 2003 and when I try to use the macro recoder to create a
custom
chart type of "Line - Column on 2 Axes" the recoder creates the
following
code and an example of the data is below the code. The chart that is
creaded
the first time when recording the macro is perfect.
However when I try to excute the macro I either receive an error message
runtime error 1004, "Methods axes of object - chart failed when I name
the
axes of the Series or
I simply have a column chart and not a combination chart.
What I am trying to do is have a single chart that shows staff in column
and
Volumn in line by time of day. I can make it work perfect by hand but I
am
unable to write a VBA routine that will accomplish the same thing again.
VBA
acts like it doesn't reconize the Chart Type.
Please help and Thanks in advance
'Macro Code
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData
Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
With Selection.Interior
.ColorIndex = 36
.Pattern = 1
End With
End Sub
Spread Sheet Data
Col B Col C Col D
Row 37 Hours Staff Volumn
Row 38 0:00 2 52280
Row 39 1:00 2 52280
Row 40 2:00 2 52280
Row 41 3:00 1 52280
Row 42 4:00 0 52280
Row 43 5:00 0 52280
Row 44 6:00 0 52280
Row 45 7:00 0 52280
Row 46 8:00 0 95280
Row 47 9:00 0 58380
Row 48 10:00 0 52380
Row 49 11:00 0 58980
Row 50 12:00 4 81280
Row 51 13:00 4 117080
Row 52 14:00 4 70880
Row 53 15:00 3 96100
Row 54 16:00 4 91900
Row 55 17:00 4 27600
Row 56 18:00 3 62300
Row 57 19:00 2 44900
Row 58 20:00 2 22700
Row 59 21:00 2 9000
Row 60 22:00 2 0
Row 61 23:00 2 0