how to output vba array into excel

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hi all! New to Mr.Excel!

I am currently trying to run a code that will return all distance values between a number of x,y coordinates. i.e. the distance from each x,y location to all other x,y locations. Right now I have that as distance in the 2nd loop. I want to make this into an array, so that I can use those distance values inside of another macro. However, I can not figure out how to print the array or make it into one. I have tried the "dim distance(1 to 50, 1 to 50) as variant" to create a large matrix to then be filled up with my distance calculations in the loop because my matrix may be changing in size. Not sure if this is the correct approach. Could anyone help?

Here is the code I have.

Sub CalculateAllDistances()
Dim X1 As Double
Dim Y1 As Double
Dim X2 As Double
Dim Y2 As Double
Dim zon As Integer, wpz As Integer, rowcount As Integer, i As Integer, m As Integer
Dim distance(1 To 50, 1 To 50) As Variant
zon = Range("B1")
wpz = Range("B2")
rowcount = zon * wpz + 8

For i = 9 To rowcount

If Cells(i, 20).Value & Cells(i, 21) <> "" Then

X1 = Cells(i, 20).Value
Y1 = Cells(i, 21).Value

For m = i + 1 To rowcount

If Cells(m, 20).Value & Cells(m, 21).Value <> "" Then

X2 = Cells(m, 20).Value
Y2 = Cells(m, 21).Value
distance(i, m) = Sqr((Y2 - Y1) ^ 2 + (X2 - X1) ^ 2)

End If

Next m

End If

Next i

End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

You're on the right track using arrays - it's the most efficient way to load a range from Excel into VBA, and to write from VBA into Excel (i.e. rather than reading/writing cell by cell).

Try this:

Distances.xlsm
ABCDEFGHIJ
1A1.53.0
2B0.5-1.5DistancesBCDEF
3C0.21.0A4.612.394.745.154.00
4D-3.01.5B2.524.613.505.41
5E4.0-1.5C3.244.553.36
6F-2.53.0D7.621.58
7E7.91
Sheet1


VBA Code:
Sub CalculateAllDistances()

    Dim StartCell As Range, OutputCell As Range
    Dim Coords As Variant, Distances() As Variant
    Dim NoRows As Long, NoCols As Long, i As Long, j As Long
    
    Set StartCell = Range("A1")
    Set OutputCell = Range("E2")
    NoRows = Cells(Rows.Count, StartCell.Column).End(xlUp).Row - StartCell.Row + 1
    NoCols = 3
    Coords = StartCell.Resize(NoRows, NoCols).Value
    ReDim Distances(1 To NoRows, 1 To NoRows)
    On Error Resume Next
    Range("MyOutput").ClearContents
    On Error Resume Next
    
    For i = 2 To NoRows
        Distances(i, 1) = Coords(i - 1, 1)
        Distances(1, i) = Coords(i, 1)
        For j = i To NoRows
            Distances(i, j) = Sqr((Coords(j, 3) - Coords(i - 1, 3)) ^ 2 + (Coords(j, 2) - Coords(i - 1, 2)) ^ 2)
        Next j
    Next i
    Distances(1, 1) = "Distances"
    
    With OutputCell.Resize(UBound(Distances), UBound(Distances, 2))
        .Value = Distances
        .Rows(1).Font.Bold = True
        .Columns(1).Font.Bold = True
        .Name = "MyOutput"
    End With
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,629
Messages
5,597,252
Members
414,133
Latest member
lucid33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top