The Ultimate Guide to Programming Simple Bar Chart in Excel VBA

When you’re trying to display data with complex relationships, sometimes a table just won’t cut it. You need to use charts instead, but programming them in Excel can be challenging. That’s where Microsoft Visual Basic for Applications (VBA) comes in; it lets you program bar charts in Excel. It allows you to automate your task. This guide will take you through the process of setting up and programming bar charts in VBA with minimal effort or time investment on your part.

Why Use Charts?

Excel is a powerful tool that can help you analyze data, create reports, and even generate graphs. One of the most popular types of graphs is the bar chart. This blog post will teach you how to use Visual Basic for Applications (VBA) to create a bar chart in Excel.

Basic types of charts

There are different types of charts you can use, and each one will be most helpful for a specific type of data. The basic types of charts available on the ribbon are Bar, Column, Line, and Pie.

How to create a bar chart in excel VBA

When you want to create a chart in Excel, you can use the chart wizard to help you select the data and choose the type of chart you want. However, if you want to automate, you can use Visual Basic for Applications (VBA) to create a chart.

Assuming you have your data set up in an Excel worksheet and create a bar chart.

Please follow these steps to create a bar chart using VBA

Sub createBarChart()
Dim dataRge As Range
Set dataRge = Application.InputBox(Prompt:="Select your chart data", Type:=8)
Charts.Add
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=dataRge, PlotBy:=xlBar
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveSheet.Name = "BarChart"
End Sub

1. Open the Visual Basic Editor by pressing Alt+F11.
2. Insert a new module by clicking Insert > Module.
3. Copy and paste the above code into the your new module
4. Go to your Excel file and Save as *.xlsm
5. Press Alt + F8 to open Macro dialog box
6. Select the Macro createBarChart
7. Press Run

8. Select the data range $A$1:$B$21 to create a bar chart
9. Press OK

Above steps create a bar chart for your data in a separate sheet.

Customizing your chart

You may want to customize your chart after you have created it. There are a few ways that you can customize your charts in Excel VBA. One way is by changing the colors of the bars on the chart. Another way is by adding labels to your chart so that they show up when you hover over them with your mouse pointer. You could also add a custom picture to your chart, or change the size of the font for all of the text on it.

Sub changeBarColor()
'change bar color to Orange

Sheets("BarChart").Activate
ActiveChart.FullSeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
ActiveChart.FullSeriesCollection(1).ApplyDataLabels

End Sub

VBA Script to change your barchart bar color to Orange and add data labels.

Conclusion

As can be seen from the above, Excel VBA is a powerful tool that can be used to create attractive and informative bar charts. By following the steps outlined in this guide, you can create bar charts that will impress your boss and colleagues. With a little practice, you will be able to create bar charts that are both aesthetically pleasing and informative.

This tutorial is here to help you get started with charts in your own Excel document. We hope that you will find this guide useful. Please feel free to reach out if you have any questions or need assistance at all!