Creative Blog

Étiquette : create

  • Create Charts in Excel VBA

    Objective of the Code:

    This code creates a simple chart based on the data from an Excel worksheet, customizes the chart’s appearance, and allows you to adjust chart elements such as titles, axes, and colors.

    VBA Code to Create a Chart:

    Sub CreateChart()
    ' Declare variables for the worksheet and chart
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rangeData As Range 
    ' Assign the active worksheet to the variable ws
    Set ws = ActiveSheet   
    ' Define the data range for the chart (for example A1:B10)
    Set rangeData = ws.Range("A1:B10")   
    ' Create a chart object in the worksheet (position 100x100, size 400x300)
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)   
    ' Set the data source for the chart
    chartObj.Chart.SetSourceData Source:=rangeData   
    ' Set the chart type (e.g., a clustered column chart)
    chartObj.Chart.ChartType = xlColumnClustered   
    ' Customize the chart title
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Sales Chart"   
    ' Customize the title for the X-axis (horizontal)
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Months"   
    ' Customize the title for the Y-axis (vertical)
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales in $"   
    ' Change the color of the chart columns
    With chartObj.Chart.SeriesCollection(1)
        .Interior.Color = RGB(0, 112, 192) ' Blue color
    End With   
    ' Add a legend (optional)
    chartObj.Chart.HasLegend = True
    chartObj.Chart.Legend.Position = xlLegendPositionBottom   
    ' Activate the worksheet
    ws.Activate
End Sub

    Explanation of the Code:

    Declaring Variables:

    Dim ws As Worksheet
Dim chartObj As ChartObject
Dim rangeData As Range
      • ws : A variable representing the worksheet where the chart will be added.
      • chartObj : A variable for the chart object itself.
      • rangeData : The range of cells containing the data to be displayed in the chart.

    Selecting the Active Worksheet:

    Set ws = ActiveSheet

    This line assigns the currently active worksheet to the variable ws. This means the chart will be added to whichever sheet is active when you run the code.

    Defining the Data Range:

    Set rangeData = ws.Range("A1:B10")

    The range of data you want to include in the chart is specified here. This range should contain the values for both the X and Y axes of the chart.

    Creating the Chart Object:

    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)

    This line creates a chart on the worksheet at a specific position (100 pixels from the left, 100 pixels from the top) and with dimensions 400×300 pixels.

    Setting the Data Source for the Chart:

    chartObj.Chart.SetSourceData Source:=rangeData

    The chart is linked to the specified data range (rangeData), so it will display the values contained in that range.

    Setting the Chart Type:

    chartObj.Chart.ChartType = xlColumnClustered

    This line sets the type of chart. In this case, it’s a clustered column chart (xlColumnClustered). You can change the chart type by modifying this line (for example, for a line chart, you can use xlLine).

    Customizing the Chart Title:

    chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Sales Chart"

    This section enables the chart title and sets its text to « Sales Chart ». You can customize the title as needed.

    Customizing Axis Titles:

    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Months"
chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales in $"

    These lines add titles to the chart axes:

      • The X-axis (category axis) gets the title « Months ».
      • The Y-axis (value axis) gets the title « Sales in $ ».

    Customizing the Column Colors:

    With chartObj.Chart.SeriesCollection(1)
    .Interior.Color = RGB(0, 112, 192) ' Blue color
End With

    This section changes the color of the columns in the chart to blue (using the RGB function).

    Adding a Legend:

    chartObj.Chart.HasLegend = True
chartObj.Chart.Legend.Position = xlLegendPositionBottom

    The legend is enabled and positioned at the bottom of the chart. If you don’t want a legend, you can disable this by setting HasLegend = False.

    Finalizing and Refreshing the Worksheet:

    ws.Activate

    This line reactivates the worksheet after creating the chart, so you can immediately see the chart in your Excel window.

    Conclusion:

    This code creates a simple chart using VBA, but it can easily be customized to meet your specific needs. You can change the data range, chart type, colors, titles, and more. It provides a good foundation for automating the creation and customization of charts in Excel using VBA.

     

  • Create a candlestick chart in Excel VBA

    1. Open the VBA Editor

    To add this VBA code in Excel:

    • Open Excel.
    • Press Alt + F11 to open the VBA editor.
    • Go to Insert > Module to add a new module.
    • Paste the code below into the module window.
    1. VBA Code to Create a Candlestick Chart
    Sub CreateCandlestickChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range   
    ' Reference to the active sheet
    Set ws = ActiveSheet   
    ' Define the range of data to use for the chart
    ' Example data: Columns A (Date), B (Open), C (High), D (Low), E (Close)
    Set dataRange = ws.Range("A1:E10") ' Adjust this range according to your data   
    ' Create the chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    chartObj.Chart.SetSourceData Source:=dataRange   
    ' Set the chart type to candlestick chart (OHLC)
    chartObj.Chart.ChartType = xlStockOHLC ' Using OHLC chart type for candlestick   
    ' Add titles for each axis and the chart
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Candlestick Chart"   
    ' Customize the X-axis (date axis)
    With chartObj.Chart.Axes(xlCategory)
        .CategoryNames = ws.Range("A2:A10") ' Date range
        .TickLabelPosition = xlLow
    End With   
    ' Customize the Y-axis (value axis)
    With chartObj.Chart.Axes(xlValue)
        .MinimumScale = 0 ' Minimum value (adjust based on your data)
        .MaximumScale = 100 ' Maximum value (adjust based on your data)
    End With   
    ' Customize the colors of the candlesticks
    With chartObj.Chart.SeriesCollection(1)
        .UpFill.ForeColor.RGB = RGB(0, 255, 0) ' Green for bullish candles
        .DownFill.ForeColor.RGB = RGB(255, 0, 0) ' Red for bearish candles
        .Border.Color = RGB(0, 0, 0) ' Black border
    End With   
    ' Disable the legend (optional)
    chartObj.Chart.HasLegend = False
End Sub
    1. Explanation of the Code

    Variable Declarations:

      • ws is a reference to the active worksheet.
      • chartObj is the object that will hold the created chart.
      • dataRange is the range of data that will be used to create the chart.

    Data Range:

      • The candlestick chart requires four types of data:
        • Open
        • High
        • Low
        • Close
      • In this example, the data is in columns A to E, from row 1 to row 10 (A1:E10). You can adjust the range according to your dataset.
    • Creating the Chart:
      • The chart is created using the ChartObjects.Add method. This adds a chart to the active worksheet.
      • SetSourceData Source:=dataRange sets the data range for the chart.
    • Chart Type:
      • The chart is set to be a candlestick chart using the xlStockOHLC chart type.
    • Customizing the Axes:
      • The category axis (X-axis) represents the dates. The CategoryNames property sets the dates from column A (A2:A10).
      • The value axis (Y-axis) is configured with minimum and maximum values. You can adjust the minimum and maximum scale values based on your data.
    • Customizing Candlestick Colors:
      • Bullish candles (closing price > opening price) are colored green, while bearish candles (closing price < opening price) are colored red.
      • The border of the candles is set to black.
    • Disabling the Legend:
      • The legend is turned off with HasLegend = False. You can enable it if you prefer.
    1. How to Use the Code
    • After pasting the code, you can run it by pressing F5 in the VBA editor, or you can create a button on your worksheet and assign this macro to the button.
    • Once the macro is executed, a candlestick chart will be generated on the active sheet using the specified data range.
    1. Sample Data

    Here is an example of the data you can use to test the code:

    Date Open High Low Close
    01/12/2024 100 105 98 102
    02/12/2024 102 106 100 104
    03/12/2024 104 108 103 107
    04/12/2024 107 110 106 109
    05/12/2024 109 111 108 110

    Don’t forget to adjust the data range in the code to match your own dataset.

    Conclusion

    This code creates a simple and customizable candlestick chart. You can adjust the data range, colors, and other settings to fit your specific needs.

  • Creating a calendar in Excel using VBA

    VBA Code to Create a Calendar

    Sub CreateCalendar()
    Dim ws As Worksheet
    Dim month As Integer
    Dim year As Integer
    Dim firstDay As Date
    Dim lastDay As Date
    Dim day As Integer
    Dim cell As Range
    Dim i As Integer, j As Integer
    ' Ask the user for the month and year
    month = InputBox("Enter the month number (1-12):")
    year = InputBox("Enter the year:")
    ' Check if the month and year are valid
    If month < 1 Or month > 12 Then
        MsgBox "Invalid month. Please enter a month between 1 and 12.", vbCritical
        Exit Sub
    End If   
    If year < 1900 Or year > 9999 Then
        MsgBox "Invalid year. Please enter a valid year.", vbCritical
        Exit Sub
    End If
    ' Create a new worksheet for the calendar
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Calendar " & month & "-" & year
    ' Calculate the first and last days of the month
    firstDay = DateSerial(year, month, 1)
    lastDay = DateSerial(year, month + 1, 0)   
    ' Calendar title (month and year)
    ws.Cells(1, 1).Value = "Calendar of " & MonthName(month) & " " & year
    ws.Cells(1, 1).Font.Size = 16
    ws.Cells(1, 1).Font.Bold = True
    ws.Cells(1, 1).HorizontalAlignment = xlCenter
    ws.Range("A1:G1").Merge
    ' Weekday headers
    ws.Cells(2, 1).Value = "Sun"
    ws.Cells(2, 2).Value = "Mon"
    ws.Cells(2, 3).Value = "Tue"
    ws.Cells(2, 4).Value = "Wed"
    ws.Cells(2, 5).Value = "Thu"
    ws.Cells(2, 6).Value = "Fri"
    ws.Cells(2, 7).Value = "Sat"
    ws.Rows(2).Font.Bold = True
    ' Fill the calendar with days
    day = 1
    For i = 3 To 8 ' Rows of the calendar
        For j = 1 To 7 ' Columns (days of the week)
            ' If it's the first day of the month, start in the correct column
            If i = 3 And j = Weekday(firstDay, vbSunday) Then
                ws.Cells(i, j).Value = day
                day = day + 1
            ' Fill the remaining days
            ElseIf day <= Day(lastDay) Then
                ws.Cells(i, j).Value = day
                day = day + 1
            End If
        Next j
    Next i
    ' Adjust column widths and row heights
    ws.Columns("A:G").ColumnWidth = 4
    ws.Rows("2:8").RowHeight = 25
    ' Format the cells for the days
    For i = 3 To 8
        For j = 1 To 7
            Set cell = ws.Cells(i, j)
            cell.HorizontalAlignment = xlCenter
            cell.VerticalAlignment = xlCenter
        Next j
    Next i
    MsgBox "Calendar created for " & MonthName(month) & " " & year, vbInformation
End Sub

    Explanation of the Code:

    1. Ask for the month and year:
      The code starts by asking the user to input the month (between 1 and 12) and the year using InputBox. If the values entered are invalid (for example, a month outside the range 1-12), an alert is displayed, and the process is stopped.
    2. Create a new worksheet:
      A new worksheet is created to display the calendar. The worksheet is named with the format « Calendar M-YYYY » (for example, « Calendar 12-2024 »).
    3. Calculate the first and last day of the month:
      The first day of the month is calculated using DateSerial(year, month, 1), and the last day of the month is found using DateSerial(year, month + 1, 0), which returns the last day of the previous month (thus the month we want).
    4. Calendar title:
      The title « Calendar of month year » is inserted in cell A1, and this cell is merged with the others in the row to span the width of the calendar.
    5. Weekday headers:
      The headers for the days of the week (Sunday, Monday, etc.) are added in row 2. These cells are bold to make them stand out.
    6. Fill the calendar with days:
      The calendar is filled row by row. The code uses a loop to place the days in the correct cells, considering the weekday of the 1st day of the month (Weekday(firstDay, vbSunday)). It continues filling the days until the last day of the month.
    7. Adjust column width and row height:
      The columns are adjusted to a fixed width, and the row heights are modified to make the calendar more readable. The cells are also centered horizontally and vertically.
    8. Confirmation message:
      A MsgBox pops up at the end to inform the user that the calendar has been successfully created.

    How to Use the Code:

    1. Open the VBA editor:
      Open Excel, then press Alt + F11 to open the VBA editor.
    2. Add a module:
      In the VBA editor, go to Insert > Module to create a new module.
    3. Copy the code:
      Copy the code above and paste it into the new module.
    4. Run the code:
      To run the code, press F5 or go to Run > Run Sub/UserForm.

    The calendar will be generated in a new worksheet with the specified month and year.

    Customization:

    • You can add events or color-code specific days by modifying the logic that fills the cells.
    • You can also customize the font size, style, and other visual aspects of the calendar for better appearance.

     

  • Creating a bullet chart in Excel VBA

    Since Excel does not have a built-in « bullet chart » type, we can simulate this using shapes (rectangles) to represent the bullet chart style.

    Main Steps:

    1. Create a dataset with values that will be displayed as bullets.
    2. Insert bars (e.g., horizontal rectangles) to simulate the bullets.
    3. Format these bars to look like a bullet chart.

    Example VBA Code to Create a Bullet Chart:

    Sub CreateBulletChart()
    Dim ws As Worksheet
    Dim i As Integer
    Dim dataRange As Range
    Dim bulletWidth As Double
    Dim maxLength As Double
    Dim maxValue As Double
    Dim rect As Shape   
    ' Create a new worksheet for the chart
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "BulletChart"   
    ' Sample data (values to display as bullets)
    ws.Cells(1, 1).Value = "Name"
    ws.Cells(1, 2).Value = "Value"   
    ws.Cells(2, 1).Value = "Item 1"
    ws.Cells(2, 2).Value = 7
    ws.Cells(3, 1).Value = "Item 2"
    ws.Cells(3, 2).Value = 5
    ws.Cells(4, 1).Value = "Item 3"
    ws.Cells(4, 2).Value = 9
    ws.Cells(5, 1).Value = "Item 4"
    ws.Cells(5, 2).Value = 6   
    ' Set the data range
    Set dataRange = ws.Range("A2:B5")   
    ' Find the maximum value in the "Value" column
    maxValue = Application.WorksheetFunction.Max(ws.Range("B2:B5"))   
    ' Set the width of the bullet bars and maximum length
    bulletWidth = 5 ' Initial width of the bullet
    maxLength = 200 ' Maximum width of the bars  
    ' Create the bullet chart (insert rectangle shapes for each row)
    For i = 2 To dataRange.Rows.Count
        ' Add a rectangle shape for each item
        Set rect = ws.Shapes.AddShape(msoShapeRectangle, 100, 20 * i, 0, 10)       
        ' Set the width of the rectangle based on the value
        rect.Width = (ws.Cells(i, 2).Value / maxValue) * maxLength       
        ' Format the bullet (color, border, etc.)
        rect.Fill.ForeColor.RGB = RGB(0, 0, 255) ' Blue color
        rect.Line.Visible = msoFalse ' No border
        rect.LockAspectRatio = msoFalse ' Unlock aspect ratio of the shape
    Next i  
    ' Adjust columns and rows for better visualization
    ws.Columns("A:B").AutoFit
    ws.Rows("1:1").RowHeight = 20
End Sub

    Explanation of the Code:

    Create a New Worksheet: A new worksheet is created to host the bullet chart.

    Set ws = ThisWorkbook.Sheets.Add
ws.Name = "BulletChart"

    Insert Data: We add some sample data (names and values) to the worksheet. These values will be represented as bullet bars.

    ws.Cells(1, 1).Value = "Name"
ws.Cells(1, 2).Value = "Value"
ws.Cells(2, 1).Value = "Item 1"
ws.Cells(2, 2).Value = 7

    Find Maximum Value: We calculate the maximum value from the « Value » column. This will be used to scale the width of the bullet bars.

    maxValue = Application.WorksheetFunction.Max(ws.Range("B2:B5"))

    Create Bullet Bars (Rectangle Shapes): For each value in the « Value » column, a rectangle shape is added to represent a bullet. The width of the rectangle is proportional to the value compared to the maximum value.

    Set rect = ws.Shapes.AddShape(msoShapeRectangle, 100, 20 * i, 0, 10)
rect.Width = (ws.Cells(i, 2).Value / maxValue) * maxLength

    Format the Bullet Bars: Each rectangle is formatted by setting a color (blue) and removing the border for a clean look. The aspect ratio of the rectangle is unlocked to allow for free resizing.

    rect.Fill.ForeColor.RGB = RGB(0, 0, 255) ' Blue color
rect.Line.Visible = msoFalse ' No border

    Adjust Columns and Rows: Finally, we autofit the columns and adjust the row height to make the chart more readable.

    ws.Columns("A:B").AutoFit
ws.Rows("1:1").RowHeight = 20

    Result:

    This code will create a bullet chart on a new worksheet, where each row represents an item, and the width of the bullet (represented by a rectangle) is proportional to the value in the « Value » column.

  • Creating a bubble chart with a variable size in Excel using VBA

    A bubble chart is a type of chart where each data point is represented by a bubble, whose position on the X and Y axes is determined by values from those axes, and its size is determined by a third variable.

    Objectives:

    • Create a bubble chart.
    • Add data with X, Y values, and bubble sizes.
    • Customize chart properties.

    Example Data:

    X Value Y Value Bubble Size
    10 20 15
    30 40 30
    50 60 25
    70 80 10

    Detailed VBA Code:

    Sub CreateBubbleChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As Chart
    Dim dataRange As Range   
    ' Assign the active worksheet to the ws variable
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name   
    ' Define the data range for the chart (for example, A1 to C5)
    Set dataRange = ws.Range("A1:C5") ' Adjust the range to your data   
    ' Create a chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=500, Height:=300)   
    ' Assign the created chart to the chart variable
    Set chart = chartObj.Chart   
    ' Set the chart type to "Bubble"
    chart.ChartType = xlBubble   
    ' Assign the data to the chart
    chart.SetSourceData Source:=dataRange   
    ' Configure the chart series
    With chart.SeriesCollection(1)
        ' Set the X, Y values and bubble size
        .XValues = ws.Range("A2:A5") ' X values
        .Values = ws.Range("B2:B5") ' Y values
        .BubbleSizes = ws.Range("C2:C5") ' Bubble size
    End With   
    ' Customize the chart (example)
    With chart
        ' Add a chart title
        .HasTitle = True
        .ChartTitle.Text = "Bubble Chart"       
        ' Add titles to the X and Y axes
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Value"       
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y Value"      
        ' Customize bubble colors
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(0, 255, 0) ' Bubble color is green
    End With   
    ' Display the chart
    chartObj.Visible = True
End Sub

    Detailed Explanation of the Code:

    1. Variable Declaration:
      • ws: A variable that refers to the worksheet containing the data.
      • chartObj: The chart object that will be created in the worksheet.
      • chart: The chart object that allows manipulation of the chart.
      • dataRange: The range of data containing X values, Y values, and bubble sizes.
    2. Defining the Data Range:
      • The code refers to a data range in the worksheet (A1:C5), which contains X, Y, and bubble size values.
    3. Creating the Chart:
      • The ChartObjects.Add method creates a chart as an object.
      • Then, the chart type is set to a bubble chart using chart.ChartType = xlBubble.
    4. Configuring the Chart Data:
      • XValues: The data range for the X-axis values.
      • Values: The data range for the Y-axis values.
      • BubbleSizes: The data range for the size of the bubbles.
    5. Customizing the Chart:
      • Add a title to the chart and axis titles.
      • The color of the bubbles is customized (in this case, set to green).
      • You can further adjust the chart (e.g., change colors, titles, labels, etc.).
    6. Displaying the Chart:
      • chartObj.Visible = True ensures the chart is visible after creation.

    Customization:

    You can adjust:

    • The bubble sizes by modifying the values in the « Bubble Size » column.
    • The appearance of the chart, bubble colors, axis labels, and other visual properties.
    • The data range can be adjusted based on the position and size of your dataset.

    Note:

    To run this code, you need to open the VBA editor in Excel (Alt + F11), create a new module, and paste this code there. Then, you can run it by pressing F5 or calling it through a button on your worksheet.

     

  • Create a bubble chart in Excel VBA.

    Steps to Follow:

    1. Open the VBA Editor:
      • In Excel, press Alt + F11 to open the VBA editor.
      • In the editor, go to Insert and then click Module to create a new module.
    2. Insert the following code into the new module:
    Sub CreateBubbleChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    ' Define the data range for the chart (e.g., A1:C10)
    Set dataRange = ws.Range("A1:C10") ' Replace with the range of your data
    ' Add a bubble chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=500, Height:=300
    ' Set the chart type to bubble chart
    chartObj.Chart.ChartType = xlBubble
    ' Set the data source for the chart
    chartObj.Chart.SetSourceData Source:=dataRange
    ' Add axis titles and chart title
    With chartObj.Chart
        .HasTitle = True
        .ChartTitle.Text = "Bubble Chart Example"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Axis (Value 1)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y Axis (Value 2)"
        .Axes(xlBubbleSize, xlPrimary).HasTitle = True
        .Axes(xlBubbleSize, xlPrimary).AxisTitle.Text = "Bubble Size (Value 3)"
    End With
    ' Customize the legend (optional)
    chartObj.Chart.HasLegend = True
    chartObj.Chart.Legend.Position = xlLegendPositionBottom
    ' Modify bubble colors (optional)
    Dim series As Series
    Set series = chartObj.Chart.SeriesCollection(1)
    series.Format.Fill.ForeColor.RGB = RGB(0, 255, 0) ' Set bubble color to green
End Sub

    Explanation of the Code:

    1. Variable Declaration:
      • ws: A reference to the worksheet containing your data.
      • chartObj: A reference to the chart object (the bubble chart).
      • dataRange: The range of data containing the X values, Y values, and bubble sizes.
    2. Setting the Worksheet:
      • The ws variable refers to the specified worksheet (here, « Sheet1 »). Replace « Sheet1 » with your actual sheet name.
    3. Defining the Data Range:
      • The dataRange is defined for the range that contains the values for the X axis (horizontal), Y axis (vertical), and the bubble sizes.
    4. Creating the Bubble Chart:
      • A new chart object is added to the worksheet using ChartObjects.Add.
      • The chart type is set to xlBubble, which creates a bubble chart.
    5. Setting Titles for Axes and the Chart:
      • Titles for the X axis, Y axis, and the size of the bubbles are added using .HasTitle and .AxisTitle.Text.
    6. Customizing the Legend:
      • The legend is enabled and positioned at the bottom of the chart using .Legend.Position = xlLegendPositionBottom.
    7. Customizing the Bubble Color:
      • The color of the bubbles is customized using series.Format.Fill.ForeColor.RGB. In this case, the bubbles are colored green (RGB(0, 255, 0)).

    Sample Data for the Bubble Chart:

    To use this code, your data should be structured like this in your worksheet:

    X Value Y Value Bubble Size
    10 20 15
    30 50 25
    40 60 35
    50 80 45
    60 90 55

    Each row represents one « bubble » on the chart, where:

    • X Value: Determines the position on the X-axis (horizontal).
    • Y Value: Determines the position on the Y-axis (vertical).
    • Bubble Size: Determines the size of the bubble.

    Running the Code:

    1. After inserting the code into the VBA editor, press Alt + F8 to open the Macro dialog.
    2. Select CreateBubbleChart and click Run.

    This will generate a bubble chart with the data specified in the range A1:C10 on your worksheet. You can adjust the code to match your own data layout or further customize the appearance of the chart.

     

  • Creating a Box Plot (Box and Whisker chart) in Excel VBA

    This code calculates the required statistics (minimum, first quartile, median, third quartile, and maximum) and creates the chart based on these values.

    Objective:

    • The code will take a range of data, calculate the necessary statistics for the box plot (minimum, first quartile, median, third quartile, and maximum), and then create a chart from those values.
    1. Preparing Data

    Before running the code, ensure your data is in a column in Excel. Let’s assume your data is in column A.

    1. Create a VBA Module
    1. Press Alt + F11 to open the VBA editor.
    2. From the Insert menu, select Module to create a new module.
    3. Copy and paste the following code into the module.

    VBA Code for Creating a Box Plot

    Sub CreateBoxPlot()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim Min As Double, Q1 As Double, Median As Double, Q3 As Double, Max As Double
    Dim BoxChart As ChartObject
    Dim CalcTable As Range
    Dim SerieData As Range
    ' Set the active worksheet
    Set ws = ActiveSheet   
    ' Set the range for the data (e.g., A2:A101)
    Set dataRange = ws.Range("A2:A101")   
    ' Calculate the necessary statistics for the box plot
    Min = Application.WorksheetFunction.Min(dataRange)
    Q1 = Application.WorksheetFunction.Quartile_Inc(dataRange, 1)
    Median = Application.WorksheetFunction.Median(dataRange)
    Q3 = Application.WorksheetFunction.Quartile_Inc(dataRange, 3)
    Max = Application.WorksheetFunction.Max(dataRange)   
    ' Insert a temporary table to store the results
    Set CalcTable = ws.Range("C2:C6")
    CalcTable.Cells(1, 1).Value = Min
    CalcTable.Cells(2, 1).Value = Q1
    CalcTable.Cells(3, 1).Value = Median
    CalcTable.Cells(4, 1).Value = Q3
    CalcTable.Cells(5, 1).Value = Max   
    ' Create a box plot chart object
    Set BoxChart = ws.ChartObjects.Add(Left:=300, Width:=400, Top:=100, Height:=300)
    BoxChart.Chart.ChartType = xlColumnClustered   
    ' Add series to the chart
    BoxChart.Chart.SeriesCollection.NewSeries
    BoxChart.Chart.SeriesCollection(1).XValues = Array("Min", "Q1", "Median", "Q3", "Max")
    BoxChart.Chart.SeriesCollection(1).Values = CalcTable   
    ' Add a title to the chart
    BoxChart.Chart.HasTitle = True
    BoxChart.Chart.ChartTitle.Text = "Box Plot"   
    ' Customize the chart (hide the column bars)
    BoxChart.Chart.SeriesCollection(1).Format.Fill.Visible = msoFalse
    BoxChart.Chart.SeriesCollection(1).Format.Line.Visible = msoFalse   
    ' Add a line chart to connect the points
    BoxChart.Chart.SeriesCollection.NewSeries
    BoxChart.Chart.SeriesCollection(2).XValues = Array("Min", "Q1", "Median", "Q3", "Max")
    BoxChart.Chart.SeriesCollection(2).Values = CalcTable
    BoxChart.Chart.SeriesCollection(2).ChartType = xlLine
    BoxChart.Chart.SeriesCollection(2).Format.Line.Color = RGB(0, 0, 0)   
    ' Show specific points for Min, Q1, Median, Q3, Max
    BoxChart.Chart.SeriesCollection(2).Points(1).MarkerStyle = xlMarkerStyleCircle
    BoxChart.Chart.SeriesCollection(2).Points(1).MarkerSize = 8
    BoxChart.Chart.SeriesCollection(2).Points(1).MarkerBackgroundColor = RGB(0, 0, 255)   
    BoxChart.Chart.SeriesCollection(2).Points(2).MarkerStyle = xlMarkerStyleCircle
    BoxChart.Chart.SeriesCollection(2).Points(2).MarkerSize = 8
    BoxChart.Chart.SeriesCollection(2).Points(2).MarkerBackgroundColor = RGB(0, 255, 0)   
    BoxChart.Chart.SeriesCollection(2).Points(3).MarkerStyle = xlMarkerStyleCircle
    BoxChart.Chart.SeriesCollection(2).Points(3).MarkerSize = 8
    BoxChart.Chart.SeriesCollection(2).Points(3).MarkerBackgroundColor = RGB(255, 0, 0)   
    ' Clear the temporary calculation table
    CalcTable.ClearContents  
End Sub

    Explanation of the Code:

    1. Defining Variables:
      • ws: The active worksheet.
      • dataRange: The range of data for which the box plot will be created.
      • Min, Q1, Median, Q3, Max: The statistical values needed for the box plot.
      • BoxChart: An object for the chart that will be created.
      • CalcTable: A temporary table used to store the statistical values.
    2. Calculating Required Statistics:
      • The Min, Q1, Median, Q3, and Max values are calculated using Excel’s built-in functions (MIN, QUARTILE_INC, MEDIAN, MAX).
    3. Creating the Chart:
      • A clustered column chart (xlColumnClustered) is created, and the calculated values (Min, Q1, Median, Q3, Max) are added to it as series.
      • The chart’s type is changed to a line chart (xlLine) to connect the points representing the statistical values.
    4. Customizing the Chart:
      • The column bars are hidden using msoFalse, as we only want to see the connecting lines.
      • Specific markers (circles) are added at each of the statistical points (Min, Q1, Median, Q3, Max) to make them visually distinct.
    5. Cleanup:
      • The temporary table (CalcTable) is cleared after the chart is created.

    How to Run the Code:

    1. Enter your data in column A (for example, from A2:A101).
    2. Press Alt + F8, select CreateBoxPlot, and click Run.
    3. A box plot chart will appear on the worksheet, showing the minimum, first quartile, median, third quartile, and maximum values.

    Customization:

    You can customize the chart’s colors, marker sizes, and the chart’s position by modifying the corresponding parameters in the code. If you want to display additional elements or further customize the look of the box plot, you can do so using Excel’s chart formatting options through VBA.

     

  • Create a bell curve (normal distribution curve) in Excel VBA

    Steps to Create a Bell Curve

    1. Calculate the values for the normal distribution (probability density function).
    2. Create a chart based on these values.
    3. Customize the chart to display a smooth curve.

    VBA Code for Creating a Bell Curve

    Sub CreateBellCurve()
    ' Define parameters for the normal distribution (mean and standard deviation)
    Dim mean As Double
    Dim stdDev As Double
    Dim i As Integer
    Dim x As Double
    Dim y As Double
    Dim numPoints As Integer
    Dim startX As Double
    Dim endX As Double
    Dim rangeX As Range
    Dim rangeY As Range   
    ' Initialize normal distribution parameters
    mean = 0            ' Mean of the normal distribution
    stdDev = 1          ' Standard deviation of the normal distribution
    numPoints = 100     ' Number of data points for the curve
    startX = -5         ' Starting value for the X-axis
    endX = 5            ' Ending value for the X-axis   
    ' Calculate the X and Y values for the bell curve
    For i = 1 To numPoints
        ' Calculate the X value for each point
        x = startX + (endX - startX) * (i - 1) / (numPoints - 1)
        ' Calculate the Y value using the probability density function
        y = (1 / (stdDev * Sqr(2 * WorksheetFunction.Pi()))) * _
            Exp(-((x - mean) ^ 2) / (2 * stdDev ^ 2))       
        ' Place the values into the Excel cells (Column X and Y)
        Cells(i, 1).Value = x
        Cells(i, 2).Value = y
    Next i   
    ' Define the ranges for the chart data
    Set rangeX = Range(Cells(1, 1), Cells(numPoints, 1))
    Set rangeY = Range(Cells(1, 2), Cells(numPoints, 2))   
    ' Create a scatter plot (XY chart)
    Dim chart As Chart
    Set chart = Charts.Add
    With chart
        .ChartType = xlXYScatterSmooth
        .SetSourceData Source:=rangeX
        .SeriesCollection(1).XValues = rangeX
        .SeriesCollection(1).Values = rangeY
        .HasTitle = True
        .ChartTitle.Text = "Bell Curve (Normal Distribution)"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Value"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability Density"
    End With
End Sub

    Explanation of the Code

    1. Define Parameters for the Normal Distribution:
      • mean: The mean of the normal distribution (set to 0 in this example).
      • stdDev: The standard deviation of the normal distribution (set to 1 in this example).
      • numPoints: The number of data points to calculate for the curve.
      • startX and endX: The range for the X-axis of the curve (set from -5 to 5 here).
    2. Calculate the Values:
      • For each point, the x value is calculated as a regular increment between startX and endX.
      • The y value is then calculated using the probability density function (PDF) of the normal distribution:

    y=σ2π​1​exp(−2σ2(x−μ)2​)

    where:

        • μ is the mean (0 here),
        • σ is the standard deviation (1 here).

    3. Create the Chart:

      • The x and y values are placed into Excel columns A and B.
      • A « Smooth XY Scatter » chart is created, which represents the bell curve.
      • Titles for the chart and axes are added for clarity.

    How to Use the Code

    1. Open the VBA Editor:
      • Press Alt + F11 to open the VBA editor in Excel.
    2. Create a New Module:
      • In the VBA editor, go to Insert -> Module.
    3. Paste the Code:
      • Paste the code into the new module.
    4. Run the Macro:
      • Close the VBA editor and return to Excel.
      • Press Alt + F8, select CreateBellCurve, and click Run.

    Result

    The code will generate a smooth bell curve (normal distribution curve) in Excel, where the mean is 0 and the standard deviation is 1. You can adjust the parameters (mean, standard deviation, etc.) to customize the curve as you like.

     

  • 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.

    1. 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
    1. 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 »).
    1. 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 ».
    1. 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).