Top
|
Chart Object Information - The file path to Excel 2003's Chart Help File is "C:\Program Files\Microsoft Office\OFFICE11\1033\VBAGR10.CHM".
I created a desktop link to this file and use it often to help me figure out some issue or other with the incredibly complicated Chart Object Model.
I read several books on VBA programming and was always confused about which sub objects belong to which object and how properties and methods fit into the picture. I really believe that I could have reduced my VBA - Chart learning curve if I had started with Excel's help file.
The file includes a hyperlinked Chart Object Model that you can use to navigate to specific chart elements to see how they relate to other chart elements and to view sample VBA code
If you click on an object, say Plot Area, you get detailed information on the PlotArea Object as shown in the graphic below. If you press the Multiple Objects box, it lists the sub objects of the Plot Area. You can then hyperlink to the sub objects.
|
Top
|
Chart List
This procedure counts the number of chartobjects on the activesheet and then prepares a list of all charts, listing the chart name, index, top and left position as well as chart width and height.
Public Sub chart_list()
Dim chtobj As ChartObject
Dim Msg As String
Dim n As Integer
n = ActiveSheet.ChartObjects.Count
Msg = "Chart List for Sheet " & vbTab & ActiveSheet.Name & vbTab & "No charts = " & n & vbCrLf & vbCrLf
Msg = Msg & "Name " & vbTab & vbTab & "Index" & vbTab & "Top Pos" & vbTab & "Left Pos " & vbTab & "Width " & vbTab & "Height" & vbCrLf
For Each chtobj In ActiveSheet.ChartObjects
cht_width = chtobj.Width
cht_height = chtobj.Height
Top_Position = chtobj.Top
Left_Position = chtobj.Left
Msg = Msg & chtobj.Name & vbTab & vbTab & chtobj.Index & vbTab & Top_Position & vbTab & Left_Position & vbTab & cht_width & vbTab & cht_height & vbCrLf
Next chtobj
out = MsgBox(Msg, , "Chart List")
End Sub
|
Top
|
Freeze Text Font Size
Excel's chart text format has a property to enable auto scaling so that the font is changed whenever the chart size changes. This can be a nuisance in cases where you have a nice chart format that you do not want adjusted when you tweak the chart size.
Jon Peltier has good discussion of this issue http://peltiertech.com/Excel/Charts/FixFonts.html.
Here is a simple VBA procedure to count the number of embedded charts on the activesheet, cycle through each sheet and set autoscale to false.
Public Sub Freeze_text()
Dim chtobj As ChartObject
Dim Msg As String
Dim n As Integer
n = ActiveSheet.ChartObjects.Count
'Msg = "There are " & n & " charts " & vbNewLine
For Each chtobj In ActiveSheet.ChartObjects
With chtobj.Chart.ChartArea
.AutoScaleFont = False
End With
Next chtobj
End Sub
|
Top
|
Size & Align Charts
Do you have several embedded charts on a worksheet? Do you want to line them up vertically or display them in a matrix 3 across by 4 down?
The Size_Align_Charts procedure counts the number of charts on your activesheet, freezes chart text font size, asks User for number of charts across, desired chart width and height in points and start cell for chart placement. Based on these User specifications, procedure sizes and aligns charts.
Chrt_size_align.xls includes an example of this procedure as well as the freeze font procedure.
Sub Size_align_charts()
'Procedure to size and align embedded charts on active worksheet
' Developed by D. Kelly O'Day, ProcessTrends.Com
' User specifies: Number of charts across (1 to max )
' Chart width - points
' Chart Height - points
' User selects start cell for charts
' Procedure automatically freezes text size to avoid distorting text as charts resized
Dim chrt_width As Single
Dim chrt_height As Single
Dim Chrts_across As Long
Dim Chrt_cnt As Long
Dim i As Integer
Dim Start_Top As Single
Dim Start_Left As Single
Chrt_cnt = ActiveSheet.ChartObjects.Count
' Check to see if charts on sheet - terminate if no charts
If Chrt_cnt = 0 Then
MsgBox ("No charts on sheet. Terminating.")
End
End If
Call Freeze_text ' Procedure to freeze text to for all charts on active sheet
Chrts_across = InputBox("How many charts across do you wants?", "Charts Across", , 0, 0)
chrt_width = InputBox("Enter Chart Width - Points?", "Chart Width - Points", , 0, 0)
chrt_height = InputBox("Enter Chart Height - Points", "Chart Height - Points", , 0, 0)
Set start_cell = Application.InputBox(prompt:="Select start celll for chart(s)", Type:=8)
On Error GoTo 0
If start_cell Is Nothing Then
MsgBox "You did not select a cell. Exiting procedure."
End
End If
' Go to start_cell range
Application.Goto reference:=start_cell
Start_Top = start_cell.Top
Start_Left = start_cell.Left
' Loop through all charts on active sheet
For i = 1 To Chrt_cnt
With ActiveSheet.ChartObjects(i)
.Width = chrt_width
.Height = chrt_height
v = i - 1
.Left = Start_Left + (v Mod Chrts_across) * chrt_width
.Top = Start_Top + Int((i - 1) / Chrts_across) * chrt_height
End With
Next
Range("a1").Select
End Sub
|