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]

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



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

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)

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


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



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

[unmarkedhelicopter]

Then there is always the problem of zero in the data.
e.g.
1,12,22,0,15,45,0,0,0,0,0,..........True ...
I consider 0 to be valid data ... but ...
Given that this is the case ALL of the above solutions would fail !!!
The OP asked for the first non-zero value to determine the 'valid' size of his array upto 999

Edit: for bad spelling :(

[rory]

Match returns a 1-based index into an array so you would need to adjust for whatever the Lbound of the array actually is. The reason you get an error is because sometimes your array does not contain a zero.
I suspect that there is no definitive answer to this - just as with sorting arrays, the quickest way will depend on how large the array is.

[unmarkedhelicopter]

Yeah, Paul had adjusted ... just not quite enough and I was blind enough not to see it also (I edited my post when I did)
As for the array possibly not containing a zero, I hadn't thought of that, well spotted, so that's the cause for the 1004 error !
I suppose you could just extend the array and add a zero, just in case :)
or just test the last value, if it's zero :- do the test, if it's not :- you've found the answer :) :) :)

[unmarkedhelicopter]

Found another problem with Match.
As it is a worksheet function, the array it expects seems to have an upper limit of 65536 elements, I wonder where that comes from ??? :)
It may just be my Excel 2002 but I would guess that anyone without 2007 would have the same problem having said that I've done a 20,000,000 array size with loops and bin. div. No problem - try that in 2007 with Match ... and backward compatibility :- forget it !
Still looking for a faster way on a 999 array (as OP requested) than bin. div. :) :) :)

[rory]

Faster way: = don't define an array larger than you actually need! Then testing time = 0... :)

[unmarkedhelicopter]

Rory, first:- which of the 6 counties ?
Yeah ! I too have many loops to test for inclusion of data, if it's good redim preserve the array at ubound + 1 etc. and thinking about it that way it must take ages over the course of building the array so if you know the upper limit build it that big and trim it afterwards, having said that I know when I exit the loop so I know exactly how far I've gone so again, don't need to test. It's a lose - lose situation when it comes to testing, but this is more of an interlectual exercise ;)

[rory]

My family is from Antrim originally. (I'm not really from anywhere in particular)

[Zack Barresse]

For multi-dimension array's..

Private Function CountNumOfElement(ByVal buf) As String
Dim iDims As Long, iInt As Long, iRows As Long, iCols As Long, lb As Long
Dim DummyArr() As Variant
iDims = 1: iRows = 1
If TypeName(buf) Like "Variant()" Then
DummyArr = buf
iDims = GetNumberOfDim(DummyArr)
End If
For iInt = 1 To iDims
lb = LBound(buf, iInt)
iCols = UBound(buf, iInt)
iRows = iRows * (iCols - lb + 1)
Next
CountNumOfElement = CStr(iRows & "," & iCols)
End Function

Private Function GetNumberOfDim(ByRef vMultiDimArr As Variant) As Long
'http://puremis.net/excel/code/076.shtml
Dim i As Long, j As Long, k As Long
If VarType(vMultiDimArr) & vbArray = 0 Then
GetNumberOfDim = -1
Exit Function
End If
RtlMoveMemory i, ByVal VarPtr(vMultiDimArr) + 8, 4
RtlMoveMemory j, ByVal i, 4
RtlMoveMemory k, ByVal j, 2 'Get Number of Dimensions
GetNumberOfDim = k
End Function

I know it's kind of redundant, but I was playing one day with unique array items...
Option Explicit

Declare Sub RtlMoveMemory Lib "kernel32" ( _
Destination As Any, _
Source As Any, _
ByVal Length As Long)

Sub testUL()
Dim x, y, s As String
If Selection.Cells.Count = 1 Then
s = Selection
GoTo OnlyOneItem
End If
x = UniqueList(Selection)
For Each y In x
s = s & y & vbNewLine
Next y
OnlyOneItem:
MsgBox s
End Sub

Sub testUL2()
Dim x As Variant, y As Variant, s As String
x = UniqueList(Array("a", "a", "b", "b", "B", 1, 2, 3))
For Each y In x
s = s & y & vbNewLine
Next y
MsgBox s
End Sub
Function UniqueList(varInput As Variant) As Variant
Dim Dic As Object, dicItem As Object, blnArray As Boolean
Dim aTemp As Variant, arrUnique() As Variant, vItem As Variant
Dim iRow As Long, iCol As Long, iDim() As String, iCnt As Long
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = vbTextCompare
If TypeName(varInput) = "Range" Then aTemp = varInput.value
If IsEmpty(aTemp) Then aTemp = varInput: blnArray = True
iDim = Split(CountNumOfElement(aTemp), ",")
iCnt = 1
For iCol = 1 To iDim(UBound(iDim))
If blnArray = True Then
If Not Dic.exists(aTemp(iCol)) Then
If IsEmpty(aTemp(iCol)) = False Then
Dic.Add aTemp(iCol), iCnt
End If
iCnt = iCnt + 1
End If
Else
For iRow = 1 To iDim(LBound(iDim)) / iDim(UBound(iDim))
If Not Dic.exists(aTemp(iRow, iCol)) Then
If IsEmpty(aTemp(iRow, iCol)) = False Then
Dic.Add aTemp(iRow, iCol), iCnt
End If
iCnt = iCnt + 1
End If
Next iRow
End If
Next iCol
iCnt = 1
For Each vItem In Dic
ReDim Preserve arrUnique(1 To iCnt)
arrUnique(iCnt) = vItem
iCnt = iCnt + 1
Next vItem
UniqueList = arrUnique
End Function

Private Function CountNumOfElement(ByVal buf) As String
Dim iDims As Long, iInt As Long, iRows As Long, iCols As Long, lb As Long
Dim DummyArr() As Variant
iDims = 1: iRows = 1
If TypeName(buf) Like "Variant()" Then
DummyArr = buf
iDims = GetNumberOfDim(DummyArr)
End If
For iInt = 1 To iDims
lb = LBound(buf, iInt)
iCols = UBound(buf, iInt)
iRows = iRows * (iCols - lb + 1)
Next
CountNumOfElement = CStr(iRows & "," & iCols)
End Function

Private Function GetNumberOfDim(ByRef vMultiDimArr As Variant) As Long
'http://puremis.net/excel/code/076.shtml
Dim i As Long, j As Long, k As Long
If VarType(vMultiDimArr) & vbArray = 0 Then
GetNumberOfDim = -1
Exit Function
End If
RtlMoveMemory i, ByVal VarPtr(vMultiDimArr) + 8, 4
RtlMoveMemory j, ByVal i, 4
RtlMoveMemory k, ByVal j, 2 'Get Number of Dimensions
GetNumberOfDim = k
End Function

HTH