efficient way of comparing whats already in a collection

[BrianMH]

Hi,

Was wondering as this has come up a few times. What is the most efficient to tell if a value is in a collection. I like SQL for its in statement.

In(1, 2, 3, 4 ,5, 7, 9)

So you can tell if value x is in an array with out having to cycle through the whole array.

Is there any simple function like this in vba?

For example you have a 100 workbooks and you want to cycle through them and copy all their sheets over if they don't already exist there.

I normally end up doing a loop through each workbook then looping through each sheet in the other workbook then setting a boolean and looping through my current workbook sheets collection and any of the sheets names match setting the boolean to true and then based on this copying the sheet and then continuing the other loops. This is a lot of loops and seems innefficient and long winded to type out.

Same goes for if you have 20 values for a combobox and 20 comboboxes and you don't want the values to repeat for each one you need to cycle through all the current values and not allow them.

Hope this is making sense. There are lots of times this situation comes up and there must be a better way of doing it than I have been.

[GTO]

Hi Brian,

I do not see a way of avoiding looping thru the wb's being opened, but as to checking against the one wb, why not use Dictionary? If I am understanding, after building the initial collection, you could just add keys as sheets are copied, and for checking, .Exists.

Is that any help?

Mark

[BrianMH]

Never used dictionaries before. Gives me a new thing to learn about.

[mdmackillop]

If this is a regular occurrence the add some code to each to maintain a database of each workbook and its contents. There will be an initial overhead in the setup, but a timesaver in the long run. You could then determine missing sheets and where they existed.

[Kenneth Hobs]

That question is too broad to really answer fully. Not all collections are the same. There are some tricks one can use to not iterate through them all sometimes.

Lets look at one type that you asked about, arrays. Iterating through all those elements goes very quickly. In the WordPerfect Office Suite, it comes with a programming language called PerfectScript though it can do VBA and other languages too. The IN operator in it and slices of arrays .., makes that a nice language for arrays. What the IN operator does in PerfectScript is to return the index or position of a value in the array for example. So, to do the index thing for arrays in VBA, I wrote this snippet:

'val is not case sensitive
Function Index(vArray() As Variant, val As Variant) As Long
On Error GoTo Minus1
Index = WorksheetFunction.Match(val, WorksheetFunction.Transpose(vArray), 0)
Exit Function
Minus1:
Index = -1
End Function

Speaking of collections, here is how to get an index in a VBA collection:


'This function tries to add an item to the collection, and returns the position of the item in the collection
Public Function GetIndex(c As Collection, Key As String) As Long
On Error Resume Next 'Adding an existing item causes an error, trap it
c.Add c.Count + 1, Key 'try to add item, store next index
On Error GoTo 0
GetIndex = -1 'set default value as "not found" - shouldn't be necessary as we should always find the item
'(unless, of course, the key we are trying to add is invalid, eg not a string)
GetIndex = c(Key) 'look up and return the index number of our item
End Function
Often, one might want to know if a worksheet exists:


Function Test_WorkSheetExists()
MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), _
vbInformation, "ActiveWorkbook.ActiveSheet"
End Function

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function

One might want to know if a workbook is open:

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

As you know, we iterated the userform control collection in thread: http://www.vbaexpress.com/forum/showthread.php?t=36790. There are some other methods that I iterated once and made control arrays. That makes it more efficient if you need a subset of the controls sometimes.

[BrianMH]

Wow I really like the way you used an error to say if a worksheet existed. That saves running through them all :)

[Paul_Hossler]

Another way


Option Explicit
Sub Test()
Dim C As New Collection

Call C.Add("Fluffy", "cat")
Call C.Add("Fido", "dog")
Call C.Add("Tweety", "bird")

If ItemExists(C, "cat") Then MsgBox "Cat = " & C("cat")
If ItemExists(C, "dog") Then MsgBox "Dog = " & C("dog")
If ItemExists(C, "zebra") Then MsgBox "Zebra = " & C("zebra")

Set C = Nothing

End Sub
'determines if a item by Key is in the collection
Function ItemExists(C As Collection, K As Variant) As Boolean
Dim i As Variant
i = Null
On Error Resume Next
i = C(CStr(K))

ItemExists = Not IsNull(i)
End Function



Paul