how to output vba array into excel

[breezer123]

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

 

[StephenCrump]

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
	A	B	C	D	E	F	G	H	I	J
1	A	1.5	3.0
2	B	0.5	-1.5		Distances	B	C	D	E	F
3	C	0.2	1.0		A	4.61	2.39	4.74	5.15	4.00
4	D	-3.0	1.5		B		2.52	4.61	3.50	5.41
5	E	4.0	-1.5		C			3.24	4.55	3.36
6	F	-2.5	3.0		D				7.62	1.58
7					E					7.91
Sheet1

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