Create Bell Curve Chart with Excel VBA

The goal is to generate a normal distribution, then display it as a chart. Here’s a step-by-step guide along with the corresponding VBA code.

1. Create the Data

The bell curve is a graph of the normal distribution. To generate this, we will create X values (e.g., from -5 to +5) and calculate the corresponding Y values using the probability density function of the normal distribution.

2. VBA Code

Here is the detailed VBA code to create this chart:

Sub CreateBellCurve()
    ' Declare variables
    Dim ws As Worksheet
    Dim x As Double
    Dim mu As Double, sigma As Double
    Dim i As Long
    Dim nPoints As Long
    Dim rangeX As Range, rangeY As Range
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Initialize parameters for the normal curve
    mu = 0 ' Mean
    sigma = 1 ' Standard deviation
    nPoints = 100 ' Number of points to generate   
    ' Clear previous data
    ws.Cells.Clear   
    ' Generate X and Y data
    For i = 1 To nPoints
        x = (i - 1) * (10 / (nPoints - 1)) - 5 ' Generate X values from -5 to +5
        ws.Cells(i, 1).Value = x ' Place X in column A
        ws.Cells(i, 2).Value = (1 / (sigma * Sqr(2 * Application.Pi))) * Exp(-((x - mu) ^ 2) / (2 * sigma ^ 2)) ' Calculate Y (normal density)
    Next i   
    ' Define data ranges
    Set rangeX = ws.Range(ws.Cells(1, 1), ws.Cells(nPoints, 1))
    Set rangeY = ws.Range(ws.Cells(1, 2), ws.Cells(nPoints, 2))   
    ' Create a chart
    Dim chartObj As ChartObject
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=600, Height:=400)   
    ' Add a scatter chart type with smooth lines
    chartObj.Chart.SetSourceData Source:=Union(rangeX, rangeY)
    chartObj.Chart.ChartType = xlXYScatterSmooth ' Chart type: Smooth line  
    ' Add a title to the chart
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Gaussian Curve (Normal Distribution)"   
    ' Add axis titles
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "X (Values)"   
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability Density"
End Sub

3. Code Explanation

• Variable Declaration:
  • ws: The worksheet where the data will be created.
  • x, mu, sigma: Variables needed to calculate the normal distribution values. mu is the mean, and sigma is the standard deviation.
  • i: A counter for the loop that generates the data points.
  • nPoints: The number of data points to generate for the curve.
  • rangeX, rangeY: Ranges that hold the X and Y values for the chart.
• Generating X and Y Data:
  • For each X value, the corresponding Y value is calculated using the normal distribution formula.
• Creating the Chart:
  • A scatter plot with smooth lines (xlXYScatterSmooth) is added to the worksheet.
  • The data is linked to the chart using SetSourceData.
• Customizing the Chart:
  • The chart title is set to « Gaussian Curve (Normal Distribution) ».
  • Axis titles are added to the X-axis (« X (Values) ») and the Y-axis (« Probability Density »).

4. Running the Code

1. Open Excel and go to the VBA editor (press Alt + F11).
2. Insert a new module (Insert > Module).
3. Paste the code into the module.
4. Close the editor and run the macro by going to « Developer » > « Macros », selecting CreateBellCurve, and clicking « Run ».

5. Result

After running the code, a chart will appear on the worksheet, showing a smooth bell curve based on a standard normal distribution (mean = 0, standard deviation = 1).