# how to output vba array into excel

#### breezer123

##### New Member
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### StephenCrump

##### MrExcel MVP
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``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,329
Messages
5,836,675
Members
430,444
Latest member
WrenchBoy

### 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.

### Which adblocker are you using?

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

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