Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges and Arrays in Excel VBA...
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges and Arrays in Excel VBA...
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 "a.riva@UCL" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranges and Arrays in Excel VBA | Excel Worksheet Functions | |||
ranges as arrays | Excel Programming | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Ranges and Arrays | Excel Programming | |||
Help with Arrays & Ranges in VBA | Excel Programming |