 Search:

# Distance Function

Asked By: Harry    Date: Jan 14    Category: MS Office    Views: 1818

I want to create a function module that takes two ranges as arguments,
as in Distance(B3:C5,E4:F6). Assume that the two ranges are the same
size and shape so that there is a one-to-one correspondence between the
cells of each range. The distance function is the standard euclidean
one: the square root of the sum of the squares of the differences
between corresponding cells in each range.

When looping through the first range, how can I access the
CORRESPONDING cell in the second range? VBA For Each In -- Next loops
seem to frustrate this.

Share:

There's probably several ways of doing it, but here's one that's
fairly
simple:

I filled B2:C5 with 1,2,3,4,5,6
and E4:F6 with A,B,C,D,E,F

Then, I took advantage of the fact that a Range.Row returns the row
number of the first cell, and Range.Column returns the column number
of the first cell.
Then measured the distance  between the first cell  of each range  and
used the value as an "offset"

Function Distance(Rng1 As Range, Rng2 As Range)
Dim RowRng1, RowRng2, ColRng1, ColRng2
Dim OffsetRow, OffsetCol, Data, msg
RowRng1 = Rng1.Row
RowRng2 = Rng2.Row
ColRng1 = Rng1.Column
ColRng2 = Rng2.Column
OffsetRow = RowRng2 - RowRng1
OffsetCol = ColRng2 - ColRng1
msg = ""
For Each Data In Rng1
msg = msg & Data.Value _
& Cells(Data.Row + OffsetRow, Data.Column + OffsetCol) _
& Chr(13)
Next Data
MsgBox msg
End Function

I then called the function  with:

stat = Distance(Range("B3:C5"), Range("E4:F6"))

it produces a msgbox with:
1A
2B
3C
4D
5E
6F

You can change the math any way you want...

Another way would be to load two arrays with the contents of the
ranges, then compare array indexes..
(for a 30x30 array)
for X = 0 to 29
for Y = 0 to 29
msg = msg & Array1(X,Y) & ", " & Array2(X,Y)
Next Y
Next X

If I've understood correctly I think I can do this as a one liner:

Function distance(rng1 As Range, rng2 As Range)
distance = Evaluate("SQRT(SUM((" & rng1.Address _
& "-" & rng2.Address & ")^2))")
End Function

(the middle line there has a vba  continuation line in and should paste
across without editing)

This gives the same result as the formula in the spreadsheet
array-entered thus:

=SQRT(SUM((D5:F7-K14:M16)^2))

(Array-entered means holding the Ctrl and Shft keys while pressing the
Enter key to commit the formula.)

Which in turn gives the same result as doing it long hand.

At the moment, there are no checks to see that both ranges  are the
same size. Use the function  in a worksheet like so:

=distance(D5:F7,K14:M16)

=SQRT(SUMXMY2(D5:F7,K14:M16))

also gives the same result without the need to array-enter.

In the distance  formula in Euclidean space we haev only 4 arguments  required
to calculate the distance between two points.

I don't understand how you will use more than 4 points to calculate the
distance if you are giving input to function  with two ranges  and that too with
several values in each range.

I am using the euclidean metric for pattern recognition in a
space of high dimesionality. This has no connection to classical
euclidean plane, nor to the projective plane that we study in
geometry.

And by the way, thanks to all for the great support. In the end, I
used the non-macro approach, and it works fine. I always had a low
opinion of spread sheets as a programming environment, but that
mentality was a relic of Visicalc from 25 years ago. As a result of
and macro programming! Excel is very appealing to me: I remember
the dark days of fortran II and fortran 4, when we spent 5% of the
time solving the problem, and 95% of the time formatting the output.
I guess my other favorite application on a PC is MathCad.

A bit off topic for Excel, but: At last weekend's HP calculator
conference in Rancho Bernardo there was a programming contest. Given
an integer of up to ten digits (no digit can be a zero), display the
inteter that has those same digits sorted in increasing order (no
need to show leading zeros). This was to be programmed on an HP-35s
calculator, but it is an interesting problem in any environment.
Entries to be judged on speed alone. My solution was to dissect the
input integer into digits, sort the digits, and re-build the new
number. My entry placed about fourth out of 25. But the winner was
siginificantly faster than the second placer. He did something very
different. I think this can be done in excel with a single formula
in a cell  (albeit a very long one).

In A1 your "integer of up to ten digits (no digit can be a zero)", in
any other cell  that "single formula in a cell (albeit a very long
one)" array-entered:

=SUM(SMALL(VALUE(MID(\$A\$1,ROW(INDIRECT("A1:A" &
LEN(A1))),1)),ROW(INDIRECT("A1:A" &
LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("A1:A" & LEN(A1)))))

making sure the cell is wide enough to show all the digits. Will work
up to 15 digits but for more than 11 digits it requires the cells  to
be formatted as Number with no decimal places.
There will be a shorter one I'm sure!

Sorry , my ignorance in the field of geometry is limited to my college
years. It was an innocent reaction from my side considering my level of
knowledge in geometry. I better think twice next time before giving such an

Anyway congrats on solving your problem.

Didn't find what you were looking for? Find more on Distance Function Or get search suggestion and latest updates.