Excel 2013 auto-generated macro VBA code does not work correctly

Question

I am trying to create a very simple Excel macro in Office 2013. The macro should create a line chart with titles on the x and y axes. I go to the "Developer" tab and select "Record Macro". I create my chart and everything looks fine. When I view my auto-generated VBA code it looks like the following:

Sub SimplePlotExample()

Range("A1:B11").Select

ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$11")

ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)

Selection.Caption = "This is my rows title"

ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

Selection.Caption = "This is my y axis title"

Range("A1").Select

End Sub

The problem is that when I try to run this macro, the y-axis title does not get created and instead the x-axis has the title intended for the y-axis. If I go into the VBA code directly, comment out the line "Selection.Caption = "This is my y axis title"" and manually replace it with the code as shown below everything works fine.

Sub SimplePlotExample()

Range("A1:B11").Select

ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$11")

ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)

Selection.Caption = "This is my rows title"

ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

' Add the 2 lines of code below manually to my macro code

ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "This is my hand-edited y axis title"

' Selection.Caption = "This is my y axis title" ### This is the auto-generated line commented out

Range("A1").Select

End Sub

My questions is.....Why does the "auto-generated" VBA code not work correctly?  I am using Microsoft Office Professional Plus 2013.

Thanks!

Answer 1

Thank you very much for the response and the code is very helpful.  My primary question is "why does the auto-generated macro VBA code created by Excel not work correctly and/or cause errors when I try to run it?"

Hi,

Sorry, but i can't answer to your question.

Usually, the problem exists if you try to create /edit, shapes (pictures, charts ,...)

In any case, you can create a new thread  and i'm sure that someone in this forum,  

could help to solve your issue.

Answer 2

Thank you very much for the response and the code is very helpful.  My primary question is "why does the auto-generated macro VBA code created by Excel not work correctly and/or cause errors when I try to run it?".  My ultimate goal is to create an application that co-workers who are not familiar with VBA can use by creating their own Excel auto-generated VBA code.  However, this auto-generated code does not always seem to be 100% usable without manual manipulation of the code.  Any idea why this may be?  Thank you.

Answer 3

Hi,

try this sample

add two lines Line-1, Line-2 (red-green color)

pic1

vba macro

Sub AddChart_LineMarkers()

'May 24, 2017

Const xAxis1 As String = "A2:A10" '<<  labels-dates /primary x-axis

Dim ws As Worksheet

Set ws = Sheets("Sheet1") 'source sheet name

Dim N As Integer

N = ws.Range("A2:A10").Rows.Count

Set cel = ws.Range("E2") ' << add chart /top left cell location

'delete old chart from cell E2 (top left cell property)

For Each s In ws.Shapes

If Not Intersect(s.TopLeftCell, cel) Is Nothing Then s.Delete

Next

Dim obj As ChartObject

Set obj = ws.ChartObjects.Add(cel.Left, cel.Top, cel.Resize(, 10).Width, cel.Resize(20).Height)

Dim c As Chart

Set c = obj.Chart

'

With c.SeriesCollection.NewSeries'series1

.ChartType = xlLineMarkers

.Name = ws.Range("B1")

.Values = ws.Range("B2:B10")

.XValues = ws.Range(xAxis1)

.Format.Line.ForeColor.RGB = vbRed

For x = 1 To N

With .Points(x)

.HasDataLabel = True

.ApplyDataLabels Type:=xlValue

.DataLabel.Font.Size = 11

.DataLabel.Font.Color = vbRed

.Format.Fill.ForeColor.RGB = vbRed

End With

Next

End With

'

With c.SeriesCollection.NewSeries'series2

.ChartType = xlLineMarkers

.Name = ws.Range("C1")

.Values = ws.Range("C2:C10")

.XValues = ws.Range(xAxis1)

.Format.Line.ForeColor.RGB = vbGreen

For x = 1 To N

With .Points(x)

.HasDataLabel = True

.ApplyDataLabels Type:=xlValue

.DataLabel.Font.Size = 11

.DataLabel.Font.Color = vbGreen

.Format.Fill.ForeColor.RGB = vbGreen

End With

Next

End With

'

With c

'set titles in  x/y axes

.Axes(xlCategory).HasTitle = True

.Axes(xlCategory).AxisTitle.Text = "X-AXIS NAME"

.Axes(xlValue).HasTitle = True

.Axes(xlValue).AxisTitle.Text = "Y-AXIS NAME"

'set legend

.SetElement (msoElementLegendBottom)

'format chart-plot area

.ChartArea.Interior.Color = RGB(240, 255, 255)

.PlotArea.Interior.Color = RGB(240, 255, 255)

End With

End Sub