---

All VBA codes I suggested are summarized in my github.

https://github.com/agronomy4future/VBA/blob/main/adjusting_axis

---

Here is one data, and I made three bar graphs per location.

You can download above data in my github.

https://github.com/agronomy4future/raw_data_practice/blob/main/VBA_practice.csv

---

Now, I’d like to add a title in x and y axis, and adjust the range and unit in y-axis. Of course, we can change it in each graph, but if there are 100 graphs, will you still do it one by one? We don’t have time to do such useless things.

Using VBA would be the powerful tool to change and adjust lots of graphs at a time. Today, I’ll introduce how to add a title and adjust in x and y-axis at a time using VBA.

---

---

First, open the VBA window, easily we can open as Alt + F11. Then, open the Module window (Insert → Module).

Then, write the below codes in the Module window.

Sub sample01 ()
Dim xytitle As chart
Set xytitle = Worksheets("graph").ChartObjects(1).chart
With xytitle.axes(xlCategory)
   .HasTitle = True
   .AxisTitle.Text = "Genotype"
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
With xytitle.axes(xlValue)
   .HasTitle = True
   .AxisTitle.Text = "Yield(kh/ha)"
   .MaximumScale = 150
   .MajorUnit = 30
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
End Sub

Remember!! The blue color text should be shown (activated). Otherwise, The codes do not work.

Then, press F5 to run the code.

Now, you can see the first graph was changed. Let’s go through what the VBA codes mean.

Sub sample01 ()
→ This is the basic construction of VBA code. This code name is sample 01 
Dim xytitle As chart
→ declare variable. I named variable name as xytitle, and this variable is for chart
Set xytitle = Worksheets("graph").ChartObjects(1).chart
→ xytitle set up in the worksheet name; "graph", and the first graph in the worksheet. For worksheet, please check your worksheet name in which graphs exist!!
With xytitle.axes(xlCategory)
→ about xytitle's x-axis
   .HasTitle = True
→ it has a title
   .AxisTitle.Text = "Genotype"
→ the title name is "Genotype"
   .AxisTitle.Font.Size = 16
→ the font size of axis title is 16
   .AxisTitle.Font.Bold = False
→ I'll not set up bold in title
End With
→ and 
With xytitle.axes(xlValue)
→ about xytitle's y-axis
   .HasTitle = True
→ it has a title
   .AxisTitle.Text = "Yield(kh/ha)"
→ the title name is "Yield(kh/ha)"
   .MaximumScale = 150
→ the maximum number is 150
   .MajorUnit = 30
→ the unit is 30
   .AxisTitle.Font.Size = 16
→ the font size of axis title is 16
   .AxisTitle.Font.Bold = False
→ I'll not set up bold in title
End With
→ and 
End Sub
→ code end

Now, I want to apply the same axis titles and font size to the second and third graph. In the VBA code, we set up the first graph as ChartObjects(1).chart. Then, simply add ChartObjects(2).chart and ChartObjects(3).chart.

Sub sample01 ()
Dim xytitle As chart
Set xytitle = Worksheets("graph").ChartObjects(1).chart
With xytitle.axes(xlCategory)
   .HasTitle = True
   .AxisTitle.Text = "Genotype"
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
With xytitle.axes(xlValue)
   .HasTitle = True
   .AxisTitle.Text = "Yield(kh/ha)"
   .MaximumScale = 150
   .MajorUnit = 30
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
Set xytitle = Worksheets("graph").ChartObjects(2).chart
With xytitle.axes(xlCategory)
   .HasTitle = True
   .AxisTitle.Text = "Genotype"
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
With xytitle.axes(xlValue)
   .HasTitle = True
   .AxisTitle.Text = "Yield(kh/ha)"
   .MaximumScale = 150
   .MajorUnit = 30
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
Set xytitle = Worksheets("graph").ChartObjects(3).chart
With xytitle.axes(xlCategory)
   .HasTitle = True
   .AxisTitle.Text = "Genotype"
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
With xytitle.axes(xlValue)
   .HasTitle = True
   .AxisTitle.Text = "Yield(kh/ha)"
   .MaximumScale = 150
   .MajorUnit = 30
   .AxisTitle.Font.Size = 16
   .AxisTitle.Font.Bold = False
End With
End Sub

Automatically all graphs were changed with the same x-y title, and the range and unit in y-axis.

---

---

For… Next code

How about there are 100 graph? Do we have to copy and paste the same code 100 times? Again!! We don’t have time to do such useless things. In this case, if we use For... Next code, we can quickly change graphs at a time.

For example, now I have 6 graphs, and I want to add the same x-y title and adjust the same range and unit in y-axis.

Here is a new code.

Sub sample02 ()  
Dim xytile As Chart  
Dim i As Integer    
For i = 1 To 6    
Set xytitle = Worksheets("graph").ChartObjects(i).Chart     
With xytitle.Axes(xlCategory)    
 .HasTitle = True    
 .AxisTitle.Text = "Genoytpe"  
 .AxisTitle.Font.Size = 16   
 .AxisTitle.Font.Bold = False      
End With    
With xytitle.Axes(xlValue)    
 .HasTitle = True   
 .AxisTitle.Text = "Yield(kg/ha)"    
 .MaximumScale = 150    
 .MajorUnit = 30    
 .AxisTitle.Font.Size = 16    
 .AxisTitle.Font.Bold = False    
End With    
Next    
End Sub

All graphs were changed at a time.

All of sudden, we realized that yield was not kg/ha, but kg/m2. So, we need to change the title of y-axis in all graphs, but we already made up the code, so simply we can change the title name of y-axis.

Sub sample02 ()  
Dim xytile As Chart  
Dim i As Integer    
For i = 1 To 6    
Set xytitle = Worksheets("graph").ChartObjects(i).Chart     
With xytitle.Axes(xlCategory)    
 .HasTitle = True    
 .AxisTitle.Text = "Genoytpe"  
 .AxisTitle.Font.Size = 16   
 .AxisTitle.Font.Bold = False      
End With    
With xytitle.Axes(xlValue)    
 .HasTitle = True   
 .AxisTitle.Text = "Yield(kg/m2)"    
 .MaximumScale = 0.15    
 .MajorUnit = 0.03    
 .AxisTitle.Font.Size = 16    
 .AxisTitle.Font.Bold = False    
End With    
Next    
End Sub

When we run above code, all graphs were changed at a time.

---

---