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.

 

[Jim Cone]

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.

 

[ilia]

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?

 

[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

 

[Alan Beban]

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

 

[Alan Beban]

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

 

[ilia]

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).

 

[Harlan Grove]

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.

 

[a.riva@UCL]

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.

 

[Harlan Grove]

. . . 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]

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.

 

[Alan Beban]

. . . .
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.

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

 

[a.riva@UCL]

Thanks for all the suggestions!

Now I have an other question...

I have my usual option-based-1 array1 in VBA, which contains x
numbers. Some of them are repeated. What I would like to do is
creating an other option-based-1 array, let's call it array2, which
contains the numbers of occurrences of each of the repeated elements
of array1 within array1... I'm struggling to find a solution...

For example:

option-based-1 array1 is (1, 2, 4, 6, 5, 4, 7, 2, 3, 2, 3) --> I
cannot sort the array.

I think that the procedure should do the following operation: it
detects how many items are repeated in "array1", and for each of this
repeated items stores in a new array "array2" a number corresponding
to the number of its occurrences.

For example, in array1 the procedure detects that there are n=3 items
which occur more than once (they are "2", "4" and "3"). Then it ReDims
array2 (1 to n), and for i=1 to n it gives to array2(i) the values of:

i=1 --> array2(1) = 3 (occurrences of "2"),
i=2 --> array2(2) = 2 (occurrences of "4"),
i=3 --> array2(3) = 2 (occurrences of "3").

Can somebody help me?

Thanks in advance

Antonio.

 

[ilia]

I'm not sure whether this is the most efficient solution but it
works. Change iIncrement to suit the size of arrays and number of
duplicates you expect.

Private Function countStuff(array1() As Long) As Long()
' frequency of ReDim
Const iIncrement As Long = 10

Dim iLoc As Long, iCurrDim As Long
Dim iPos1 As Long, iPos2 As Long
Dim count As Long
Dim array2() As Long

iCurrDim = iIncrement

ReDim array2(1 To iCurrDim)

iLoc = 0
' look through the array1
For iPos1 = LBound(array1) To UBound(array1)
count = 0
' check to see whether this value has already been counted
If iPos1 > 1 Then
For iPos2 = LBound(array1) To iPos1 - 1
If array1(iPos2) = array1(iPos1) Then
' -1 will prevent counting in next step
count = -1
End If
Next iPos2
End If
' only count if value not duplicate
If count <> -1 Then
' look for future occurences
For iPos2 = iPos1 To UBound(array1)
If array1(iPos1) = array1(iPos2) Then
count = count + 1
End If
Next iPos2
'only add if count > 1
If count > 1 Then
iLoc = iLoc + 1
array2(iLoc) = count
' ReDim if necessary
If iLoc = iCurrDim Then
iCurrDim = iCurrDim + iIncrement
ReDim Preserve array2(1 To iCurrDim)
End If
End If
End If
Next iPos1

' downsize if too big
If iLoc < iCurrDim Then
ReDim Preserve array2(1 To iLoc)
End If

' assign return value
countStuff = array2
End Function

 

[a.riva@UCL]

I'm not sure whether this is the most efficient solution but it
works. Change iIncrement to suit the size of arrays and number of
duplicates you expect.

Private Function countStuff(array1() As Long) As Long()
' frequency of ReDim
Const iIncrement As Long = 10

Dim iLoc As Long, iCurrDim As Long
Dim iPos1 As Long, iPos2 As Long
Dim count As Long
Dim array2() As Long

iCurrDim = iIncrement

ReDim array2(1 To iCurrDim)

iLoc = 0
' look through the array1
For iPos1 = LBound(array1) To UBound(array1)
count = 0
' check to see whether this value has already been counted
If iPos1 > 1 Then
For iPos2 = LBound(array1) To iPos1 - 1
If array1(iPos2) = array1(iPos1) Then
' -1 will prevent counting in next step
count = -1
End If
Next iPos2
End If
' only count if value not duplicate
If count <> -1 Then
' look for future occurences
For iPos2 = iPos1 To UBound(array1)
If array1(iPos1) = array1(iPos2) Then
count = count + 1
End If
Next iPos2
'only add if count > 1
If count > 1 Then
iLoc = iLoc + 1
array2(iLoc) = count
' ReDim if necessary
If iLoc = iCurrDim Then
iCurrDim = iCurrDim + iIncrement
ReDim Preserve array2(1 To iCurrDim)
End If
End If
End If
Next iPos1

' downsize if too big
If iLoc < iCurrDim Then
ReDim Preserve array2(1 To iLoc)
End If

' assign return value
countStuff = array2
End Function













- Show quoted text -

Hi Ilia.

This seems pretty clear. I'll adapt ilncrement and I'll let you
know

Thanks again,

Antonio.

 

[Chip Pearson]

Try something like the following. Arr1 is the input array of numbers.
Populate that array any way you want. At the end of the procedure, Arr2 will
contain the count of each element in Arr1, such that Arr2(N) is the count of
elements equal to N in Arr1. In other words Arr2(Arr1(N)) equals the count
of the element in Arr1(N). In the example below, Arr2(3) equal 3 because
there are three 3's in Arr1. Arr2(Arr1(3)) returns 1 because Arr1(3) is a
five, and there is only one 5 in Arr1.

Sub ArrayElementCounts()

Dim Arr1(1 To 10) As Long
Dim Arr2() As Long
Dim N As Long
Dim ArrMax As Long
Dim ArrMin As Long

''''''''''''''''''''''''''''''''''''
' Populate Arr1 any way you want.
''''''''''''''''''''''''''''''''''''
Arr1(1) = 1
Arr1(2) = 3
Arr1(3) = 5
Arr1(4) = 3
Arr1(5) = 2
Arr1(6) = 3
Arr1(7) = 2
Arr1(8) = 7
Arr1(9) = 7
Arr1(10) = 9

'''''''''''''''''''''''''''''''''''''
' Get the Min and Max values of Arr1
'''''''''''''''''''''''''''''''''''''
ArrMin = Application.Min(Arr1)
ArrMax = Application.Max(Arr1)

'''''''''''''''''''''''''''''''''''''
' Redim Arr2 from Min to Max
'''''''''''''''''''''''''''''''''''''
ReDim Arr2(ArrMin To ArrMax)
For N = LBound(Arr1) To UBound(Arr1)
'''''''''''''''''''''''''''''''''''''''''''
' Increment element Arr1(N) of Arr2
'''''''''''''''''''''''''''''''''''''''''''
Arr2(Arr1(N)) = Arr2(Arr1(N)) + 1
Next N
''''''''''''''''''''''''''''''''''''''''
' Display the results. Do whatever you
' want with the result array Arr2.
''''''''''''''''''''''''''''''''''''''''
For N = LBound(Arr2) To UBound(Arr2)
Debug.Print "Value: " & N, "Count: " & Arr2(N)
Next N

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)