To declare or not to declare

[Alan Beban]

The following procedure works correctly to load arr2 with red, blue,
Blue, brown if CaseSensitive is True; and to load it with red, blue,
brown if CaseSensitive is False. When I attempt to declare the variable
x like so (which is commented out in the procedure)

If CaseSensitive Dim x As Dictionary Else Dim x As Collection

it throws a compile error--Duplicate declaration in current scope.

How can the variable x be appropriately declared?

Sub abc()
'This procedure requires project reference to
'the "Microsoft Scripting Runtime"

Dim arr, arr2, i As Long
'If CaseSensitive Dim x As Dictionary Else Dim x As Collection

CaseSensitive = True
'CaseSensitive = False

arr = Array("red", "blue", "Blue", "red", "brown")

If CaseSensitive Then Set x = New Dictionary Else Set x = New Collection

On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, key:=CStr(Elem)
Next
On Error GoTo 0

ReDim arr2(1 To x.Count)
i = 1
For Each Elem In x
arr2(i) = Elem
i = i + 1
Next
End Sub

Thanks,
Alan Beban

 

[Peter T]

Hi Alan,

This seems to avoid the duplicate decalaration problem but I can't make it
work correctly. Regardless of true/false it runs to the #Else, maybe you can
do something with it.

#If CaseSensitive Then
Dim x As Dictionary
Set x = New Dictionary
#Else
Dim x As Collection
Set x = New Collection
#End If

Regards,
Peter

 

[Tom Ogilvy]

Dim x as Object

 

[Tom Ogilvy]

#Const CaseSensitive = True
#If CaseSensitive Then
Dim x As Dictionary
Set x = New Dictionary
#Else
Dim x As Collection
Set x = New Collection
#End If

But the compiler constant CaseSensitive is not the same as the variable
casesensitive.

 

[Alan Beban]

#Const CaseSensitive = True
#If CaseSensitive Then
Dim x As Dictionary
Set x = New Dictionary
#Else
Dim x As Collection
Set x = New Collection
#End If

But the compiler constant CaseSensitive is not the same as the variable
casesensitive.

Thanks.

The choices seem to be something like the above, Dim x As Object, or
don't declare x. In this specific case, what's really wrong with the
third choice (which certainly streamlines the code, at least from a
visual standpoint)? And what are the advantages of something like the
above rather than Dim x As Object?

Thanks,
Alan Beban

 

[keepITcool]

Alan,

hmm.. nice discussion

What about :

not using the collection, just the dictionary.
using the dictionary comparemode for casesensitivity.

The speed gained by transferring a dictionary BACK to
the array (compared to redim/iterate for collection)
is probably compensating for the performance loss of
creating the non native dictionary.

I haven't done any performance testing.. but I plan to
test following:

Collection vs Dictionary.
Dictionary Early/Late binding.
Error Resume vs oDict.Exists


I'll let you know the findings.




Function MakeUnique(vIn, Optional CaseSensitive As Boolean, _
Optional Sorted As Integer)
'Sorted :0 No Sort,-1 Ascending, 1 Descending
Dim vOut, vItm, i&, j&, n&, oDict As Object

On Error Resume Next
vItm = UBound(vIn, 1)
If Err <> 0 Then
vOut = CVErr(xlErrValue)
GoTo TheEnd
End If

Set oDict = CreateObject("scripting.dictionary")
oDict.comparemode = Abs(Not CaseSensitive)

For Each vItm In vIn
'skip empty or nullstring
If Len(vItm) Then oDict.Add CStr(vItm), vItm
Next
On Error GoTo 0
n = oDict.Count
If VarType(vIn(LBound(vIn))) = vbString Then
'String comparison
vOut = oDict.Keys
If Sorted = 0 Then GoTo TheEnd
Sorted = Sorted \ Abs(Sorted)
For i = 0 To n - 2
For j = i To n - 1
'Note on sorting..aBcAbC goes in..
'0 => binary abcABC
'1 => text aABbcC
If StrComp(vOut(i), vOut(j), vbTextCompare) = Sorted Then
vItm = vOut(i): vOut(i) = vOut(j): vOut(j) = vItm
End If
Next
Next
Else
'numeric comparison
vOut = oDict.Items
If Sorted > 0 Then
For i = 0 To n - 2
For j = i To n - 1
If vOut(i) - vOut(j) < 0 Then
vItm = vOut(i): vOut(i) = vOut(j): vOut(j) = vItm
End If
Next
Next
ElseIf Sorted < 0 Then
For i = 0 To n - 2
For j = i To n - 1
If vOut(i) - vOut(j) > 0 Then
vItm = vOut(i): vOut(i) = vOut(j): vOut(j) = vItm
End If
Next
Next
End If
End If

TheEnd:
MakeUnique = vOut

End Function



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

A variant can hold an object, and that is in essence the third choice. The
only disadvantage would be the size of a variant and Option Explicit
problems if you don't declare it. I assume there would be a slight speed
impact, but for a single variable, I don't see any of the above factors as
really being of great relevance.

Someone else might have a stronger opinion.

 

[Stephen Bullen]

Hi KeepITcool,

compensating for the performance loss of
creating the non native dictionary.

FWIW, I don't think either the Collection or the Dictionary are
'native', so there's no performance loss. The Collection object is
provided by the VBA library that our projects reference, while the
Dictionary object is provided by the Scripting library that our
projects (can) reference. The only difference is that the VBA library
is already referenced for us by default in our projects.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

A variant can hold an object, and that is in essence the third choice. The
only disadvantage would be the size of a variant and Option Explicit
problems if you don't declare it. I assume there would be a slight speed
impact, but for a single variable, I don't see any of the above factors as
really being of great relevance.

Someone else might have a stronger opinion.

Thanks, Tom. I will probably use the Dim x As Object that you suggested
earlier; I suppose that's somewhat better from a size standpoint than no
declaration at all, and it avoids the Option Explicit complications. The
conditional constant approach is more cumbersome, particularly since in
the resulting procedure that the posted code will actually be part of,
the CaseSensitive variable will be a parameter of the procedure call.

Thanks for reviewing this,
Alan Beban

 

[keepITcool]

Stephen..
yep that makes sense to me..<g>

I saw that you've finished your new book
Professional Excel Development..

Pity I'll have to wait till FEB 4th for it's release


I've just run a few tests:

(For the purpose of finding unique items)

the Collection and the Dictionary
add items at practically the same speed.

One advantage of using a Dictionary would be that
the Dictionary can return a 0based array, (either items or keys)
whereas for the dictionary this array must created.

IF you need a 1 dimensional array of unique items
the dictionary is up to 50% faster than the collection.
(plus you have CaseSensitivity!)
======================================================

For the dictionary: on error resume next
works slightly faster then using .Exists(key)
before adding the itm.

tests done with a latebound dictionary

If you DONT need to return an array.. and prefer to "keep" the object..
the dictionary has a few other advantages like being able to CHANGE the
key.

Does it have any DISADVANTAGE?

1 I've heard that some companies disable scripting...

2 ??


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Stephen Bullen]

Hi KeepITcool,

I saw that you've finished your new book
Professional Excel Development..

Yes, At last!

Pity I'll have to wait till FEB 4th for it's release

That's Addison-Wesley's production schedule - there's quite a bit of
work for them to do once it's all been written, and rushing it results

tests done with a latebound dictionary

So an early-bound use will be even faster, as the late binding comms is
probably a significant part of this.

Does it have any DISADVANTAGE?

Yes - you can't insert items in the middle of it, so it isn't much use
if you need to maintain an orderable collection

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk