Calculate the range of displacement in Excel VBA

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.