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.

It's untested...
RankAbsDiff(i) =
Application.Rank(AbsDiff(i), Application.Transpose(AbsDiff),1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"a.riva@UCL"
wrote in message 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.

It's difficult to test potential solutions without the actual AbsDiff
code you're using.

Second question, why aren't you doing this with built-in worksheet
functions?



On Oct 15, 11:01 am, "a.riva@UCL" wrote:
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.

ilia wrote:
It's difficult to test potential solutions without the actual AbsDiff
code you're using.

Second question, why aren't you doing this with built-in worksheet
functions?



What built-in worksheet functions are relevant besides the RANK function
that the Op is using?

Alan Beban

OP did not state what other functions he expects to be using. My
question is why is it done in VBA, up to the RankAbsDiff stage.

I don't think your code will work as a Function, as you are assigning
values to a range, which Functions notably cannot do.


{=ABS(Set1-Set2)} returns an array of absolute differences.

Presuming that's in a range by itself, named AbsDiff,

{=RANK(AbsDiff,AbsDiff,1)} returns an array of ranks corresponding in
index. Or, use the non-array equivalent. Either takes about 3
minutes to calc for a 50,000-set of random doubles (x2).





On Oct 15, 5:19 pm, Alan Beban wrote:
ilia wrote:
It's difficult to test potential solutions without the actual AbsDiff
code you're using.

Second question, why aren't you doing this with built-in worksheet
functions?

What built-in worksheet functions are relevant besides the RANK function
that the Op is using?

Alan Beban

Dim Range3 As Range
Set Range3 = Range("A1").Resize(1,UBound(AbsDiff)-LBound(AbsDiff)+1)
'Or use some other available range start than A1
Range3.Value = AbsDiff
RankAbsDiff(i) = Application.Rank(AbsDiff(i), Range3)

Alan Beban

a.riva@UCL wrote:
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.

Antonio,

You might be interested in considering the functions in the freely
downloadable file at http://home.pacbell.net/beban. They have a lot of
the otherwise necessary looping built-in. E.g.:

Set1 = Range1 that works for a Variant() array does not work if Set1 is
of type Double() [or any other builkt-in type besides Variant()]; it
needs to be looped in.

Assign Range1, Set1

works; the Assign function from the Web site has the looping built-in.

Similarly, the ArrayAdd function has built-in looping so that

Diff = ArrayAdd(Set1, Set2, False) will load Diff with the differences
between the elements of Set1 and Set2.

Regards,
Alan Beban

"a.riva@UCL" wrote...
....
I'm trying to program a new excel function that I need for my job
....
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))
....
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:
....

Up to this point you don't need to use VBA at all. If your two source
data ranges were named X and Y, you could select another range of the
same size and shape as X and Y and enter the array formula

=MMULT(--(ABS(X-Y)<TRANSPOSE(ABS(X-Y))),ROW(X)^0)+1

which would rank the highest absolute difference as 1. Ties would
receive the same rank. It's likely any subsequent calculations you
need to perform could also be done entirely using worksheet formulas,
possibly involving array formulas.

Thank you all guys :-)

I know that I could use formulas (maybe array formulas) in the Excel
worksheet, instead of using VBA. The fact is that sometimes I have
huge tables of values, and having a formula that does all the job
without the need of inserting new columns in the worksheet would be
ideal. I'll try some of your suggestion :-)

Just a quick question, general about VBA: does VBA, in general, assign
an array to a range without writing values in the excel worksheet? I
mean, Double, Single, Long, Variant, etc. etc. are all type of
variables. But Range is different, isn't it?

Thanks again :-)

Antonio.

"a.riva@UCL" wrote...
. . . The fact is that sometimes I have
huge tables of values, and having a formula that does all the job
without the need of inserting new columns in the worksheet would
be ideal. I'll try some of your suggestion :-)

That being the case, you may want to consider using something other
than Excel for statistical analysis. Excel itself isn't particularly
efficient with large datasets, and VBA is usually slower still.

If you really believe you must use VBA, then don't call worksheet
functions unnecessarily. It'll slow down your VBA code.


Function foo(X As Range, Y As Range) As Variant
Dim i As Long, j As Long, k As Long, n As Long
Dim D As Variant, R As Variant

'check that X and Y are both column vectors of same size
If X.Areas(1).Rows.Count < Y.Areas(1).Rows.Count Or _
X.Areas(1).Columns.Count 1 Or _
Y.Areas(1).Columns.Count 1 Then
foo = CVErr(xlErrValue)
Exit Function
End If

n = X.Areas(1).Rows.Count

D = X.Areas(1).Value2
R = Y.Areas(1).Value2

'store abs diffs in D
For i = 1 To n
D(i, 1) = Abs(D(i, 1) - R(i, 1))
Next i

'load ranks into R
For i = 1 To n
k = 1
For j = 1 To n
If D(i, 1) < D(j, 1) Then k = k + 1
Next j
R(i, 1) = k
Next i

foo = R
End Function


Just a quick question, general about VBA: does VBA, in general,
assign an array to a range without writing values in the excel
worksheet? I mean, Double, Single, Long, Variant, etc. etc. are
all type of variables. But Range is different, isn't it?

A Range object is a collection of cells. The .Value and .Value2
properties of single area Range objects are arrays. Excel cell
contents map into exactly 5 VBA data types: Variant of subtype Empty
for blank cells and Error for cells evaluating to errors, String for
cells evaluating to text, Boolean for cells evaluating to TRUE or
FALSE, Double for cells evaluating to numbers (numbers in Excel are
**ALWAYS** double precision reals), and, for .Value but not .Value2,
Date for cells evaluating to numbers AND formatted as date/time.

So if you have a 1D or 2D VBA array of variants, when you assign that
array to an Excel range, Excel will store Variant/Error values as
their particular error value (#NUM!, #DIV/0!, #NULL!, #NAME?, #REF!,
#VALUE! or #N/A), Boolean values as their corresponding Excel boolean
values, String values as text, Date values as numbers with OS standard
date/time formatting, and any other numeric data type as doubles. Any
other VBA type would be converted to #VALUE!.

Yeah... I know I should use some other statistical softwares... Excel
is not the best, but in terms of data storage for subsequent analyses
it gives a lot of flexibility. It's just a matter of copying and
pasting my columns in on other application, even if sometimes I
believe everyone would just like to apply a formula and do everything
in just one application...

Anyway :-)

Thanks a lot for your code! It's great.
And thanks for the explanation about ranges and arrays. I didn't find
it so clear anywhere!

Have a great day!

Antonio.


On 16 Oct, 19:03, Harlan Grove wrote:
"a.riva@UCL" wrote...
. . . The fact is that sometimes I have
huge tables of values, and having a formula that does all the job
without the need of inserting new columns in the worksheet would
be ideal. I'll try some of your suggestion :-)

That being the case, you may want to consider using something other
than Excel for statistical analysis. Excel itself isn't particularly
efficient with large datasets, and VBA is usually slower still.

If you really believe you must use VBA, then don't call worksheet
functions unnecessarily. It'll slow down your VBA code.

Function foo(X As Range, Y As Range) As Variant
Dim i As Long, j As Long, k As Long, n As Long
Dim D As Variant, R As Variant

'check that X and Y are both column vectors of same size
If X.Areas(1).Rows.Count < Y.Areas(1).Rows.Count Or _
X.Areas(1).Columns.Count 1 Or _
Y.Areas(1).Columns.Count 1 Then
foo = CVErr(xlErrValue)
Exit Function
End If

n = X.Areas(1).Rows.Count

D = X.Areas(1).Value2
R = Y.Areas(1).Value2

'store abs diffs in D
For i = 1 To n
D(i, 1) = Abs(D(i, 1) - R(i, 1))
Next i

'load ranks into R
For i = 1 To n
k = 1
For j = 1 To n
If D(i, 1) < D(j, 1) Then k = k + 1
Next j
R(i, 1) = k
Next i

foo = R
End Function

Just a quick question, general about VBA: does VBA, in general,
assign an array to a range without writing values in the excel
worksheet? I mean, Double, Single, Long, Variant, etc. etc. are
all type of variables. But Range is different, isn't it?

A Range object is a collection of cells. The .Value and .Value2
properties of single area Range objects are arrays. Excel cell
contents map into exactly 5 VBA data types: Variant of subtype Empty
for blank cells and Error for cells evaluating to errors, String for
cells evaluating to text, Boolean for cells evaluating to TRUE or
FALSE, Double for cells evaluating to numbers (numbers in Excel are
**ALWAYS** double precision reals), and, for .Value but not .Value2,
Date for cells evaluating to numbers AND formatted as date/time.

So if you have a 1D or 2D VBA array of variants, when you assign that
array to an Excel range, Excel will store Variant/Error values as
their particular error value (#NUM!, #DIV/0!, #NULL!, #NAME?, #REF!,
#VALUE! or #N/A), Boolean values as their corresponding Excel boolean
values, String values as text, Date values as numbers with OS standard
date/time formatting, and any other numeric data type as doubles. Any
other VBA type would be converted to #VALUE!.

a.riva@UCL wrote:
. . . .
Thanks a lot for your code! It's great.
And thanks for the explanation about ranges and arrays. I didn't find
it so clear anywhere!

Have a great day!

Antonio.

Just a quick question, general about VBA: does VBA, in general,
assign an array to a range without writing values in the excel
worksheet? I mean, Double, Single, Long, Variant, etc. etc. are
all type of variables. But Range is different, isn't it?

A Range object is a collection of cells. . . .

One slight correction, perhaps semantic. Although a Range object is a
"collection" of cells, it is not an Excel Collection object. It's
similar, but there are some differences, perhaps not relevant to your
inquiry.

Alan Beban