ExcelBanter - Working with arrays

The easy one first:

#1:
Dim myArr As Variant
myArr = Array(1, 1, 2, "z", "z", 3, 3, 4, 4, 4, 1, 1, 4, 5)
MsgBox Application.Sum(myArr)

#2. I think that you'll have to loop through the array.

Option Explicit
Sub testme02()

Dim myArr1 As Variant
Dim myArr2() As Variant
Dim iCtr As Long
Dim eCtr As Long

myArr1 = Array(1, 1, 2, "z", "z", 3, 3, 4, 4, 4, 1, 1, 4, 5)

eCtr = 0
For iCtr = LBound(myArr1) To UBound(myArr1)
If Application.IsNumber(myArr1(iCtr)) Then
eCtr = eCtr + 1
ReDim Preserve myArr2(1 To eCtr)
myArr2(eCtr) = myArr1(iCtr)
End If
Next iCtr

If eCtr = 0 Then
MsgBox "No elements"
Else
For iCtr = LBound(myArr2) To UBound(myArr2)
MsgBox iCtr & "--" & myArr2(iCtr)
Next iCtr
End If

End Sub

#3. You can build the array, but check using application.match(). If that
returns an error, then the value isn't in the second array.

Option Explicit
Sub testme03()

Dim myArr1 As Variant
Dim myArr2() As Variant
Dim iCtr As Long
Dim eCtr As Long

myArr1 = Array(1, 1, 2, "z", "z", 3, 3, 4, 4, 4, 1, 1, 4, 5)

ReDim myArr2(1 To 1)
myArr2(1) = myArr1(1)

eCtr = 1
For iCtr = LBound(myArr1) + 1 To UBound(myArr1)
If IsError(Application.Match(myArr1(iCtr), myArr2, 0)) Then
eCtr = eCtr + 1
ReDim Preserve myArr2(1 To eCtr)
myArr2(eCtr) = myArr1(iCtr)
End If
Next iCtr

For iCtr = LBound(myArr2) To UBound(myArr2)
MsgBox iCtr & "--" & myArr2(iCtr)
Next iCtr

End Sub

There are lots of other techniques. You could use a Collection or a Dictionary
object. If you want to try the collection, you may want to look at John
Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip47.htm

His routine has an option sort, too.

filo666 wrote:

Hi, I have some operations that i think VB has some easier way to accomplish
or may be a function:

1)The sum of all the array elements
2)Get just the array elements different of "" or 0
3)Get juste the array elements that they are not repeated (i mean
arr1(1,1,2,z,z,3,3,4,4,4,1,1,4,5) to arr1(1,2,3,4,5,z)

TIA

--

Dave Peterson