Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
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 "Alan Beban" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
Dim x as Object
-- Regards, Tom Ogilvy "Alan Beban" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
#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. -- Regards, Tom Ogilvy "Peter T" <peter_t@discussions wrote in message ... 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 "Alan Beban" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
Tom Ogilvy wrote:
#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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
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 Alan Beban wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
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. -- Regards, Tom Ogilvy "Alan Beban" wrote in message ... Tom Ogilvy wrote: #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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
Tom Ogilvy wrote:
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
To declare or not to declare
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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declare variable as what? | Excel Programming | |||
Declare Variable | Excel Programming | |||
Declare Variable | Excel Programming | |||
Declare Variable | Excel Programming | |||
Declare an Array() ???? | Excel Programming |