Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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.

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

your help, I learned that there is a lot to learn about spreadsheets

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

answer.

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.

Related Topics:

- Wait function and Invisible function
- Wait function and Invisible function
- Distance measurement
- Develop a distance learning system as my project
- How can I call the function TotalPrice within another function?
- help on functions
- Problems with XL's 'MATCH' function
- Excel won't calculate my function
- User Function - is optional calculation possibe?
- Automatic update of Function return value.
- String-Split Function parameter "compare"
- function as argument
- Need to create function smiliar to DMAX
- Logistic Regression (Statistical Functions)
- Wierd problem in VBA code re Mid function
- function question
- function to change the color of the cell
- User Defined Function didn't work
- How does the Worksheets function work?
- Converting Date functions
- user-designed Excel math functions
- Convert Serial Number to a Date Function
- about JSP reset function
- how Mysql database function java code execute ?
- MAPI Functions