Creative Blog

Catégorie : Excel VBA Course

  • Creating a combined chart in Excel using VBA

    Creating a combined chart in Excel using VBA involves using different data series in a single chart, combining multiple chart types (e.g., column and line charts). 

    Steps to Create a Combined Chart Using VBA:

    1. Prepare the Data: For this example, let’s assume you have data in the range A1:C6:
      • Column A: Months
      • Column B: Sales
      • Column C: Costs
    2. Create a Combined Chart: The combined chart will display « Sales » as a column chart and « Costs » as a line chart.

    VBA Code:

    Sub CreateCombinedChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As Chart
    ' Reference the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Modify "Sheet1" to the actual sheet name
    ' Create a combined chart (column and line chart)
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    Set chart = chartObj.Chart
    ' Set the data range for the chart
    chart.SetSourceData Source:=ws.Range("A1:C6")
    ' Set the default chart type to clustered column
    chart.ChartType = xlColumnClustered  ' Default chart type: clustered columns
    ' Add a series (e.g., "Sales") as a column chart
    With chart.SeriesCollection.NewSeries
        .Name = "Sales"
        .XValues = ws.Range("A2:A6")
        .Values = ws.Range("B2:B6")
        .ChartType = xlColumnClustered  ' Column chart
    End With
    ' Add a series (e.g., "Costs") as a line chart
    With chart.SeriesCollection.NewSeries
        .Name = "Costs"
        .XValues = ws.Range("A2:A6")
        .Values = ws.Range("C2:C6")
        .ChartType = xlLine  ' Line chart
        .AxisGroup = 2  ' Place this series on the secondary axis
    End With
    ' Add a secondary axis for the "Costs" series
    chart.Axes(xlValue, xlSecondary).CategoryNames = ws.Range("A2:A6")
    chart.HasSecondaryAxis = True
    ' Customize the axes
    With chart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
        .TickLabels.NumberFormat = "#,##0"
    End With
    ' Customize the chart title and legend
    chart.HasTitle = True
    chart.ChartTitle.Text = "Combined Chart for Sales and Costs"
    chart.HasLegend = True
    chart.Legend.Position = xlLegendPositionBottom
End Sub

    Explanation of the Code:

    1. Declaration of Objects:
      • ws: Refers to the worksheet where the data and chart will be created.
      • chartObj: Represents the chart object that will be inserted into the worksheet.
      • chart: Refers to the actual chart being created.
    2. Creating the Chart:
      • Set chartObj = ws.ChartObjects.Add(…): Adds a chart object to the worksheet with the specified dimensions (left, top, width, height).
      • chart.SetSourceData Source:=ws.Range(« A1:C6 »): Sets the data range from which the chart will be created.
    3. Adding the Series:
      • For the first series (« Sales »), the chart type is set to xlColumnClustered (column chart).
      • For the second series (« Costs »), the chart type is set to xlLine (line chart), and the property .AxisGroup = 2 places this series on the secondary axis.
    4. Secondary Axis:
      • chart.HasSecondaryAxis = True: Adds a secondary axis for the « Costs » series, allowing it to have a different scale than the « Sales » series.
    5. Customizing the Axes:
      • The primary axis has major gridlines enabled, and tick labels are formatted to show numbers with commas.
      • The secondary axis is used for the « Costs » series.
    6. Chart Customization:
      • The chart title is set to « Combined Chart for Sales and Costs ».
      • The legend is placed at the bottom of the chart using chart.Legend.Position = xlLegendPositionBottom.

    Expected Result:

    The code will create a combined chart where:

    • The « Sales » series is displayed as a column chart.
    • The « Costs » series is displayed as a line chart.
    • The secondary axis is used for the « Costs » series, allowing a different scale for both series.

    Further Customization:

    • You can adjust colors, chart types, and other settings by modifying the properties of the chart and series.

  • Create a dropdown list (ComboBox) in a UserForm in Excel VBA

    Steps to create a dropdown list in a UserForm in Excel VBA

    1. Create the UserForm:
      • Open the VBA editor (press Alt + F11 in Excel).
      • Click on Insert > UserForm to create a new form.
    2. Add a ComboBox:
      • From the toolbox that appears, choose the ComboBox control (dropdown list) and click on the form to add it.
    3. Add code to populate the dropdown list:
      • You can populate the ComboBox in various ways: either manually entering the values, or pulling them from a range of cells in Excel.

    Example Detailed Code

    Let’s assume we want to create a UserForm with a ComboBox that contains options coming from a range of cells in an Excel worksheet. Here’s a complete example of code:

    Step 1: Create the UserForm

    In the VBA editor, create a new UserForm and add a ComboBox and a CommandButton to close the form.

    Step 2: Code to populate the dropdown list

    1. Code for the UserForm:

    Open the code window of the UserForm and add the following code:

    Private Sub UserForm_Initialize()
    ' Fill the ComboBox with data from an Excel range
    Dim rng As Range
    Dim cell As Range   
    ' Define the range of data (e.g., A1:A10)
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")   
    ' Clear previous items in the ComboBox
    ComboBox1.Clear   
    ' Loop through each cell in the range and add its value to the ComboBox
    For Each cell In rng
        If cell.Value <> "" Then
            ComboBox1.AddItem cell.Value
        End If
    Next cell
End Sub

    Explanation of the Code:

    • UserForm_Initialize: This procedure automatically runs when the UserForm is opened.
    • Define the range rng: We define the range of cells from which the values for the dropdown will be taken. In this case, the range is A1:A10 from the « Sheet1 ».
    • ComboBox1.Clear: Before adding new items, we clear any old items in the ComboBox to avoid duplicates.
    • For Each loop: This loop goes through each cell in the defined range, and if the cell is not empty, the value of the cell is added to the dropdown list using ComboBox1.AddItem cell.Value.

    Add a button to close the UserForm:

    Add a button on the UserForm with the following code to close the form when the user clicks it:

    Private Sub CommandButton1_Click()
    ' Close the UserForm
    Unload Me
End Sub

    Step 3: Code to open the UserForm

    Now, you need to add a small piece of code to open the UserForm. You can place this code in a standard module.

    Open the UserForm:

    Go to a standard module (or create one) and add this code:

    Sub OpenUserForm()
    UserForm1.Show
End Sub

    This opens the UserForm with the ComboBox populated as soon as you run the OpenUserForm macro.

    Step 4: Running the code

    • Close the VBA editor.
    • In Excel, run the macro OpenUserForm (press Alt + F8, select OpenUserForm, and click Run).
    • You’ll see that the UserForm opens with the dropdown list filled with the values from the range A1:A10 in the « Sheet1 ».

    Additional Option: Adding values directly in the code

    If you want to manually add values to the ComboBox instead of pulling them from a range of cells, you can use this code in UserForm_Initialize:

    Private Sub UserForm_Initialize()
    ' Add items manually to the ComboBox
    ComboBox1.AddItem "Option 1"
    ComboBox1.AddItem "Option 2"
    ComboBox1.AddItem "Option 3"
    ComboBox1.AddItem "Option 4"
End Sub

    Summary

    • UserForm_Initialize: This is where you add items to the ComboBox.
    • ComboBox1.Clear: Clears existing items before adding new ones.
    • ComboBox1.AddItem: Adds an item to the dropdown list.
    • CommandButton1_Click: Code to close the UserForm when the user clicks the button.

  • Creating checkboxes in a UserForm using Excel VBA.

    Steps to create checkboxes in a UserForm in Excel VBA

    1. Open the VBA Editor
      • In Excel, press Alt + F11 to open the VBA editor.
    2. Add a UserForm
      • Click on Insert > UserForm to add a new form.
    3. Add Checkboxes
      • Click on the « Checkbox » control in the toolbox of the UserForm and place it on the form. You can add multiple checkboxes according to your needs.
    4. Write the VBA Code
      • After adding checkboxes to your form, you will write the code to handle the events associated with them, such as checking which checkboxes are selected and performing actions based on the user’s choices.

    Detailed Code to Create a UserForm with Checkboxes

    Let’s assume you have three options represented by checkboxes: « Option 1 », « Option 2 », and « Option 3 ». The goal is to capture which options are selected and display them in an Excel cell.

    1.1 Creating Checkboxes Dynamically in the Code

    We will dynamically create a set of checkboxes and add them to the form using VBA code.

    Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim chkBox As MSForms.CheckBox
    Dim options() As String
    options = Array("Option 1", "Option 2", "Option 3") ' Array with the option labels   
    ' Loop to create checkboxes dynamically
    For i = 0 To UBound(options)
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1") ' Create a checkbox
        chkBox.Caption = options(i) ' Assign text to the checkbox
        chkBox.Left = 10 ' Horizontal position
        chkBox.Top = 10 + (i * 25) ' Vertical position (25px spacing between checkboxes)
        chkBox.Name = "chkOption" & i ' Name each checkbox (chkOption0, chkOption1, etc.)
    Next i
End Sub

    Explanation of the Code:

    • UserForm_Initialize(): This procedure runs when the form is opened. It initializes the checkboxes.
    • Me.Controls.Add(« Forms.CheckBox.1 »): This line creates a checkbox dynamically.
    • chkBox.Caption = options(i): The text of the checkbox is set from the options array.
    • chkBox.Left and chkBox.Top: These properties define the position of each checkbox on the form.
    • chkBox.Name = « chkOption » & i: The name of each checkbox is dynamic and based on the index i.

    1.2 Retrieve Selected Checkboxes

    When the user clicks a submit button, we want to retrieve which checkboxes were selected and display them in an Excel cell.

    Here is the code for the submit button:

    Private Sub btnSubmit_Click()
    Dim i As Integer
    Dim selectedOptions As String
    selectedOptions = "Selected options: "   
    ' Loop through the checkboxes
    For i = 0 To 2 ' 3 options (index 0, 1, 2)
        If Me.Controls("chkOption" & i).Value = True Then
            selectedOptions = selectedOptions & Me.Controls("chkOption" & i).Caption & ", "
        End If
    Next i   
    ' Remove the last comma and space
    If Len(selectedOptions) > 0 Then
        selectedOptions = Left(selectedOptions, Len(selectedOptions) - 2)
    End If   
    ' Display the selected options in an Excel cell
    Sheets("Sheet1").Range("A1").Value = selectedOptions
    MsgBox selectedOptions ' Show a message with the selected options
End Sub

    Explanation of the Code:

    • If Me.Controls(« chkOption » & i).Value = True Then: This condition checks if the checkbox is selected (True means checked, False means unchecked).
    • selectedOptions = selectedOptions & Me.Controls(« chkOption » & i).Caption & « , « : If the checkbox is checked, its caption (label) is appended to the selectedOptions string.
    • Sheets(« Sheet1 »).Range(« A1 »).Value = selectedOptions: The selected options are displayed in cell A1 on the worksheet.
    • MsgBox selectedOptions: A message box shows the selected options.

    Add a Close Button to Close the UserForm

    You can also add a button to the form to close it once the user is done.

    Private Sub btnClose_Click()
    Unload Me ' Close the UserForm
End Sub

    Add a Button to Launch the UserForm

    In a standard module, you can add code to open the UserForm. For example, you can create a button on the Excel worksheet to show the form:

    Sub ShowUserForm()
    UserForm1.Show
End Sub

    Summary:

    • The code creates a UserForm with dynamically generated checkboxes based on an array of options.
    • When the user submits their choices, the code checks which checkboxes are selected and displays the results in an Excel cell.
    • You can add a close button to cleanly close the UserForm when done.

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