vba Excel 2010 宏有运行时错误 -2147467259 (80004005)：无效参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14965279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2010 Macro has run-time error -2147467259 (80004005): Invalid parameter
提问by ash
I'm getting a run-time error -2147467259 (80004005): Invalid parameterwhen running a vba macro I created in Excel 2010. The error occurs when I try to set .majorUnitScale = xlMonths after setting .CategoryType = xlTimeScale. Trying to create a chart with .chartType = xlLineMarkers
-2147467259 (80004005): Invalid parameter运行我在 Excel 2010 中创建的 vba 宏时出现运行时错误。当我尝试在设置 .CategoryType = xlTimeScale 后设置 .majorUnitScale = xlMonths 时发生错误。尝试使用 .chartType = xlLineMarkers 创建图表
The strange thing is that when I run this code in Excel 2007, it works flawlessly and produces a line chart as needed.
奇怪的是，当我在 Excel 2007 中运行此代码时，它可以完美运行并根据需要生成折线图。
Here's part of the code:
这是代码的一部分：
dim myChtObj as ChartObject
Set myChtObj = ActiveSheet.ChartObjects.Add(Left:=202, Width:=340, Top:=28,Height:=182)
With myChtObj.Chart
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
.ChartType = xlLineMarkers
.HasTitle = True
.ChartTitle.Text = "Performance Trends"
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Name = "Calibri"
.ChartTitle.Font.FontStyle = "Bold"
With .Axes(xlCategory)
.CategoryType = xlTimeScale
.BaseUnit = xlMonths
.MajorUnit = 2
.MajorUnitScale = xlMonths ' run-time error occurs here
.MinorUnit = 1
.MinorUnitScale = xlMonths
.TickLabels.NumberFormat = "mmm yy"
.TickLabels.Orientation = 45
End With
.....
End with
Thanks!
谢谢！
采纳答案by David Zemens
Ash -
灰——
In your code, try adding series data and category labels prior to assigning the axis scales.
在您的代码中，尝试在分配轴比例之前添加系列数据和类别标签。
I can successfully run this code on a chart so long as there is at least 1 series, and the category axis labels are in date format.
只要至少有 1 个系列，并且类别轴标签采用日期格式，我就可以在图表上成功运行此代码。
I have uploaded a workbook to Google Docs which has the chart already created. Delete the chart, but leave the series data in columns B&C, then run the macro AshOriginalWithAddSeries. All I have done really is to add one series of data with date-formatted XValues, and then your code works.
我已将工作簿上传到已创建图表的 Google Docs。删除图表，但将系列数据保留在 B&C 列中，然后运行宏AshOriginalWithAddSeries。我所做的只是添加一系列带有 date-formatted 的数据XValues，然后您的代码就可以工作了。
https://docs.google.com/file/d/0B1v0s8ldwHRYUWUtRWpqblgzM3M/edit?usp=sharing
https://docs.google.com/file/d/0B1v0s8ldwHRYUWUtRWpqblgzM3M/edit?usp=sharing
Sub AshOriginalWithAddSeries()
Dim cht As Chart
Dim srs As Series
Dim dummyDate As Date
Set cht = ActiveSheet.ChartObjects.Add(Left:=202, Width:=340, Top:=28, Height:=182).Chart
dummyDate = DateSerial(2013, 2, 1)
' remove extra series
With cht
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
'Add at least one series:
Set srs = .SeriesCollection.NewSeries
With srs
.Name = "Series Title"
.Values = 0.5 '=Working!$C:$C" ' or you can pass an array of stored values
.XValues = Array(dummyDate) '"=Working!$B:$B" 'or you can pass an array of values, make sure they are valid DATES though
End With
.ChartType = xlLineMarkers
.HasTitle = True
.ChartTitle.Text = "Performance Trends"
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Name = "Calibri"
.ChartTitle.Font.FontStyle = "Bold"
With .Axes(xlCategory)
.CategoryType = xlTimeScale
'.BaseUnit = xlMonths
.MajorUnit = 2
.TickLabels.NumberFormat = "mmm yy"
.TickLabels.Orientation = 45
'.MajorUnitScale = xlMonths ' run-time error occurs here
.MinorUnit = 1
'.MinorUnitScale = xlMonths
End With
End With
'Now assign some real values to srs
With srs
.Name = "Series Title"
.Values = "=Working!$C:$C" ' or you can pass an array of stored values
.XValues = "=Working!$B:$B" 'or you can pass an array of values, make sure they are valid DATES though
End With
End Sub
I think your code is failing because there is no series data, and consequently no category values. It is peculiar because some properties of the axis canbe set even when the axis doesn't exist. I have noticed similar behavior elsehwere in chart automation -- sometimes it won't let you access the properties unless there is series data.
我认为您的代码失败，因为没有系列数据，因此没有类别值。这很奇怪，因为即使轴不存在，也可以设置轴的某些属性。我注意到图表自动化中的 elsehwere 类似行为 - 有时除非有系列数据，否则它不会让您访问属性。
回答by fraggykrueger
I had the same issue.
我遇到过同样的问题。
I was able to resolve it by selecting all the cells containing the X axis values, right-clicking to open "Format Cells", and then setting the Number Category (1st tab) to "Date".
我能够通过选择包含 X 轴值的所有单元格来解决它，右键单击打开“格式单元格”，然后将数字类别（第一个选项卡）设置为“日期”。
