Ranges and Arrays in Excel VBA...

[a.riva@UCL]

Hi everyone!

I'm trying to program a new excel function that I need for my job
(I'm
a biomedical scientist at UCL London), but I got stuck with ranges
and
arrays.


Basically what the formula should do is:

reading values from two ranges in a worksheet (Range1 and Range2, both "As Range")
transferring each set of values from its own range into a separate array (Set1() and Set2(), both "As Double")

Range1 --> Set1()
Range2 --> Set2()

calculating the difference between the two arrays as a new array (Diff(), "As Double")

Diff(i) = Set1(i) - Set2(i)

calculating the absolute value of each item in the "difference" array as a new array (AbsDiff(), "As Double")

AbsDiff(i) = Abs(Diff(i))

(and so far my code works fine, but from now on I have problems...)

ranking each item in the array AbsDiff() according to the same array into a new array (RankAbsDiff(), "As Double"). And here I have problems, because the Rank formula preset in Excel requires the first argument (the value to rank) "As Double" (in my case this would be each AbsDiff(i), and it's fine) and the second argument (the reference for the calculation) "As Range". The fact is that I do not know how to convert my array of doubles (AbsDiff()) into a variable that can be used as range:

RankAbsDiff(i) = Application.WorksheetFunction.Rank(x As Double, ref
As Range) should become
RankAbsDiff(i) =
Application.WorksheetFunction.Rank(AbsDiff(i), ???)...

And after that I have to use the new array RankAbsDiff() for other
mathematical calculations, that I think shouldn't give me any
problems.


What can I do? Can somebody help me?


Thanks a lot!!! In advance!!!


Please let me know soon!!!


Antonio.

 

[Bernie Deitrick]

Antonio,

You could do something along these lines (this code that will return a lower rank for a higher
value). In this code, I have used

ADA = Abs Diff Array
RADA = Ranked ADA

If you want the Rank in the opposite direction, let me know.

HTH,
Bernie
MS Excel MVP


Sub RunTestOnRADFunction()
Dim myArray As Variant
Dim i As Integer

myArray = RAD(Range("A1:A10"), Range("B1:B10"))

For i = LBound(myArray) To UBound(myArray)
MsgBox myArray(i)
Next i

End Sub

Function RAD(myR1 As Range, myR2 As Range) As Variant
Dim ADA() As Double
Dim RADA() As Integer
Dim i As Integer
Dim j As Integer

ReDim ADA(1 To myR1.Cells.Count)
ReDim RADA(1 To myR1.Cells.Count)

For i = LBound(ADA) To UBound(ADA)
ADA(i) = Abs(myR1(i).Value - myR2(i).Value)
Next i

For i = 1 To UBound(RADA)
RADA(i) = 1
For j = 1 To UBound(ADA)
RADA(i) = RADA(i) + IIf(ADA(i) < ADA(j), 1, 0)
Next j
Next i

RAD = RADA

End Function