Excel- vba- to preset chart axis labels for a macro- generated chart

0
votes

I have a problem. Let's say, for simplicity, I'm processing a simple two colunms of data repeatedly and the output is a plot, so I recorded a macro. But the problem is I want to set the axis labels on the chart, as I want, so as they have (the same) concrete names, every times I run the macro. But the vba- macro code does not record any information about the difference between the X and the Y axis, and the result is, that the one axis label is overwritten or, there are two same axis labels in the macro- generated chart. I add: code sample (in the code, I removed all unnecessary data, and such, that prevent the macro from running next time). What I want (I apologize for such rude formulation, but I just wanted to strictly state, what is my idea): I would like to have a solution, such, that every time I run the macro, I have a different chart, but with the same axis label names, i.e., X-axis: U [V], and Y-axis: I [A]

code:

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.ApplyLayout (1)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""I-V char"""
ActiveChart.SeriesCollection(1).XValues = "=hviezd1!$D$2:$D$193"
ActiveChart.SeriesCollection(1).Values = "=hviezd1!$E$2:$E$193"
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "I [A]"
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "U [V]"

I tried to record the macro by different ways such as throught the tool bar in excel (also more ways throught this), throught the keyboard, but none recorded code contains any information, that could distinguish between X and Y axis.

I'm using a 2007 excel (as I read before in other questions, the 2007 excel chart macro recorder is pretty poor and the problem, I'm talking about might be caused by this), but I'm looking for any good answer that can help solve my problem. Thank's for your answer.

2
excelgraphvba

2 Answers

4
votes

Here is a nice VBA script for drawing a bar chart. you need to make changes in cell values to make it work for you. For me it worked good. This is a kind of note of thanks to all the net users from who I learnt VBA (not expert level yet)....

Sub yourMethodName()

Dim myChart As Chart, cht As ChartObject
Dim rngChart As Range, desinationSheet As String
destinationSheet = ActiveSheet.Name
Set myChart = Charts.Add
Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:=destinationSheet)
myChart.SetSourceData Source:=Range("D37:E38").CurrentRegion, PlotBy:=xlColumns
myChart.ChartType = xlColumnClustered
ActiveSheet.ChartObjects(1).Activate
Set cht = ActiveChart.Parent
'Y-axis value is set up below
cht.Chart.Axes(xlValue).MinimumScale = 1
cht.Chart.Axes(xlValue).MaximumScale = 1
' X axis and Y axiz titles
With cht
'X axis title
cht.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
cht.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "All CRs"
 'y-axis title
cht.Chart.Axes(xlValue, xlPrimary).HasTitle = True
cht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% Complete"
End With

'Chart area is set up below
Set rngChart = Range("B12:G33")
cht.Left = rngChart.Left
cht.Top = rngChart.Top
cht.Width = rngChart.Width
cht.Height = rngChart.Height
Range("A37").Select

End Sub
0
votes

You can use this:

With ActiveChart 

 'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True 
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X-Axis" 
 'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True 
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Axis" 
End With