Catégorie : Excel VBA Course

Steps to Create the Code:

1. Open the VBA Editor:
   • In Excel, press Alt + F11 to open the VBA editor.
2. Add a Module:
   • In the VBA editor, go to the Insert menu and choose Module to add a new module to your project.
3. Write the VBA Code:

Here is the complete VBA code to calculate the number of days between two dates:

VBA Code:

Sub CalculateDaysBetweenDates()
    ' Declare variables
    Dim date1 As Date
    Dim date2 As Date
    Dim days As Long
    Dim message As String
    ' Ask the user to enter the dates via an InputBox
    On Error GoTo DateError
    date1 = CDate(InputBox("Enter the first date (format: dd/mm/yyyy):"))
    date2 = CDate(InputBox("Enter the second date (format: dd/mm/yyyy):"))   
    ' Calculate the difference in days
    days = DateDiff("d", date1, date2)  
    ' Display the result in a message box
    message = "The number of days between " & date1 & " and " & date2 & " is: " & days
    MsgBox message, vbInformation, "Result"
    Exit Sub
DateError:
    MsgBox "Error with the date format. Please enter valid dates in dd/mm/yyyy format.", vbCritical, "Error"
End Sub

Explanation of the Code:

1. Declaring Variables:
   • date1 and date2 store the two dates entered by the user.
   • days is a Long type variable that will store the number of days between the two dates.
   • message is a String variable used to prepare the message that will be displayed to the user.
2. Getting Dates from User:
   • The user is prompted to enter the two dates via InputBox dialogs. Each date is converted to a Date type using the CDate function.
3. Calculating the Difference in Days:
   • The DateDiff function is used to calculate the difference between date1 and date2. The « d » parameter specifies that we want the difference in days.
   • The result is stored in the days variable.
4. Displaying the Result:
   • The result is displayed in a message box (MsgBox). The message shows both entered dates and the number of days between them.
5. Error Handling:
   • If the user enters an invalid date format, an error is triggered, and the user is shown an error message. This is handled with the On Error GoTo statement and the DateError label.

Example of Usage:

1. When you run the macro, a dialog box will appear asking you to enter the first date.
2. A second dialog box will appear for you to enter the second date.
3. Once the dates are entered, the macro will calculate the number of days between them and display a message with the result.

Possible Improvements:

• Automatic Date Entry: You can modify the code to fetch the dates directly from specific cells in the worksheet.
• Advanced Error Handling: You can add more checks to ensure the dates are entered correctly before performing the calculation.

Example of Code with Dates from Cells:

Sub CalculateDaysBetweenDatesFromCells()
    ' Declare variables
    Dim date1 As Date
    Dim date2 As Date
    Dim days As Long
    Dim message As String
    ' Get the dates from cells A1 and B1
    date1 = Cells(1, 1).Value  ' Cell A1 for the first date
    date2 = Cells(1, 2).Value  ' Cell B1 for the second date   
    ' Check if the cells contain valid dates
    If IsDate(date1) And IsDate(date2) Then
        ' Calculate the difference in days
        days = DateDiff("d", date1, date2)      
        ' Display the result in a message box
        message = "The number of days between " & date1 & " and " & date2 & " is: " & days
        MsgBox message, vbInformation, "Result"
    Else
        MsgBox "Please enter valid dates in cells A1 and B1.", vbCritical, "Error"
    End If
End Sub

In this example, the dates are taken from cells A1 and B1. If either of the cells does not contain a valid date, an error message will be shown.

Summary:

• The first code uses InputBox to allow the user to input the dates manually.
• The second code retrieves the dates from cells A1 and B1 on the worksheet.
• Both versions calculate the number of days between the two dates and display the result in a message box.

Covariance Formula

Covariance measures the degree to which two variables change together. The formula for calculating the covariance between two datasets X and Y is:

Cov(X,Y)=∑(Xi−Xˉ)(Yi−Yˉ)/n

Where:

• n is the number of observations,
• Xi, Yi​ are the values of datasets X and Y,
• Xˉ,  Yˉ are the means of X and Y, respectively.

VBA Code to Calculate Covariance

Here’s a detailed VBA code to calculate the covariance between two datasets in Excel:

Step 1: Open the VBA Editor

1. Press Alt + F11 to open the VBA editor.
2. In the editor, click on Insert and then Module to insert a new module.

Step 2: Code in VBA

Sub CalculateCovariance()
    ' Declare variables
    Dim rangeX As Range
    Dim rangeY As Range
    Dim n As Integer
    Dim sumXY As Double
    Dim sumX As Double
    Dim sumY As Double
    Dim meanX As Double
    Dim meanY As Double
    Dim covariance As Double
    Dim i As Integer
    ' Ask the user to select the data ranges
    On Error Resume Next
    Set rangeX = Application.InputBox("Select the range of X data", Type:=8)
    Set rangeY = Application.InputBox("Select the range of Y data", Type:=8)
    On Error GoTo 0
    ' Check if the ranges have the same size
    If rangeX.Count <> rangeY.Count Then
        MsgBox "The ranges of X and Y must have the same number of values.", vbCritical
        Exit Sub
    End If
    ' Calculate the size of the ranges (number of observations)
    n = rangeX.Count
    ' Calculate the necessary sums
    sumX = 0
    sumY = 0
    sumXY = 0 
    For i = 1 To n
        sumX = sumX + rangeX.Cells(i, 1).Value
        sumY = sumY + rangeY.Cells(i, 1).Value
        sumXY = sumXY + rangeX.Cells(i, 1).Value * rangeY.Cells(i, 1).Value
    Next i
    ' Calculate the means
    meanX = sumX / n
    meanY = sumY / n
    ' Calculate the covariance
    covariance = (sumXY - n * meanX * meanY) / (n - 1)
    ' Display the result
    MsgBox "The covariance between X and Y is: " & covariance, vbInformation
End Sub

Explanation of the Code

1. Declare Variables:
   • rangeX and rangeY are the ranges of cells containing the datasets XXX and YYY respectively.
   • n represents the number of observations (i.e., the number of values in each dataset).
   • sumX, sumY, and sumXY are variables for storing the necessary sums to compute covariance.
   • meanX and meanY are the means of the XXX and YYY datasets.
   • covariance stores the final covariance value.
2. Prompt User to Select Data Ranges:
   • Application.InputBox allows the user to select the ranges of data for XXX and YYY. The Type:=8 parameter ensures that the user selects a range of cells.
3. Check if the Ranges Have the Same Size:
   • If the two ranges have different sizes (i.e., the number of observations in XXX and YYY are not equal), an error message appears, and the code exits.
4. Calculate the Sums:
   • A For loop iterates over each element in the data ranges to calculate the sum of XXX, the sum of YYY, and the sum of the products of corresponding elements Xi×YiX_i \times Y_iXi​×Yi​.
5. Calculate the Means:
   • The means of XXX and YYY are calculated by dividing the sum of each dataset by the number of observations nnn.
6. Calculate the Covariance:
   • The covariance is calculated using the formula provided at the beginning.
7. Display the Result:
   • The covariance value is displayed in a message box.

How to Use the Code:

1. After copying the code into a new VBA module (as described above), you can run the macro by pressing F5 in the VBA editor or by assigning it to a button in Excel.
2. When you run the macro, it will prompt you to select the data ranges for XXX and YYY.
3. Once you have selected the ranges, the code will calculate the covariance and display the result in a message box.

 

Steps to calculate the correlation coefficient:

The correlation coefficient (often denoted as r) measures the strength and direction of the linear relationship between two variables. It ranges from -1 to 1:

• 1 means a perfect positive correlation.
• -1 means a perfect negative correlation.
• 0 means no linear correlation.

The correlation coefficient can be calculated using Excel’s CORREL function, but in VBA, we can also calculate it manually using the following formula:

r=(∑(Xi−Xˉ)(Yi−Yˉ))/sqrt(∑(Xi−Xˉ)2∑(Yi−Yˉ)2)

Where:

• x and y are the datasets.
• n is the number of data pairs.

VBA Code to calculate the correlation coefficient:

Sub CalculateCorrelation()
    ' Declare variables
    Dim RangeX As Range
    Dim RangeY As Range
    Dim i As Long
    Dim n As Long
    Dim sumX As Double
    Dim sumY As Double
    Dim sumXY As Double
    Dim sumX2 As Double
    Dim sumY2 As Double
    Dim correlation As Double
    ' Set the data ranges for X and Y
    Set RangeX = Range("A1:A10")  ' Range of X values
    Set RangeY = Range("B1:B10")  ' Range of Y values
    ' Check if the ranges have the same size
    If RangeX.Cells.Count <> RangeY.Cells.Count Then
        MsgBox "The data ranges must have the same number of cells."
        Exit Sub
    End If   
    ' Initialize the sums
    sumX = 0
    sumY = 0
    sumXY = 0
    sumX2 = 0
    sumY2 = 0
    n = RangeX.Cells.Count
    ' Calculate the necessary sums
    For i = 1 To n
        sumX = sumX + RangeX.Cells(i).Value
        sumY = sumY + RangeY.Cells(i).Value
        sumXY = sumXY + (RangeX.Cells(i).Value * RangeY.Cells(i).Value)
        sumX2 = sumX2 + (RangeX.Cells(i).Value ^ 2)
        sumY2 = sumY2 + (RangeY.Cells(i).Value ^ 2)
    Next i
    ' Calculate the correlation coefficient r
    correlation = (n * sumXY - sumX * sumY) / _
                 Sqr((n * sumX2 - sumX ^ 2) * (n * sumY2 - sumY ^ 2))
    ' Display the result in a message box
    MsgBox "The correlation coefficient is: " & correlation
End Sub

Explanation of the code:

1. Variable Declaration:
   • RangeX and RangeY represent the ranges of data in columns X and Y, respectively.
   • Variables sumX, sumY, sumXY, sumX2, and sumY2 are used to store the sums needed to calculate the correlation.
   • n is the number of data pairs.
2. Setting the data ranges:
   • The code takes the data from the ranges in columns A and B (you can adjust these ranges to suit your needs).
3. Checking the size of the ranges:
   • The code checks that both data ranges (X and Y) have the same number of cells. If they don’t, it shows a message box and stops.
4. Calculating the necessary sums:
   • A For loop goes through each pair of data points in the X and Y ranges and calculates the sums of xxx, yyy, x⋅yx \cdot yx⋅y, x2x^2×2, and y2y^2y2.
5. Calculating the correlation coefficient:
   • Using the formula for the correlation coefficient, the code computes the result.
6. Displaying the result:
   • The correlation coefficient is displayed in a message box.

How to use the code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the editor, click on Insert and then Module.
3. Paste the code into the module.
4. Close the VBA editor.
5. Go back to Excel and press Alt + F8 to run the CalculateCorrelation macro.

The result of the correlation calculation will be displayed in a message box. You can adjust the ranges Range(« A1:A10 ») and Range(« B1:B10 ») to match your actual data.

 

Compound Interest Formula:

The formula for compound interest is:

A= P * (1 + r / n) ^ (n * t)

Where:

• A= Final amount (principal + interest)
• P= Principal (initial investment)
• r = Annual interest rate (in decimal form, for example, 5% is 0.05)
• n = Number of times the interest is compounded per year (for monthly compounding, n=12n = 12n=12)
• t = Number of years the money is invested.

VBA Code to Calculate Compound Interest:

1. Open the VBA editor:
   • Open Excel.
   • Press Alt + F11 to open the VBA editor.
   • In the editor, click Insert -> Module to add a new module.
2. VBA Code:

Sub CalculateCompoundInterest()
    ' Declare variables
    Dim P As Double  ' Principal (initial investment)
    Dim r As Double  ' Annual interest rate
    Dim n As Integer ' Number of compounding periods per year
    Dim t As Double  ' Duration of investment in years
    Dim A As Double  ' Final amount (principal + interest)   
    ' Get values from Excel cells
    P = Range("B1").Value  ' Principal (initial investment) from cell B1
    r = Range("B2").Value  ' Annual interest rate from cell B2
    n = Range("B3").Value  ' Number of compounding periods from cell B3
    t = Range("B4").Value  ' Duration in years from cell B4   
    ' Calculate compound interest
    A = P * (1 + r / n) ^ (n * t)   
    ' Display the result in cell B5
    Range("B5").Value = A   
    ' Optional: Display the result in a message box
    MsgBox "The final amount after " & t & " years is " & Format(A, "0.00") & " €.", vbInformation, "Result"  
End Sub

Explanation of the Code:

1. Variable Declaration:
   • P (Principal): The initial investment amount.
   • r (Interest Rate): The annual interest rate in decimal form (e.g., 5% would be entered as 0.05).
   • n (Number of Periods): The number of times the interest is compounded per year. For monthly compounding, use n = 12.
   • t (Time): The number of years the money is invested.
   • A (Final Amount): The total amount after applying compound interest.
2. Getting Values from Excel Cells:
   • The code retrieves the values from cells B1, B2, B3, and B4 where the user inputs the data for the principal, interest rate, number of periods, and duration.
3. Calculating Compound Interest:
   • The compound interest formula is applied here:

A = P * (1 + r / n) ^ (n * t)

This calculates the final amount after applying compound interest.

1. Displaying the Result:
   • The result (final amount) is displayed in cell B5 of the Excel sheet.
   • Additionally, a message box (MsgBox) shows the final amount in a pop-up message with the formatted result.

Example Usage in Excel:

Suppose you have the following values in your cells:

• B1 (Principal): 1000 (the initial investment of 1000 €)
• B2 (Interest Rate): 0.05 (5% annual interest rate)
• B3 (Number of Periods): 12 (interest compounded monthly)
• B4 (Time): 5 (investment duration in years)

When you run the macro, the calculation will be done, and the final amount (principal + interest) will appear in cell B5.

How to Run the Macro:

1. After pasting the code into the VBA editor, return to your Excel sheet.
2. Press Alt + F8 to open the « Macro » dialog.
3. Select CalculateCompoundInterest and click Run.

The final amount will be calculated and displayed in cell B5, and a message box will also show the result.

Customization:

You can adjust the cells (e.g., change B1, B2, B3, B4) to fit where you want the user to input the values in your Excel sheet.

The formula for BMI is:

BMI=Weight (kg)/(Height (m)*Height (m))

Steps to Create the VBA Code

1. Open the VBA Editor:
   • Open your Excel file.
   • Press Alt + F11 to open the VBA editor.
   • In the editor, click Insert in the menu, then select Module to create a new module.
2. Add the VBA Code:

In the module, paste the following code:

Sub CalculateBMI()
    ' Declare variables
    Dim weight As Double
    Dim height As Double
    Dim bmi As Double
    Dim message As String   
    ' Ask the user to enter the weight (in kg)
    weight = InputBox("Please enter your weight in kilograms:")   
    ' Check if the weight is a positive number
    If weight <= 0 Then
        MsgBox "Weight must be a positive number.", vbCritical
        Exit Sub
    End If
    ' Ask the user to enter the height (in meters)
    height = InputBox("Please enter your height in meters:")   
    ' Check if the height is a positive number
    If height <= 0 Then
        MsgBox "Height must be a positive number.", vbCritical
        Exit Sub
    End If   
    ' Calculate the BMI (Weight / (Height^2))
    bmi = weight / (height ^ 2)   
    ' Determine the BMI category
    If bmi < 16 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Severe Thinness"
    ElseIf bmi >= 16 And bmi < 16.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Moderate Thinness"
    ElseIf bmi >= 17 And bmi < 18.4 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Mild Thinness"
    ElseIf bmi >= 18.5 And bmi < 24.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Normal Weight"
    ElseIf bmi >= 25 And bmi < 29.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Overweight"
    ElseIf bmi >= 30 And bmi < 34.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Obesity (Moderate)"
    ElseIf bmi >= 35 And bmi < 39.9 Then
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Obesity (Severe)"
    Else
        message = "BMI: " & Round(bmi, 2) & vbCrLf & "Category: Morbid Obesity"
    End If   
    ' Display the result in a message box
    MsgBox message, vbInformation, "BMI Result"
End Sub

Explanation of the Code

• Declarations:
  • weight: Stores the user’s weight in kilograms.
  • height: Stores the user’s height in meters.
  • bmi: Holds the calculated BMI value.
  • message: Contains the message that will be displayed with the BMI result and category.
• User Input:
  • The InputBox function is used to ask the user for their weight and height.
  • If the user enters an invalid value (like a negative number or zero), an error message appears, and the code stops execution.
• BMI Calculation:
  • The BMI is calculated using the formula weight / (height ^ 2).
• BMI Categories:
  • Based on the BMI value, the program categorizes it into different groups, following standard BMI classifications:
    • Less than 16: Severe Thinness
    • 16 to 16.9: Moderate Thinness
    • 17 to 18.4: Mild Thinness
    • 18.5 to 24.9: Normal Weight
    • 25 to 29.9: Overweight
    • 30 to 34.9: Obesity (Moderate)
    • 35 to 39.9: Obesity (Severe)
    • 40 and above: Morbid Obesity
• Displaying the Result:
  • The result is shown in a message box, including the calculated BMI and the corresponding category.

How to Use the Code

1. Once you’ve added the code to the VBA editor, you can run the macro by pressing F5 within the editor.
2. A prompt will ask you to enter your weight and height. After entering the values, the result will appear in a pop-up message.

Notes

This code works in Excel with VBA enabled. You can further adapt the code for more complex use cases, such as working with data in an Excel worksheet. However, this example provides a straightforward approach for calculating BMI with user inputs.

Steps:

1. Set up the Excel sheet with a list of birthdates.
2. Write the VBA code to calculate each person’s age from their birthdate.
3. Calculate the average age.
4. Display the result.

Example VBA Code:

1. Excel Sheet Structure

• Let’s assume birthdates are in column A, from A2 to A10 (you can adjust this range based on your actual data).
• You want the average age to be displayed in cell B1.

2. The VBA Code

Here’s a sample VBA code to perform this task:

Sub CalculateAverageAge()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim totalAge As Double
    Dim numberOfPeople As Long
    Dim age As Integer
    Dim birthdate As Date
    Dim averageAge As Double   
    ' Reference to the active sheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if necessary   
    ' Find the last row of data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Initialize variables
    totalAge = 0
    numberOfPeople = 0   
    ' Loop through the birthdates (column A)
    For i = 2 To lastRow ' Start at row 2 to skip headers
        ' Check if the cell contains a valid date
        If IsDate(ws.Cells(i, 1).Value) Then
            ' Get the birthdate
            birthdate = ws.Cells(i, 1).Value
            ' Calculate age
            age = DateDiff("yyyy", birthdate, Date)
            ' Adjust if the birthday hasn't occurred yet this year
            If Month(birthdate) > Month(Date) Or (Month(birthdate) = Month(Date) And Day(birthdate) > Day(Date)) Then
                age = age - 1
            End If
            ' Add the age to the total
            totalAge = totalAge + age
            ' Increment the number of people
            numberOfPeople = numberOfPeople + 1
        End If
    Next i   
    ' Calculate the average age
    If numberOfPeople > 0 Then
        averageAge = totalAge / numberOfPeople
        ' Display the average age in cell B1
        ws.Cells(1, 2).Value = "Average Age: " & averageAge
    Else
        ' If no valid data is found
        ws.Cells(1, 2).Value = "No valid data"
    End If
End Sub

Explanation of the Code:

1. Reference to the active sheet: The code starts by setting a reference to the active worksheet (in this case, « Sheet1 »). If your sheet has a different name, update this line:

Set ws = ThisWorkbook.Sheets("Sheet1")

2. Finding the last row: The lastRow variable is used to find the last row of data in column A (where the birthdates are). This way, the code adjusts automatically if you add or remove data.

3. Calculating the age:

• • The code loops through each cell in column A (from row 2 to the last row of data).
  • For each valid birthdate, it calculates the age using the DateDiff function, which returns the difference in years between the birthdate and the current date.
  • If the birthday hasn’t occurred yet this year, the code subtracts 1 from the age to adjust it.

4. Calculating the average age:

• • The sum of ages is stored in totalAge, and the number of people is counted in numberOfPeople.
  • After the loop, if any valid birthdates were found, the average age is calculated by dividing the total age by the number of people.

5. Displaying the result:

• • If there are valid dates, the average age is displayed in cell B1.
  • If no valid data is found, the message « No valid data » is shown in cell B1.

How to Run This Code:

1. Open the VBA editor:
   • In Excel, press Alt + F11 to open the VBA editor.
2. Insert a Module:
   • Go to Insert > Module to add a new module.
3. Copy and Paste the Code:
   • Paste the provided VBA code into the new module.
4. Run the Code:
   • Press F5 or go to Run > Run Sub/UserForm to execute the macro and calculate the average age.

After running the code, the average age will be displayed in cell B1 of your worksheet. If there’s no valid data in column A, the cell will display « No valid data ».

This code takes into account years, months, and days, ensuring accurate results even considering whether the birthday has already passed this year or not.

Step 1: Access the VBA Editor

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, go to Insert > Module to create a new module.
3. Copy and paste the following code into the new module.

VBA Code: Calculate Age

Function CalculateAge(BirthDate As Date) As String
    ' This function calculates age based on the birthdate   
    Dim CurrentDate As Date
    Dim AgeYears As Integer
    Dim AgeMonths As Integer
    Dim AgeDays As Integer
    Dim BirthdayThisYear As Date  
    ' Get the current date
    CurrentDate = Date   
    ' Calculate the birthday for this year
    BirthdayThisYear = DateSerial(Year(CurrentDate), Month(BirthDate), Day(BirthDate))   
    ' If the birthday hasn't passed yet this year, subtract 1 from the age
    If CurrentDate < BirthdayThisYear Then
        AgeYears = Year(CurrentDate) - Year(BirthDate) - 1
    Else
        AgeYears = Year(CurrentDate) - Year(BirthDate)
    End If   
    ' Calculate the age in months
    If Month(CurrentDate) < Month(BirthDate) Or (Month(CurrentDate) = Month(BirthDate) And Day(CurrentDate) < Day(BirthDate)) Then
        AgeMonths = Month(CurrentDate) - Month(BirthDate) + 12
    Else
        AgeMonths = Month(CurrentDate) - Month(BirthDate)
    End If   
    ' Calculate the remaining days
    If Day(CurrentDate) < Day(BirthDate) Then
        ' If the current day is smaller than the birth day, calculate the remaining days of the previous month
        AgeDays = Day(CurrentDate) + (Day(DateSerial(Year(CurrentDate), Month(CurrentDate), 0)) - Day(BirthDate))
    Else
        AgeDays = Day(CurrentDate) - Day(BirthDate)
    End If  
    ' Return the age as a string in the format "x years, y months, z days"
    CalculateAge = AgeYears & " years, " & AgeMonths & " months, " & AgeDays & " days"
End Function

Explanation of the Code

1. Variable Declaration:
   • BirthDate: The person’s birthdate, passed as an argument to the function.
   • CurrentDate: The current date.
   • AgeYears, AgeMonths, AgeDays: These variables will hold the person’s age in years, months, and days, respectively.
   • BirthdayThisYear: The calculated birthday for the current year.
2. Age Calculation (Years):
   • If the birthday hasn’t passed this year yet, subtract 1 year from the calculated age.
3. Age Calculation (Months):
   • If the current month is earlier than the birth month or the current month is the same as the birth month but the birthday hasn’t occurred yet, adjust the month difference by adding 12 months.
4. Remaining Days Calculation:
   • If the current day is less than the birth day in the month, calculate how many days are left in the previous month.
5. Returning the Result:
   • The function returns the age as a text string in the format: « x years, y months, z days ».

Step 2: Using the Function in Excel

1. Go back to your Excel sheet.
2. Use the function like a normal Excel function. For example, in a cell, you would type:

=CalculateAge(A1)

Where A1 is the cell containing the birthdate. Make sure the cell contains a valid date format.

Example

If the birthdate is March 25, 1990, and today’s date is November 28, 2024, the function will return:

34 years, 8 months, 3 days

Notes:

• If you prefer to show the age in a different format, such as just in years, you can modify the code by removing or adjusting the portion that calculates months and days.
• This function accounts for all date-related nuances (including leap years) and will give an accurate result, even if the birthday is yet to come this year

 

What is Adjusted R-squared?

The Adjusted R-squared (R² adjusted) is a statistical measure that provides a more accurate representation of the goodness of fit for a regression model. Unlike the traditional R-squared (R²), which can be misleading when you add more predictors to the model, the adjusted R² adjusts for the number of predictors and penalizes for overfitting.

Adjusted R-squared Formula:

Adjusted R-squared ) = 1 – ((1 – R-squared) * ((n – 1) / (n – k – 1)))

Where:

• R-squared is the coefficient of determination (regular R-squared),
• n is the number of data points (observations),
• k is the number of independent variables (predictors).

Steps to Calculate Adjusted R-squared:

1. Calculate R-squared: This is computed from the regression model between the dependent variable and the independent variables.
2. Obtain nnn and kkk: nnn is the number of observations, and kkk is the number of predictors.
3. Apply the Adjusted R-squared formula.

VBA Code Example:

Sub CalculateAdjustedRSquared()
    Dim ws As Worksheet
    Dim rngY As Range, rngX As Range
    Dim n As Long, k As Long
    Dim coeff As Double
    Dim residualSum As Double
    Dim totalSum As Double
    Dim SSR As Double, SST As Double, R2 As Double
    Dim R2Adjusted As Double
    ' Define worksheet and data ranges
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    Set rngY = ws.Range("B2:B101") ' Range for dependent variable Y
    Set rngX = ws.Range("A2:A101") ' Range for independent variable X
    ' Number of observations (n) and number of predictors (k)
    n = rngY.Rows.Count
    k = 1 ' For this example, we assume a simple linear regression (1 predictor X)
    ' Perform linear regression to get the coefficients
    With Application.WorksheetFunction
        coeff = .LinEst(rngY, rngX)(1, 1) ' Coefficient for the regression line
    End With
    ' Calculate the total sum of squares (SST)
    totalSum = 0
    For i = 1 To n
        totalSum = totalSum + (rngY.Cells(i, 1).Value - Application.WorksheetFunction.Average(rngY)) ^ 2
    Next i
    ' Calculate the sum of squared residuals (SSR)
    residualSum = 0
    For i = 1 To n
        residualSum = residualSum + (rngY.Cells(i, 1).Value - coeff * rngX.Cells(i, 1).Value) ^ 2
    Next i
    ' Calculate R-squared (R2)
    R2 = 1 - (residualSum / totalSum)
    ' Calculate Adjusted R-squared (R2 adjusted)
    R2 Adjusted = 1 - ((1 - R2) * (n - 1)) / (n - k - 1)
    ' Display the Adjusted R-squared result
    MsgBox "The Adjusted R-squared is: " & R2Adjusted
End Sub

Explanation of the Code:

1. Variable Declaration:
   • ws: The worksheet object that contains the data.
   • rngY and rngX: Ranges for the dependent variable YYY and the independent variable XXX.
   • n: The number of observations (rows of data).
   • k: The number of independent variables (predictors). For a simple linear regression, this is 1.
   • R2: The R-squared value.
   • R2Adjusted: The adjusted R-squared value.
2. Setting the Worksheet and Data Ranges: The code assumes your data is in « Sheet1 » with the dependent variable YYY in column B and the independent variable XXX in column A, from rows 2 to 101.
3. Linear Regression Calculation:
   • The LinEst function in Excel calculates the slope (regression coefficient) of the linear model. In this case, it’s used for simple linear regression, so it’s accessing the first coefficient in the result.
4. Sum of Squares Calculations:
   • SST (Total Sum of Squares): Measures the total variation in the dependent variable.
   • SSR (Sum of Squared Residuals): Measures the variation not explained by the regression model.
   • These are calculated manually by iterating through the data points.
5. R-squared and Adjusted R-squared:
   • R-squared is calculated as 1−SSRSST1 – \frac{SSR}{SST}1−SSTSSR​.
   • Adjusted R-squared is then computed using the formula, which adjusts for the number of predictors kkk and the sample size nnn.
6. Displaying the Result: The adjusted R-squared value is shown in a message box.

How to Use:

• Place your data in columns A and B in the Excel sheet (adjust ranges if your data set is larger or smaller).
• Run the macro by pressing Alt + F8 and selecting CalculateAdjustedRSquared.
• The result will be shown in a message box.

Customization:

• Multiple predictors (Multiple Linear Regression): If you have more than one predictor (e.g., X1,X2,…,XkX1, X2, \ldots, XkX1,X2,…,Xk), adjust the range of rngX to include the additional columns, like Set rngX = ws.Range(« A2:C101 ») for 3 predictors.
• More Observations: Adjust the ranges (B2:B101 and A2:A101) to include more data points if necessary.

The following example will allow you to refresh the data connections every time a specific sheet is opened or even schedule it to run at regular intervals. The core idea is to use the Workbook.Connections object to access all data connections and refresh them.

Objective of the VBA Code:

1. Automatically update all data connections in the workbook.
2. Ensure all connections are refreshed before working with the data.
3. Use a workbook open event or a button to trigger the refresh.

VBA Code to Refresh Data Connections

1. Refresh Connections when the Workbook is Opened (Using Workbook_Open Event)

If you want to update data connections automatically every time the workbook is opened, you should add the following code inside the ThisWorkbook module.

Steps:

1. Open the VBA editor by pressing Alt + F11.
2. In the « VBAProject » panel, find « ThisWorkbook » and double-click it to open the code window.
3. Copy and paste the following code into the code window.

Private Sub Workbook_Open()
    ' Call the function to refresh all data connections
    UpdateAllConnections
End Sub

Sub UpdateAllConnections()
    Dim conn As Object
    ' Loop through all data connections in the workbook
    For Each conn In ThisWorkbook.Connections
        ' Try to refresh each connection
        On Error Resume Next ' Ignore error if connection fails
        conn.Refresh
        On Error GoTo 0 ' Reset error handling
    Next conn
    MsgBox "All connections have been updated!", vbInformation
End Sub

Code Explanation:

• Workbook_Open: This is an event that is automatically executed when the workbook is opened. It calls the UpdateAllConnections function that refreshes all connections.
• UpdateAllConnections: This function loops through all data connections in the workbook (ThisWorkbook.Connections) and attempts to refresh them using conn.Refresh.
• On Error Resume Next: This line ensures that any errors (like if a connection cannot be refreshed) are ignored.
• MsgBox: A message box is displayed once all connections have been refreshed.

2. Refresh Connections Using a Button

If you prefer to have a button on a worksheet to trigger the refresh of connections, follow these steps:

Steps:

1. In your Excel sheet, insert a form button (via the Developer tab > Insert > Button).
2. Link the button to a macro that will refresh the connections.
3. Create a standard module to add the following code.

VBA Code:

Sub RefreshConnections()
    Dim conn As Object
    ' Loop through to refresh all connections
    For Each conn In ThisWorkbook.Connections
        ' Try to refresh each connection
        On Error Resume Next
        conn.Refresh
        On Error GoTo 0
    Next conn
    MsgBox "All connections have been updated!", vbInformation
End Sub

Code Explanation:

• This code is very similar to the one used in the Workbook_Open event, except it is triggered manually through a button.
• The RefreshConnections procedure is linked to the button you inserted in the worksheet. When the user clicks the button, all connections are refreshed.

3. Automatically Refresh Connections at Regular Intervals (Optional)

If you want the connections to refresh automatically at regular intervals, you can use the following code, which should be placed in a standard module.

VBA Code for Regular Interval Refresh:

Dim NextRefresh As Date
Sub StartAutoRefresh()
    ' Start automatic refresh every X minutes
    NextRefresh = Now + TimeValue("00:05:00") ' Refresh every 5 minutes
    Application.OnTime NextRefresh, "AutoRefresh"
End Sub

Sub AutoRefresh()
    ' Refresh all connections
    Call UpdateAllConnections
    ' Restart automatic refresh
    Call StartAutoRefresh
End Sub

Sub UpdateAllConnections()
    Dim conn As Object
    ' Loop to refresh all connections
    For Each conn In ThisWorkbook.Connections
        On Error Resume Next
        conn.Refresh
        On Error GoTo 0
    Next conn
    MsgBox "All connections have been updated!", vbInformation
End Sub

Code Explanation:

• StartAutoRefresh: This macro starts the auto-refresh process by setting the next refresh time using Application.OnTime.
• AutoRefresh: This macro is automatically called at regular intervals to refresh all connections.
• NextRefresh: This variable defines the time for the next refresh (here, it’s set to 5 minutes after the first execution).

Conclusion:

You now have a complete VBA code for automatically updating data connections in an Excel workbook. You can run it on workbook open, via a button, or on a scheduled timer at regular intervals.