Below are two programs that - to my understanding - are the same. Why does
the first one generate an error message and the second one not? Such small
differences (there is a similar thing with the Collection.add function) often
incur hours of debugging to me and therefore I would love to understand them.

1st:

Dim sums As New Collection
Dim colors As New Collection
Dim products As New Collection

If ExistsInColl(sums, color) Then "do stuff"

2nd:

Dim sums, colors, products As New Collection

If ExistsInColl(sums, color) Then "do stuff"


the function that is called:

Public Function ExistsInColl(ByRef col As Collection, ByVal index As String)
As Boolean
On Error GoTo ErrNotInColl
Dim tmp
tmp = col(index)
ExistsInColl = True
Exit Function
ErrNotInColl:
On Error GoTo 0
ExistsInColl = False
End Function

dim i, j as integer declares i as a variant and j as an integer, so

Dim sums, colors, products As New Collection declares two variants (sums,
colors) and one collection.

Sam

"Fabian" wrote:

Below are two programs that - to my understanding - are the same. Why does
the first one generate an error message and the second one not? Such small
differences (there is a similar thing with the Collection.add function) often
incur hours of debugging to me and therefore I would love to understand them.

1st:

Dim sums As New Collection
Dim colors As New Collection
Dim products As New Collection

If ExistsInColl(sums, color) Then "do stuff"

2nd:

Dim sums, colors, products As New Collection

If ExistsInColl(sums, color) Then "do stuff"


the function that is called:

Public Function ExistsInColl(ByRef col As Collection, ByVal index As String)
As Boolean
On Error GoTo ErrNotInColl
Dim tmp
tmp = col(index)
ExistsInColl = True
Exit Function
ErrNotInColl:
On Error GoTo 0
ExistsInColl = False
End Function

This..

Dim sums, colors, products As New Collection

is NOT a shortcut for this...

Dim sums As New Collection
Dim colors As New Collection
Dim products As New Collection

In VB, you must declare (Dim) each variable as to its Data type individually
or else it declared as the default type of Variant. So, in the first quoted
declaration, only 'products' is declared as a New Collection... 'sums' and
'colors', since they are not individually declared, get declared as Variant.
I'm guessing the error you are getting is because the ExistsInColl
subroutine you are passing them to does not expect them to be Collections.
By the way, IF the variables should have been New Collections (remember, I
said *if*... I'm just trying to show you syntax here), the way to declare
them as such all on one line (so that they are individually declared) would
have been this way...

Dim sums As New Collection, colors As New Collection, products As New
Collection

The above line probably word-wrapped in your newsreader, but it should all
have been on one line. The declared data types do not all have to be the
same to use the single-line declaration. For example, this would be legal
syntax...

Dim Var1 As Long, Var2 As Double, Var3 As Range

Personally, I don't like to do single line declarations like this as I think
they are harder to read (think coming back to your program in six months to
modify it in some way; or, think in an office situation where someone else
is given the assignment to modify the code you wrote).

--
Rick (MVP - Excel)


"Fabian" wrote in message
...
Below are two programs that - to my understanding - are the same. Why does
the first one generate an error message and the second one not? Such small
differences (there is a similar thing with the Collection.add function)
often
incur hours of debugging to me and therefore I would love to understand
them.

1st:

Dim sums As New Collection
Dim colors As New Collection
Dim products As New Collection

If ExistsInColl(sums, color) Then "do stuff"

2nd:

Dim sums, colors, products As New Collection

If ExistsInColl(sums, color) Then "do stuff"


the function that is called:

Public Function ExistsInColl(ByRef col As Collection, ByVal index As
String)
As Boolean
On Error GoTo ErrNotInColl
Dim tmp
tmp = col(index)
ExistsInColl = True
Exit Function
ErrNotInColl:
On Error GoTo 0
ExistsInColl = False
End Function

in older versions of BASIC

Dim sums, colors, products As New Collection
was good enough to declare each variable as a Collection. BUT not now

so this
Dim sums, colors, products As New Collection
is akin to this
Dim sums As Variant
Dim colors As Variant
Dim products As New Collection


Also, using NEW this is not like by many developers as we can't predict when
the object is created.

using
Dim products As Collection

then
SET products = New Collection
leave your code in control




"Fabian" wrote in message
...
Below are two programs that - to my understanding - are the same. Why does
the first one generate an error message and the second one not? Such small
differences (there is a similar thing with the Collection.add function)
often
incur hours of debugging to me and therefore I would love to understand
them.

1st:

Dim sums As New Collection
Dim colors As New Collection
Dim products As New Collection

If ExistsInColl(sums, color) Then "do stuff"

2nd:

Dim sums, colors, products As New Collection

If ExistsInColl(sums, color) Then "do stuff"


the function that is called:

Public Function ExistsInColl(ByRef col As Collection, ByVal index As
String)
As Boolean
On Error GoTo ErrNotInColl
Dim tmp
tmp = col(index)
ExistsInColl = True
Exit Function
ErrNotInColl:
On Error GoTo 0
ExistsInColl = False
End Function