Calculate the distance between two points in Excel using VBA

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.