Hi

Working with arrays i a lot faster than worksheet functions (I suppose
so...). I have a few questions about arrays.

1st question
Is it possible to assign a value to multiple elements of an array: I'd like
to do something like that:

a(1 to 10) = 0

2nd question:
Is it possible to get this faster, without loops:

For i = 1 To UBound(a)
If a(i) = strValue Then
Matches = aMatches + 1
End If
Next i

3rd question:
Is it possible to use worksheets functions with arrays, something like that:

matches = Application.CountIf("abc", aValues, 0)


Maybe there is a tuturial available how to work with arrays most efficient.

Tom

1) No, you cannot do that
2) Using a loop is the normal way to do that, and it's the most efficient and easy to debug
3) I don't know a way to do that in the case you described


----- Tom wrote: ----

H

Working with arrays i a lot faster than worksheet functions (I suppos
so...). I have a few questions about arrays

1st questio
Is it possible to assign a value to multiple elements of an array: I'd lik
to do something like that

a(1 to 10) =

2nd question
Is it possible to get this faster, without loops

For i = 1 To UBound(a
If a(i) = strValue The
Matches = aMatches +
End I
Next

3rd question
Is it possible to use worksheets functions with arrays, something like that

matches = Application.CountIf("abc", aValues, 0


Maybe there is a tuturial available how to work with arrays most efficient

To

Hi Tom

1st question

One way:

Sub Test()
'Leo Heuser, 25 Nov. 2003
Dim Arr As Variant

Arr = [(Row(1:10)^0)*5]

Arr = Application.WorksheetFunction.Transpose(Arr)

End Sub


Arr = [(Row(1:10)^0)*5]

creates a 2-dimensional 1-based array
Arr(1,1) to Arr(10,1) filled with 5's

Arr = Application.WorksheetFunction.Transpose(Ar)

transposes it to a 1-dimensional 1-based array
Arr(1) to Arr(10)

In Excel 97 and 2000 using Transpose as shown
there is a limit around 5400 elements. This limitation
doesn't exist in Excel 2002 and on.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Tom" skrev i en meddelelse
...
Hi

Working with arrays i a lot faster than worksheet functions (I suppose
so...). I have a few questions about arrays.

1st question
Is it possible to assign a value to multiple elements of an array: I'd
like
to do something like that:

a(1 to 10) = 0

2nd question:
Is it possible to get this faster, without loops:

For i = 1 To UBound(a)
If a(i) = strValue Then
Matches = aMatches + 1
End If
Next i

3rd question:
Is it possible to use worksheets functions with arrays, something like
that:

matches = Application.CountIf("abc", aValues, 0)


Maybe there is a tuturial available how to work with arrays most
efficient.

Tom