Geek Answers Handbook

Excel 2010 macro has a run-time error of -2147467259 (80004005): Invalid parameter

I get a runtime error -2147467259 (80004005): Invalid parameter when running the vba macro 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

The strange thing is that when I run this code in Excel 2007, it works flawlessly and creates a line chart if necessary.

Here is the piece of 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!

+4
source share
1 answer

Ash -

In your code, try adding serial data and category labels before assigning the axes.

I can successfully run this code on the chart as long as there are at least 1 series, and the category axis labels are in date format.

I uploaded a book to Google Docs in which a chart has already been created. Delete the chart, but leave the series data in the B & C columns, then run the AshOriginalWithAddSeries macro. All I did was add one series of data with XValues date XValues , and then your code will work.

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$3:$C$14" ' or you can pass an array of stored values .XValues = Array(dummyDate) '"=Working!$B$3:$B$14" '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$3:$C$14" ' or you can pass an array of stored values .XValues = "=Working!$B$3:$B$14" 'or you can pass an array of values, make sure they are valid DATES though End With End Sub

I think your code does not work because there is no serial data and therefore no category values. This is peculiar because some properties of the axis can be set even when the axis does not exist. I noticed that this behavior was also in chart automation - sometimes this will not allow you to access properties if there is no series data.

0
source

More articles:


All Articles