从Excel 2003 VBA到Excel 2007 VBA的图表 - 宏崩溃 - 帮助 [英] Charts From Excel 2003 VBA To Excel 2007 VBA - Macro Crashes - Help
问题描述
问候语：
我有一个相当复杂的Excel 2003 VBA例程，当然有效。当我尝试在Excel 2007 VBA中运行它时，它会在到达创建我的图表的区域时崩溃。
它在这一行崩溃：
ActiveChart.ApplyCustomType ChartType：= xlBuiltIn，TypeName：=" Line - 2轴上的列"
整个图表代码如下。
有人可以帮我解决这个问题，并查看剩下的代码并查看如果还有其他任何内容会在Excel 2007 VBA与Excel 2003 VBA中产生问题。我在这个应用程序中有几个图表，我相信这可能是一些简单的事情，它会影响所有这些，但我无法弄明白。
提前感谢您的任何帮助和帮助。
Glenn
'---------------------- -------------------------------------------------- --------------
'绘制蒙特卡洛图表''------------------------ -------------------------------------------------- ------------
Application.DisplayAlerts = False
Sheets（" Portfolio Output"）。激活
'如果未选择显示蒙特卡罗，则取消隐藏所有列
Cells.Select
Selection.EntireColumn.Hidden = False
'选择结束投资组合，平均剩余和成功百分比到图表范围（"K10"）。选择
范围（ActiveCell（1,3），活动Cell.End（xlDown））。选择
'Set GraphOutput = Range（ActiveCell（1,3），ActiveCell.End（xlDown））
'创建图表作为An Object
Charts.Add
ActiveChart.ApplyCustomType ChartType：= xlBuiltIn，TypeName：=" Line - 2 Axes上的列"
ActiveChart.Location其中：= xlLocationAsObject，Name：=" Portfolio Output" ;
'使用ActiveChart设置图表标题
.HasTitle = True
.ChartTitle.Characters.Text =" Monte Carlo Output"
.Axes（xlCategory，xlPrimary）。 HasTitle = True
.Axes（xlCategory，xlPrimary）.AxisTitle.Characters.Text =" Year"
ActiveChart.SeriesCollection（1）.XValues = YearDateOutput
.Axes（xlValue，xlPrimary）.HasTitle = True
.Axes（xlValue，xlPrimary）.AxisTitle.Characters.Text =" $ Value"
.Axes（xlCategory，xlSecondary） .HasTitle = False
.Axes（xlValue，xlSecondary）.HasTitle = True
.Axes（xlValue，xlSecondary）.AxisTitle.Characters.Text =" Success％"
< p align = left>'使用ActiveChart.PageSetup
'。LeftHeader =""
'。CenterHeader =""
'。RightHeader =""
'。LeftFooter = "&安培; d。 & T公司。 & Z& F"
'。CenterFooter =""
'。RightFooter =""
'结束
'将X轴刻度标签移动到"低"
ActiveChart.Axes（xlCategory）。选择
选择
.TickLabelPosition = xlLow
结束
格式成功系列Line
ActiveChart.SeriesCollection（3）。选择
使用Selection.Border
.ColorIndex = 50
.Weight = xlMedium
.LineStyle = xlContinuous
End With
使用选择
.MarkerBackgroundColorIndex = 50
.MarkerForegroundColorIndex = 50
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
'格式图表区域
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 2
.LineStyle = -1 < br>结束与
使用Selection.Interior
'。ColorIndex = 31
.ColorIndex = xlAutomatic
.PatternColorIndex = 1
.Pattern = 1
End With
结束与
'创建&格式图表图例
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
Selection.Border
.Weight = 2
.LineStyle = -1
End With
Selection.Interior
.ColorIndex = xlAutomatic
'。ColorIndex = 31
.PatternColorIndex = 1
.Pattern = 1
End使用
'删除现有图表工作表，创建新图表工作表&将图表对象移动到图表工作表
'将图表工作表移动到选项卡列表的末尾
表格（"蒙特卡罗图表"）。删除
ActiveChart.Location xlLocationAsNewSheet，"蒙特卡罗图表"
表格（"Monte Carlo Chart"）。移动后：=表格（"蒙特卡罗输出"）
表格（"蒙特卡罗图表"）.Tab.ColorIndex = 6
'重新隐藏投资组合输出表中的蒙特卡罗输出表和蒙特卡罗列...
'...如果未选择显示蒙特卡罗
如果范围（"MCTrue"）="False" ;然后
Sheets（"Monte Carlo Chart"）。Visible = False
Sheets（" Portfolio Output"）。选择
Range（"L：M"）。选择
Selection.EntireColumn .Hidden = True
Else：结束如果
Excel 2007对象模型已从Excel 2003更改。通常，较新版本向后兼容旧版本，但考虑到我们在此处获得的有关从2003年到2007年更改的问题的帖子数量，我认为在这种情况下它是不完全向后兼容。
在此处查看Excel 2003和Excel 2007之间Excel对象模型的文档更改：
自Microsoft Office以来的对象模型更改2003
请注意大部分图表属性和方法ds现在是"隐藏的"，包括ApplyCustomType。那是因为Excel 2007处理图表的方式不同。通常，"隐藏"是指"隐藏"。方法和属性可能仍然有效，但可能与之前的工作方式不同。在这里查看新的图表方法，属性等：
您提到它在ApplyCustomType行崩溃。如果你能准确地告诉我们你得到了什么错误，那将会有所帮助。
在该工作之前是否有线（即是否添加了新图表？）。如果它没有创建一个，那么下一行没有活动图表可供使用。如果它确实创建了一个，它是否被选中？如果它已创建但未被选中，则再次没有活动图表供下一行使用。
您还应该检查您尝试使用的自定义图表类型是否存在于Excel 2007中。
我怀疑您需要更改ApplyCustomType行以使用Excel 2007对象模型中的等效项（可能是ApplyLayout或ApplyChartTemplate） 。结果
Greetings:
I have a rather complicated Excel 2003 VBA routine that of course works. When I try to run it in Excel 2007 VBA it crashes when it gets to the area that creates my charts.
It crashes on this line:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
The entire chart code is below.
Can someone please help me with this and also look at the rest of the code and see if there is anything else that will create a problem in Excel 2007 VBA versus Excel 2003 VBA. I have several charts in this application and I am sure this is probably something simple and that it will affect them all, but I can't figure it out.
Thanks in advance for any help and assistance.
Glenn
'--------------------------------------------------------------------------------------
'Draws Monte Carlo Chart
'--------------------------------------------------------------------------------------
Application.DisplayAlerts = False
Sheets("Portfolio Output").Activate
'Unhides All Columns If Show Monte Carlo Is Not Selected
Cells.Select
Selection.EntireColumn.Hidden = False
'Selects Ending Portfolio, Average Residual, and Success Percent to Graph
Range("K10").Select
Range(ActiveCell(1, 3), ActiveCell.End(xlDown)).Select
'Set GraphOutput = Range(ActiveCell(1, 3), ActiveCell.End(xlDown))
'Creates The Chart As An Object
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Portfolio Output"
'Sets Chart Titles
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Monte Carlo Output"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Year"
ActiveChart.SeriesCollection(1).XValues = YearDateOutput
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "$ Value"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Success %"
'With ActiveChart.PageSetup
' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
' .LeftFooter = "&D. &T. &Z&F"
' .CenterFooter = ""
' .RightFooter = ""
'End With
'Moves X Axis Tick Labels To "Low"
ActiveChart.Axes(xlCategory).Select
With Selection
.TickLabelPosition = xlLow
End With
'Formats Succes Series Line
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 50
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 50
.MarkerForegroundColorIndex = 50
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
'Formats Chart Area
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 2
.LineStyle = -1
End With
With Selection.Interior
'.ColorIndex = 31
.ColorIndex = xlAutomatic
.PatternColorIndex = 1
.Pattern = 1
End With
End With
'Creates & Formats Chart Legend
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
With Selection.Border
.Weight = 2
.LineStyle = -1
End With
With Selection.Interior
.ColorIndex = xlAutomatic
'.ColorIndex = 31
.PatternColorIndex = 1
.Pattern = 1
End With
'Deletes Existing Chart Sheet, Creates New Chart Sheet & Moves Chart Object To Chart Sheet
'Moves Chart Sheet To End of Tab List
Sheets("Monte Carlo Chart").Delete
ActiveChart.Location xlLocationAsNewSheet, "Monte Carlo Chart"
Sheets("Monte Carlo Chart").Move After:=Sheets("Monte Carlo Output")
Sheets("Monte Carlo Chart").Tab.ColorIndex = 6
'Rehides Monte Carlo Output Sheets and Monte Carlo Columns in Portfolio Output Sheet...
'...If Show Monte Carlo Is Not Selected
If Range("MCTrue") = "False" Then
Sheets("Monte Carlo Chart").Visible = False
Sheets("Portfolio Output").Select
Range("L:M").Select
Selection.EntireColumn.Hidden = True
Else: End If
The Excel 2007 object model has changed from Excel 2003. In general, newer versions are backwards compatible with older versions, but given the number of posts we get here about problems with changing from 2003 to 2007, I think in this case it is not entirely backwards compatible.
See here for the documented changes to the Excel object model between Excel 2003 and Excel 2007:
Object Model Changes Since Microsoft Office 2003
Note that most of the charting properties and methods are now "hidden", including ApplyCustomType. That's because Excel 2007 handles charts differently. In general, "hidden" methods & properties might still work, but possibly not the same way they worked before. Look here for the new chart methods, properties, etc:
New Objects, Collections, and Enumerations
You mention that it crashes on the ApplyCustomType line. It would help if you could tell us exactly what error you get.
Did the line before that work (ie did it add a new chart?). If it didn't create one, thee there's no activechart for the next line to use. If it did create one, is it selected? If it's created but not selected, again there's no activechart for the next line to use.
You should also check if the custom chart type that you're trying to use even exists in Excel 2007.
I suspect that you will need to change the ApplyCustomType line to use the equivalent from the Excel 2007 object model (possibly ApplyLayout or ApplyChartTemplate).
这篇关于从Excel 2003 VBA到Excel 2007 VBA的图表 - 宏崩溃 - 帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
