Catégorie : Excel VBA Course

The Net Present Value (NPV) is a financial tool used to evaluate the profitability of a project or investment. It is calculated by subtracting the sum of the initial investment from the sum of the discounted cash flows at a given discount rate.

The general NPV formula is: VAN=(∑Ct/((1+r)^t)) -I0

Where:

• Ct ​: Cash flow at time t
• r: Discount rate
• t: Time period (in years, months, etc.)
• I0​: Initial investment
• n: Total number of periods

Example: NPV Calculation in VBA

Here is an example of VBA code in Excel to calculate NPV based on the cash flows and discount rate.

Steps to create the VBA code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, create a new module by going to Insert > Module.
3. Paste the following code into the module.
4. Close the VBA editor and return to Excel.

VBA Code for NPV Calculation:

Sub CalculateNPV()
    ' Define the variables
    Dim DiscountRate As Double
    Dim InitialInvestment As Double
    Dim CashFlows(1 To 5) As Double ' For example, an array for 5 cash flows
    Dim NPV As Double
    Dim i As Integer
    Dim n As Integer
    ' Initialize the data
    InitialInvestment = Range("B1").Value ' Initial investment in cell B1
    DiscountRate = Range("B2").Value ' Discount rate in cell B2   
    ' Fill the cash flows (e.g., for 5 years)
    For i = 1 To 5
        CashFlows(i) = Range("B" & i + 2).Value ' Cash flows in cells B3 to B7
    Next 
    ' Calculate the NPV
    NPV = -InitialInvestment ' Start by subtracting the initial investment
    n = 5 ' Number of periods, here we have 5 years
    For i = 1 To n
        NPV = NPV + CashFlows(i) / (1 + DiscountRate) ^ i ' Add each discounted cash flow
    Next i
    ' Display the NPV in cell B8
    Range("B8").Value = NPV
    ' Display a message if the NPV is positive or negative
    If NPV > 0 Then
        MsgBox "The NPV is positive: " & NPV, vbInformation, "Result"
    ElseIf NPV < 0 Then
        MsgBox "The NPV is negative: " & NPV, vbExclamation, "Result"
    Else
        MsgBox "The NPV is zero.", vbInformation, "Result"
    End If
End Sub

Detailed Explanation of the Code:

1. Variable Declaration:
   • DiscountRate: the discount rate (expressed as a percentage, e.g., 0.05 for 5%).
   • InitialInvestment: the initial cost of the investment (usually a negative value).
   • CashFlows(1 To 5): an array to store the cash flows for 5 periods (this can be changed based on the number of periods).
   • NPV: the calculated Net Present Value.
   • i and n: used for looping.
2. Initializing Data:
   • The initial investment is retrieved from cell B1.
   • The discount rate is retrieved from cell B2.
   • Cash flows for each year are stored in cells B3 to B7, and the code reads them into the CashFlows array.
3. Calculating the NPV:
   • The NPV calculation starts by subtracting the initial investment.
   • Then, for each period (from 1 to n), the corresponding cash flow is discounted and added to the NPV.
4. Displaying the Result:
   • The NPV result is displayed in cell B8.
   • A message box will pop up to show whether the NPV is positive, negative, or zero.

How to Use This Code in Excel:

1. Input Data:
   • B1: Initial Investment (e.g., -1000).
   • B2: Discount Rate (e.g., 0.05 for 5%).
   • B3 to B7: Cash flows for each period (e.g., 200, 300, 400, 500, 600).
2. Running the Code:
   • After entering the data in the cells, you can run the code by pressing F5 in the VBA editor or assigning the macro to a button in Excel.

This method allows you to easily calculate the NPV for a project based on its cash flows and discount rate. You can adjust the number of periods and cash flows according to your specific project.

Scenario:

• Average speed (in km/h)
• Duration of travel (in hours)
• The range of displacement will be calculated as:

Range=Speed×Duration×Adjustment Factor

Example of VBA Code to Calculate Displacement Range:

1. Open the VBA Editor

• Open Excel.
• Press Alt + F11 to open the VBA editor.
• In the editor, go to Insert > Module to create a new module.

2. VBA Code

Here’s an example of VBA code to calculate the displacement range:

Sub CalculateDisplacementRange()
    ' Declare variables
    Dim speed As Double ' Speed in km/h
    Dim duration As Double ' Duration of travel in hours
    Dim range As Double ' Displacement range in km
    ' Ask the user for the speed (in km/h)
    speed = InputBox("Enter the average speed (in km/h):")   
    ' Check if the speed is valid (positive)
    If speed <= 0 Then
        MsgBox "The speed must be greater than zero.", vbExclamation
        Exit Sub
    End If   
    ' Ask the user for the duration of travel (in hours)
    duration = InputBox("Enter the duration of travel (in hours):")  
    ' Check if the duration is valid (positive)
    If duration <= 0 Then
        MsgBox "The duration must be greater than zero.", vbExclamation
        Exit Sub
    End If   
    ' Calculate the displacement range
    range = speed * duration  
    ' Display the result in a message box
    MsgBox "The displacement range is: " & range & " km", vbInformation
End Sub

Code Explanation:

1. Variable Declarations:
   • speed: The average speed of the vehicle (in km/h).
   • duration: The duration of travel (in hours).
   • range: The calculated displacement range (in kilometers).
2. User Input:
   • The InputBox function asks the user to enter the speed and duration.
   • The program checks if the input values are positive. If the values are zero or negative, a message box will display an error, and the program will stop execution (Exit Sub).
3. Displacement Calculation:
   • The displacement range is calculated by multiplying the speed by the duration (range = speed * duration).
4. Displaying the Result:
   • The result is displayed to the user using the MsgBox function, showing the displacement range in kilometers.

Steps to Run the Code:

1. After entering this code in a new module in the VBA editor (following the steps above), you can run the program in two ways:
   • Press F5 in the VBA editor to run the code.
   • Or assign the code to a button on your Excel sheet by selecting « Insert > Shapes > Button. »

Example of Usage:

• If the user enters a speed of 60 km/h and a duration of 2 hours, the displacement range will be: 60 km/h×2 h=120 km60 \, \text{km/h} \times 2 \, \text{h} = 120 \, \text{km}60km/h×2h=120km The message displayed will be: « The displacement range is: 120 km. »

Extension:

This code can be easily modified to include other factors, such as fuel consumption or environmental factors. For instance, if you need to add a reduction in range based on fuel efficiency or other conditions, you can adjust the calculation accordingly.

Calculating a moving median (also called a « sliding median ») in Excel using VBA is a common task for time series analysis or numerical data smoothing. The moving median is used to smooth the data by calculating the median of a sliding window of values within a dataset.

Objective

The goal here is to write a VBA code to calculate the moving median over a defined window size (e.g., 3 periods, 5 periods, etc.) within a range of data in Excel.

Requirements

1. Data: A column of numerical data.
2. Window size for the moving median: A defined number of periods (e.g., 3 or 5).
3. Output: A separate column where the moving median results will be displayed.

Example Data

Assume your data is in column A, from cell A2 to A100. You want to calculate the moving median with a 3-period window and display the results starting from cell B3.

Detailed VBA Code

Here’s the VBA code that performs this calculation:

Sub CalculateMovingMedian()
    Dim dataRange As Range
    Dim resultRange As Range
    Dim windowSize As Integer
    Dim i As Long
    Dim j As Long
    Dim window() As Double
    Dim median As Double   
    ' Define the data range (column A from A2 to A100)
    Set dataRange = Range("A2:A100")   
    ' Define the window size (e.g., 3 periods)
    windowSize = 3   
    ' Define the result range (column B starting from B3)
    Set resultRange = Range("B3:B100")   
    ' Check if the result range is large enough
    If resultRange.Rows.Count < dataRange.Rows.Count - windowSize + 1 Then
        MsgBox "The result range is too small!"
        Exit Sub
    End If   
    ' Calculate the moving median
    For i = windowSize To dataRange.Rows.Count
        ' Create an array to store the window values
        ReDim window(windowSize - 1)       
        ' Fill the array with the window data
        For j = 0 To windowSize - 1
            window(j) = dataRange.Cells(i - j, 1).Value
        Next j        
        ' Sort the array to find the median
        Call SortArray(window)       
        ' Calculate the median (middle value of the sorted array)
        median = window(Int(windowSize / 2))       
        ' Display the median in the result column
        resultRange.Cells(i - windowSize + 1, 1).Value = median
    Next i
End Sub

Sub SortArray(ByRef arr() As Double)
    Dim i As Long, j As Long
    Dim temp As Double   
    ' Bubble sort to sort the array in ascending order
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                ' Swap the values
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

Explanation of the Code

1. Defining Data and Result Ranges:
   • dataRange specifies the range of cells that contain the raw data. Here, it refers to column A from A2 to A100.
   • resultRange is where the calculated moving medians will be stored. It starts at cell B3 to avoid overwriting the initial rows that don’t have enough data for the median.
2. Moving Median Window (n periods):
   • The variable windowSize defines the size of the moving window. In this example, a window of 3 periods is used, but this can be adjusted based on your needs.
3. Calculating the Moving Median:
   • For each position i in the data range, a window of n values is extracted (the last n values).
   • These values are sorted, and the median is calculated as the middle value of the sorted array.
   • The result of the median is stored in the corresponding cell in the result range.
4. Sorting the Window Values:
   • The code uses an auxiliary procedure SortArray that sorts the window of values in ascending order using the Bubble Sort algorithm.
   • After sorting, the median is simply the middle value in the sorted array (for an odd-sized window).

How to Run the Code:

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. Go to Insert > Module and paste the code.
4. Press F5 to run the macro and calculate the moving median.

Example Use Case:

• Data in column A (e.g., A2:A100).
• Moving Median Window Size: 3 periods.
• Results in column B (starting from B3).

Additional Improvements:

• The sorting function uses Bubble Sort, which can be slow for large datasets. For performance, you might want to use faster sorting algorithms like QuickSort or MergeSort if you’re dealing with large amounts of data.
• You can add an input box or a dialog to let the user choose the window size dynamically instead of hardcoding it in the code.

Moving Average Calculation Overview:

A moving average is commonly used in time series analysis to smooth out short-term fluctuations and highlight longer-term trends or cycles. The moving average is typically calculated by taking the average of a subset of data within a specified window size, which then « moves » along the series.

This code will allow you to calculate a moving average for a given range of data in Excel, where the average is calculated over a specified number of data points (e.g., a 5-point window). The results will be written to another column.

VBA Code to Calculate Moving Average:

Sub CalculateMovingAverage()
    ' Declare variables
    Dim ws As Worksheet
    Dim rangeData As Range
    Dim rangeResult As Range
    Dim windowSize As Integer
    Dim i As Integer, j As Integer
    Dim sum As Double
    Dim currentCell As Range
    ' Initialize variables
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if needed
    Set rangeData = ws.Range("A2:A100") ' Range containing the data (adjust as needed)
    Set rangeResult = ws.Range("B2:B100") ' Range where the results will be displayed
    windowSize = 5 ' Size of the moving window (this can be adjusted)
    ' Loop through each cell in the data range
    For i = windowSize To rangeData.Rows.Count
        sum = 0       
        ' Calculate the sum of values in the window of size windowSize
        For j = i - windowSize + 1 To i
            sum = sum + rangeData.Cells(j, 1).Value
        Next j    
        ' Calculate the moving average and display it in the result range
        rangeResult.Cells(i, 1).Value = sum / windowSize
    Next i
    MsgBox "Moving average calculation completed.", vbInformation
End Sub

Code Explanation:

1. Declare Variables:
   • ws is a variable that represents the worksheet where the data is stored.
   • rangeData is the range of cells that contains the data you want to calculate the moving average for (in this case, A2:A100).
   • rangeResult is the range where the moving averages will be written (in this case, B2:B100).
   • windowSize is the size of the window used to calculate the moving average (in this example, it’s set to 5).
2. Initialize Variables:
   • The code references Sheet1 for the data sheet, but you can change this to the appropriate sheet name in your workbook.
   • The range A2:A100 is used for the data, and B2:B100 is where the results are displayed. Adjust these ranges to match your actual data.
   • The windowSize is set to 5, meaning the average will be calculated using the last 5 data points.
3. Calculate the Moving Average:
   • The outer loop (starting from i = windowSize) iterates through each data point in the range starting from the 5th value (since we need at least 5 data points to calculate the first average).
   • The inner loop calculates the sum of the windowSize values leading up to the current data point.
   • The moving average is calculated by dividing the sum by the windowSize.
   • The result is written into the corresponding cell in the rangeResult column.
4. Display a Message:
   • Once the moving averages have been calculated, a message box will pop up saying « Moving average calculation completed. »

How to Use the Code:

1. Open the VBA Editor: Press Alt + F11 in Excel to open the Visual Basic for Applications editor.
2. Insert a New Module: Click Insert > Module to add a new module.
3. Copy and Paste the Code: Copy the code above and paste it into the new module.
4. Run the Macro: Press F5 or go to Run > Run Sub/UserForm to execute the macro.

Customization:

• Window Size: You can adjust the windowSize variable to change how many data points the moving average is calculated over (e.g., changing it from 5 to 10 for a larger window).
• Data Range: Modify the rangeData and rangeResult ranges to fit the location of your data and where you want to place the results.
• Result Placement: You can change the result range to any other column (e.g., C2:C100) if you prefer to place the moving averages elsewhere.

Example:

• If you have the following data in column A (from A2 to A100):

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...

• With a window size of 5, the first moving average (in cell B6) would be the average of A2:A6, the second moving average (in cell B7) would be the average of A3:A7, and so on.

This VBA code makes calculating moving averages in Excel automated and much easier, especially for larger datasets or when doing repeated calculations.

 

VBA Code: Kurtosis Calculation

Function Kurtosis(rng As Range) As Double
    Dim cell As Range
    Dim n As Long
    Dim sumX As Double, sumX2 As Double, sumX4 As Double
    Dim meanX As Double, stdDev As Double
    Dim result As Double
    ' Initialize variables
    n = rng.Cells.Count
    If n < 4 Then
        Kurtosis = CVErr(xlErrDiv0) ' Error if fewer than 4 values (kurtosis requires a sample size of at least 4)
        Exit Function
    End If   
    ' Calculate the mean
    For Each cell In rng
        sumX = sumX + cell.Value
    Next cell
    meanX = sumX / n
    ' Calculate variance and fourth-order moments
    For Each cell In rng
        sumX2 = sumX2 + (cell.Value - meanX) ^ 2
        sumX4 = sumX4 + (cell.Value - meanX) ^ 4
    Next cell
    ' Standard deviation
    stdDev = Sqr(sumX2 / n)
    ' Compute kurtosis using Fisher’s formula (adjusted for a sample)
    If stdDev <> 0 Then
        result = (sumX4 / n) / (stdDev ^ 4)
        Kurtosis = ((n * (n + 1)) / ((n - 1) * (n - 2) * (n - 3))) * result - (3 * (n - 1) ^ 2) / ((n - 2) * (n - 3))
    Else
        Kurtosis = CVErr(xlErrDiv0) ' Error if standard deviation is zero
    End If
End Function

Code Explanation

Sample size verification

• If the range contains fewer than 4 values, kurtosis cannot be properly calculated (risk of division by zero).
• Returns a #DIV/0! error.

Mean calculation

• Sums all values in the range and divides by n.

Variance and fourth-order moment calculation

• sumX2: Sum of squared deviations from the mean (raw variance).
• sumX4: Sum of deviations raised to the fourth power.

Standard deviation calculation

• The standard deviation is the square root of variance.

Application of adjusted kurtosis formula (excess kurtosis)

• Fisher’s formula for a finite sample is used:

• This formula adjusts sample kurtosis to avoid bias.

Error handling

• If the standard deviation is zero (all values are identical), kurtosis is undefined → returns #DIV/0! error.

How to use this function in Excel?
Enter values in a column (e.g., A1:A10).
In a cell, enter the formula:

=Kurtosis(A1:A10)

The cell will display the sample kurtosis.

This function is more accurate than Excel’s built-in KURT() as it uses an unbiased formula for a sample.

Steps before you start:

1. Prepare the Data: Enter the cash flows in an Excel column (for example, from cell A2 to A7).
2. Add the VBA Code: Open the VBA editor by pressing Alt + F11, then insert a new module (via Insert > Module).

VBA Code to Calculate IRR

Function InternalRateOfReturn(flux As Range) As Double
    Dim guess As Double
    Dim rate As Double
    Dim npv As Double
    Dim tolerance As Double
    Dim iteration As Integer
    Dim maxIterations As Integer
    ' Initializing variables
    guess = 0.1 ' Starting guess rate (10%)
    maxIterations = 100 ' Maximum number of iterations
    tolerance = 0.00001 ' Tolerance for determining the precision of the result
    ' Start finding the rate that makes NPV close to zero
    For iteration = 1 To maxIterations
        npv = 0 ' Reset NPV at each iteration
        ' Calculate NPV for the current rate
        For i = 1 To flux.Count
            npv = npv + flux.Cells(i).Value / (1 + guess) ^ (i - 1)
        Next i      
        ' If NPV is close enough to zero, we have found our IRR
        If Abs(npv) < tolerance Then
            InternalRateOfReturn = guess
            Exit Function
        End If      
        ' Adjust the rate depending on the direction of NPV
        guess = guess - npv / Derivative(flux, guess)
    Next iteration
    ' If no solution is found, return an error
    InternalRateOfReturn = CVErr(xlErrNA)
End Function

Function Derivative(flux As Range, guess As Double) As Double
    ' Function to calculate the derivative of NPV with respect to the rate
    Dim epsilon As Double
    Dim npv1 As Double
    Dim npv2 As Double
    Dim derivative As Double
    epsilon = 0.00001 ' Small value to compute the derivative
    npv1 = 0
    npv2 = 0
    ' Calculate NPV for two rates slightly different
    For i = 1 To flux.Count
        npv1 = npv1 + flux.Cells(i).Value / (1 + guess) ^ (i - 1)
        npv2 = npv2 + flux.Cells(i).Value / (1 + guess + epsilon) ^ (i - 1)
    Next i
    ' Calculate the derivative using finite difference
    derivative = (npv2 - npv1) / epsilon
    Derivative = derivative
End Function

Code Explanation

1. InternalRateOfReturn Function:
   • This function takes a range of cells containing cash flows as input.
   • The IRR is calculated using an iterative approach (Newton-Raphson method), where the rate is adjusted until the net present value (NPV) is close to zero.
   • The initial guess (guess) is set arbitrarily at 10% and can be adjusted as needed.
   • The tolerance determines how precise the result should be (here set to 0.00001).
   • The maximum number of iterations is set to 100 to avoid infinite loops in case the calculation doesn’t converge.
2. Derivative Function:
   • This function calculates the derivative of the NPV with respect to the rate. It is used to adjust the rate during the iterations. The derivative is calculated using finite differences, which is done by evaluating the NPV at two values close to the current rate.

How to Use the Code in Excel

1. Enter your cash flows in an Excel column (for example, from A2 to A7).
2. In any empty cell, use the custom InternalRateOfReturn function you created in VBA. For example, if your cash flows are in the range A2:A7, you can enter the following formula in any cell:

=InternalRateOfReturn(A2:A7)

Example:

If your cash flows are as follows:

• Year 0 (Initial Investment): -1000 €
• Year 1: 300 €
• Year 2: 400 €
• Year 3: 500 €
• Year 4: 600 €

The cash flows in Excel would look like this:

A2: -1000
A3: 300
A4: 400
A5: 500
A6: 600

By entering the formula =InternalRateOfReturn(A2:A6) in an empty cell, you will get the corresponding IRR.

Things to Check:

• If the IRR doesn’t converge (for example, if the cash flows are too complex), the algorithm might not find a solution. You can try modifying the initial guess (guess) or adjust the tolerance for better convergence.

This code provides a basic structure for calculating IRR in VBA, but it can be adapted for more complex cases such as irregular cash flows or other financial models.

 

This code creates a VBA function that generates the Fibonacci sequence up to a specified term.

Steps to Implement the Fibonacci Sequence in VBA:

1. Open the VBA Editor:
   • In Excel, press Alt + F11 to open the VBA editor.
   • In the editor, click Insert and then choose Module. This will add a new module where you can write your code.
2. Write the VBA Code to Calculate the Fibonacci Sequence:

Here is the complete VBA code with detailed explanations.

Sub CalculateFibonacci()
    ' Declare variables
    Dim n As Integer
    Dim fib1 As Long, fib2 As Long, fib3 As Long
    Dim i As Integer   
    ' Ask the user for the number of Fibonacci terms to display
    n = InputBox("How many Fibonacci terms do you want to display?", "Input", 10)   
    ' Check if the input is valid
    If n <= 0 Then
        MsgBox "Please enter a number greater than 0.", vbExclamation
        Exit Sub
    End If   
    ' Initialize the first two terms of the Fibonacci sequence
    fib1 = 0
    fib2 = 1   
    ' Display the first two terms
    Range("A1").Value = fib1
    Range("A2").Value = fib2   
    ' Calculate the next terms and display them in column A
    For i = 3 To n
        ' The next term is the sum of the two previous terms
        fib3 = fib1 + fib2       
        ' Display the term in the corresponding cell in column A
        Cells(i, 1).Value = fib3       
        ' Update the values of the last two terms
        fib1 = fib2
        fib2 = fib3
    Next i   
    MsgBox "Calculation completed for " & n & " Fibonacci terms.", vbInformation
End Sub

Explanation of the Code:

1. Variable Declarations:
   • n: This will be the number of terms the user wants to display from the Fibonacci sequence.
   • fib1 and fib2: The first two terms of the Fibonacci sequence.
   • fib3: The next term, which is calculated in each iteration.
   • i: A counter used in the For loop to iterate through the terms.
2. User Input:
   • InputBox: A dialog box is displayed asking the user how many terms they want to see from the Fibonacci sequence. The input is stored in the variable n.
3. Input Validation:
   • If the user enters a number less than or equal to 0, an alert appears, and the program exits.
4. Initializing the First Two Terms:
   • The first two terms of the Fibonacci sequence are defined:
     • fib1 = 0 (the first term)
     • fib2 = 1 (the second term)
5. Displaying Terms in Excel:
   • The first two terms (0 and 1) are directly displayed in cells A1 and A2 in Excel.
   • A For loop is used to calculate and display the subsequent terms until the user-specified number (n) is reached. In each iteration:
     • The next term is calculated as the sum of the previous two terms.
     • The term is displayed in the corresponding cell in column A (e.g., A3, A4, etc.).
6. Updating Previous Terms:
   • After each iteration, the variables fib1 and fib2 are updated to hold the last two terms of the sequence.
7. Final Message:
   • A message box appears indicating that the calculation is complete for the specified number of terms.

How to Use the Code:

1. After pasting the code into the VBA editor, you can run it by pressing F5 in the editor or by assigning the macro to a button in your Excel sheet.
2. The Fibonacci sequence will be displayed starting from cell A1 down to the cell corresponding to the number of terms requested.

Example:

If you enter « 10 » in the dialog box, the first 10 terms of the Fibonacci sequence will be displayed in cells from A1 to A10:

A1: 0
A2: 1
A3: 1
A4: 2
A5: 3
A6: 5
A7: 8
A8: 13
A9: 21
A10: 34

What is a factorial?

The factorial of a non-negative integer nnn is the product of all the integers from 1 to nnn, i.e.:

n!=n×(n−1)×(n−2)×⋯×1n! =n×(n−1)×(n−2)×⋯×1

For example: 5!=5×4×3×2×1=5×4×3×2×1=120

VBA Code to Calculate the Factorial

Sub CalculateFactorial()
    ' Declare variables
    Dim n As Integer
    Dim result As Long
    Dim i As Integer   
    ' Prompt the user to enter a number
    n = InputBox("Enter an integer to calculate its factorial:")   
    ' Check if the input is valid
    If n < 0 Then
        MsgBox "Factorial is not defined for negative numbers.", vbExclamation
        Exit Sub
    End If   
    ' Initialize the result
    result = 1   
    ' Calculate the factorial
    For i = 1 To n
        result = result * i
    Next i  
    ' Display the result
    MsgBox "The factorial of " & n & " is: " & result
End Sub

Explanation of the Code:

1. Variable Declarations:

Dim n As Integer
Dim result As Long
Dim i As Integer

1. • n : Variable to store the number for which we want to calculate the factorial.
   • result : Variable to store the result of the factorial calculation, declared as Long to handle larger values.
   • i : Control variable for the loop.
2. Prompting the User for Input:

n = InputBox("Enter an integer to calculate its factorial:")

• • The InputBox function asks the user to enter a number.

3. Input Validation:

• If the user enters a negative number, an error message is shown, and the code execution is stopped using Exit Sub.

4. Initializing the Result:

result = 1

• • The variable result is initialized to 1, since multiplication starts with this value (the identity element for multiplication in factorial calculation).

5. Factorial Calculation Using a Loop:

For i = 1 To n
    result = result * i
Next i

• • The For loop iterates from 1 to nnn, multiplying result by each value of i at each iteration.

6. Displaying the Result:

MsgBox "The factorial of " & n & " is: " & result

1. • Once the calculation is complete, a message box displays the result of the factorial of n.

How to Use the Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module:
   • Click on Insert in the menu bar and select Module.
3. Copy and paste the code above into this module.
4. To run the code, press F5 or go to the Run menu and select Run Sub/UserForm.

A message will appear prompting you to enter a number, and once you do, another message will show you the factorial of that number.

Example of Execution:

If you enter 5 in the input dialog, the program will calculate 5! and display the message:

The factorial of 5 is: 120

 

 

VBA Code to Calculate EMA:

Sub CalculateEMA()
    ' Define variables
    Dim DataRange As Range
    Dim i As Long
    Dim Alpha As Double
    Dim EMA As Double
    Dim CurrentValue As Double
    Dim EMARange As Range   
    ' Prompt user to select the data range
    Set DataRange = Application.InputBox("Select the data range", Type:=8)   
    ' Prompt user to enter the alpha smoothing factor
    Alpha = Application.InputBox("Enter the alpha factor (e.g., 0.1)", Type:=1)   
    ' Initialize the first EMA with the first data value
    EMA = DataRange.Cells(1, 1).Value   
    ' Create a range to display the results
    Set EMARange = DataRange.Offset(0, 1) ' Display EMA in the adjacent column 
    ' Calculate the EMA for each value
    For i = 2 To DataRange.Cells.Count
        CurrentValue = DataRange.Cells(i, 1).Value
        EMA = (Alpha * CurrentValue) + ((1 - Alpha) * EMA) ' EMA formula
        EMARange.Cells(i - 1, 1).Value = EMA ' Store the calculated EMA
    Next i
    ' Confirmation message
    MsgBox "EMA calculation complete!", vbInformation
End Sub

Explanation of the Code:

1. Data Range: The user selects the data range for which they want to calculate the EMA.
2. Alpha: The smoothing factor alpha is requested from the user. The value of alpha controls how much weight is given to the recent data; a higher alpha gives more weight to recent values.
3. EMA Calculation:
   • The first EMA is initialized with the first data point.
   • For each subsequent value, the EMA is updated using the formula: EMAt=(α×Current Value)+(1−α)×EMAt−1
4. Displaying the Results: The calculated EMA is placed in the adjacent column to the selected data range.

Example:

If you have a column of data in A1:A10 and you specify an alpha of 0.1, the EMA will be calculated and displayed in the adjacent column B1:B10.

To calculate the distance between two points in Excel using VBA (Visual Basic for Applications), we can use the Euclidean distance formula, which is:

Distance=sqrt((x2−x1)2+(y2−y1)2)

Here, (x1,y1) and (x2​,y2​) are the coordinates of the two points.

Steps to create the VBA code:

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, go to Insert > Module to insert a new module.
4. Paste the code below into the module.

VBA Code to Calculate the Distance Between Two Points:

Sub CalculateDistance()
    ' Declare variables
    Dim x1 As Double, y1 As Double
    Dim x2 As Double, y2 As Double
    Dim distance As Double   
    ' Get the coordinates of the two points (can be modified to take values from cells)
    x1 = InputBox("Enter the X coordinate of the first point (x1):")
    y1 = InputBox("Enter the Y coordinate of the first point (y1):")
    x2 = InputBox("Enter the X coordinate of the second point (x2):")
    y2 = InputBox("Enter the Y coordinate of the second point (y2):")   
    ' Calculate the distance between the two points
    distance = Sqr((x2 - x1) ^ 2 + (y2 - y1) ^ 2)  
    ' Display the result in a message box
    MsgBox "The distance between the two points is: " & distance, vbInformation, "Result"
End Sub

Explanation of the Code:

1. Declare Variables:
   We declare four variables to store the coordinates of the two points: x1, y1, x2, and y2. These variables are of type Double because the coordinates could be decimal numbers.
2. Input Coordinates:
   We use the InputBox function to prompt the user to enter the coordinates of the two points. These values are then stored in the variables x1, y1, x2, and y2.
3. Distance Calculation:
   The Euclidean distance formula is applied using the Sqr function, which calculates the square root. The formula is:
4. Display the Result:
   The result of the calculation is shown in a message box (MsgBox), which displays the distance between the two points.

Using the Code:

• When you run the code, it will prompt you to enter the coordinates of the two points. After entering the values, it will calculate and display the distance between the two points.

Example:

If the coordinates of the two points are:

• Point 1: (3, 4)
• Point 2: (7, 1)

The calculation will be:

Distance=sqrt((7−3)2+(1−4)2)=sqrt(42+(−3)2)=5

The result shown will be: « The distance between the two points is: 5. »

Customization:

If you want the code to take the coordinates directly from Excel cells (for example, A1, B1 for the first point, and A2, B2 for the second point), you can modify the InputBox section to directly retrieve the values from the cells:

x1 = Range(« A1 »).Value

y1 = Range(« B1 »).Value

x2 = Range(« A2 »).Value

y2 = Range(« B2 »).Value

This way, the coordinates will be taken directly from the specified cells in Excel.