elements in an array

[troelsi]

Hi

What is the fastest and most elegant way to examnine how many elements there is in an array?
After I've loaded my data into an array I want to make a for next loop that runs over each element in the array. Therefor I need to know how many elements (non zeros) that my array contains.

Thanks

[Bob Phillips]

[vba]

For i = LBound(myArray) To UBound(myArray)
[/vba]


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[troelsi]

Thanks but that's not exactly what I'm looking for.

Dim myarray(999)

For i = 1 to 10

[troelsi]

thanks but that's not exactly what I'm looking for.

Dim myarray(999)

for i = 1 to 10
myarray(i) = 1
next i

This array contains 10 elements. Is there a shortcut to finding index number 11 as the first nonzero (null) value. Or do I have to use a for next loop..
for i = 1 to 999
if myarray(i)=0 then
upperbound = i-1
exit for
end if
next i

[Bob Phillips]

You will have to loop through the items, but see my earlier post, arrays might start at 0.


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[JonPeltier]

Arrays in VB and VBA may start at any value, so use the LBound to UBound approach.

[TonyJollans]

Dim myarray(999)

for i = 1 to 10
myarray(i) = 1
next i

This array contains 10 elements.

No. The array contains, by default, 1000 elements.

If you want to work with a dynamic array you should explicitly create it that way, for example ..
[vba]
Dim myarray()

' To add a single element
If (Not Not myarray) = 0 Then
ReDim myarray(1 To 1)
Else
ReDim myarray(1 To UBound(myarray) + 1)
End If
myarray(UBound(myarray)) = 2.71828

' add more as you need them

MsgBox "Array starts with element #" & LBound(myarray) & _
vbCr & " and ends with element #" & UBound(myarray)

[/vba]

[unmarkedhelicopter]

As for the fastest way ...
You could save 'some' time with binary division.
But it's a bit hit and miss.
How did you build the array ? (there is usually a clue in that)
Why whould you EVER create an array with 999/1000 elements if you were only going to populate (say) 400 ?

If you know that your array starts at 0 and goes to 1023 (say, adapt as required, this is 1024 values) you test 1024/2 (i.e. 512) if that is zero, then you know that your upper limit is greater than 512 (you've just saved half the test), if it was zero you know it was less than 512.
You then test 512 +/- 1024/4 (plus if > 512, minus if < 512) etc. etc.
In theory you could find ANY result in ONLY 10 tests (within 1024 !)

[mdmackillop]

I would be looking at the data to be loaded. Surely this can be flagged to find the last value > 0 added to the array.

[unmarkedhelicopter]

MD, yeah that's why I said "How did you build the array ? (there is usually a clue in that)" and In my testit would actually be 11 or 12 tests as you don't have a less than, greater than equal to so the final one would (depending on your coding and efficiency) need a supplimentary test, still 11 from a possible 1023 is an improvement

[mikerickson]

This works for any dimension array (1D, 2D, 3D..., any UBound, LBound):

Dim xVal As Variant, countOfZeros As Double
countOfZeros = 0
For Each xVal In myArray
    countOfZeros = countOfZeros + Abs(xVal = 0)
Next xVal

[TonyJollans]

That would be pretty inefficient in this instance - individually counting around 990 zeroes when you knew after the first how many there would be.

[Bob Phillips]

and why use another method, Abs, when a simple subtraction works fine

[troelsi]

Thanks to all for your suggestions. I'm gonna go wtih the dynamic arrays.

[TonyJollans]

Very sensible, if I may say so

[unmarkedhelicopter]

As an experiment, assuming that (for some reason) it was impossible to capture where the array turns to zero when you populate it, I wrote some code to do a comparison between testing with a loop and testing using binary division.
I've not spent a great deal of time with either so both methods 'could' possibly be improved upon but it shows a general trend.
To meet the OP's (original poster's) criteria, the code defaults to test an array of 999 elements to find where the values stop and zero's commence.
There are dialog boxes so that you can easily change the size of the array and the number of comparisons to be run.
Basically for a 999 array (1000 elements), for the loop you need on average 1503 lines of code to be executed (X*3 + 3, where X is the number of times through the loop, which should average to 500 for a 1000 element array), for the binary division you need 99 lines pretty much regardless of where the boundary occurs.
This show that though 'sometimes' the loop beats binary division it is rare and only occurs in this example when the value is less than 39.
Further, with a random sampling of values, binary division takes about 9% (or less) of the time required by the loop.
Have a play

Edit: Found a bug in the code which 'sometimes' prevented small arrays being solved !!!

[unmarkedhelicopter]

2 views were taken of the original file.
Here's the updated version.
On average (on my machine at least) the bin div code runs quiker than loop for any array bigger than 38 elements.

[Paul_Hossler]

If you start load data at the "beginning" of the array, you could use MATCH() to find the first element that contains zero.

[VBA]
Sub PHH1()
'general case of bounds
Dim A(100 To 999) As Long
Dim i As Long, N As Long

'fill the first part with data
For i = LBound(A) To 499
A(i) = i + 100
Next i

'get the element number of the first element = 0
N = Application.WorksheetFunction.Match(0, A, 0) + LBound(A) - 1

MsgBox "The first element = 0 in the array A(" & LBound(A) & "," & UBound(A) & ") is element number " & N

End Sub
[/VBA]


Paul

[unmarkedhelicopter]

... And the timing to achieve this ?

Edit 1:
I've run some tests and for 'some' reason Match does not return the correct result.

lnA = Application.WorksheetFunction.Match(0, lnar, 0)

when used in the array in my example :- lnar
with the values filled to 227. i.e. 227 = 42 and 228 = 0, Match returns 229 !!!!
Edit 3: (Duh ! Match would find the first zero, i.e. element 229 which when you consider 0 is element 1 is correct,
you find the first 0, add the Lbound and Subtract 2 !!!). God I'm stupid today !

Edit 2:
Well I've also done some timings ...
Match 'sometimes' returns a 1004 error 'Unable to get the Match property of the WorksheetFunction class'
But when it does run it takes approx 300 - 400% of the time required by the loop

[mdmackillop]

Then there is always the problem of zero in the data.
e.g.
1,12,22,0,15,45,0,0,0,0,0,..........