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