Anyone happen to have book: "VBA Developers Handbook" pub Sybex?

[mdlueck]

I came across reference to this old book:

VBA Developer's Handbook, 2nd Edition
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0782129781,miniSiteCd-SYBEX.html

I found it via this page:
"The List Class"
http://msdn.microsoft.com/en-us/library/aa227565(v=vs.60).aspx

It looks like on the accompanying CD was sample files:
http://msdn.microsoft.com/en-us/library/aa227473(v=vs.60).aspx
LIST.CLS / LISTITEM.CLS / LISTTEST.BAS

Does anyone happen to know where to obtain that example code from? TIA!

 

[mdlueck]

Elaborating a bit further... I am puzzled by this text in the book:

http://msdn.microsoft.com/en-us/library/aa227565(v=vs.60).aspx (Bottom of page...)

Why Use a Linked List?

That's a good question, because the native VBA Collection object provides much of the same functionality as a linked list, without the effort. Internally, collections are stored as a complex linked list, with links in both directions (instead of only one), and the data structure also includes pointers that make it possible to traverse the collection as though it were a binary tree. This way, VBA can traverse the collection forward and backward, and it can find items quickly. (Binary trees provide very quick random access to elements in the data structure.)

I am looking at the Collection class in A2007 VBA Object Browser. I see "Members of 'Collection'" as Add / Count / Item / Remove.

I do not see provisions for walking the collection in either direction.

All I am aware of with VBA core Collection classes are that you have to know the Key of an item you wish to retrieve. I would expect to see methods such as Prev / Next if such capabilities to enumerate through the Collection's contents were supported. No?

Perhaps is For Each / Next enumeration supported?

 

[DrallocD]

Firstly, I don't have the book, sorry.

You can enumerate collections with For Each but you have to treat the items as an object or variant.

You can move up and dows with indexers assuming the collection is in the correct order. The problem is that you can't insert into the middle of a collection the same way you can into a linked list.

I would be curious if anyone has worked out the point of specifying Next and Previous parameters on the collection add method since I can't see how to change it once set.

 

[ChrisO]

Private Sub Form_Open(ByRef intCancel As Integer)
    Dim lngI              As Long
    Dim vntItem           As Variant
    Dim colThisCollection As Collection
    
    Set colThisCollection = New Collection
    
    For lngI = 1 To 6
        colThisCollection.Add Item:=Me("Label" & CStr(lngI)), Key:=CStr(lngI)
    Next lngI

    [color=green]' Insert Label7 before Item 3.[/color]
    colThisCollection.Add Item:=Me("Label7"), Key:=CStr(7), Before:=3
    
    [color=green]' Insert Label8 after Item 3.[/color]
    colThisCollection.Add Item:=Me("Label8"), Key:=CStr(8), After:=3
    
    For Each vntItem In colThisCollection
        MsgBox vntItem.Name
    Next vntItem

End Sub

 

[DrallocD]

This is useful to know - Thanks!

 

[ChrisO]

I’ll make a guess here but I think there is no previous or next because current has not been defined.

Examples:-
After loading a collection is the current the first or last insertion?
If we insert a new entry should that set the current?
If we delete an entry what becomes current?

This last question is important to answer. If we delete the first or last entry the current could become below or above the bounds of the collection.

It seems that current needs to be defined for a particular usage. Once current can be defined then previous and next can be controlled. That would involve employing a separate Static index which is meticulously maintained even under an error condition.

So I’m thinking that previous and next methods were not included because current can only be defined by the programmer and the definition of current could change.

Chris.

 

[mdlueck]

You can enumerate collections with For Each but you have to treat the items as an object or variant.

Then that suits my current need just fine.

This time I need to be able to enumerate through the complete collection in order to save it to the database table.

The other places I use a custom collection class, I only need to access the in-memory table by knowing a specific object's key. The memory does not need to be dumped and saved back to a table in all other cases.

Oh, and yes, easy enough to know my custom object storage class and define the variable to fetch out objects as that specific type.

Thank you, DrallocD!

 

[mdlueck]

So I’m thinking that previous and next methods were not included because current can only be defined by the programmer and the definition of current could change.

That does make sense.

Just that as-is, it is not apparent that one is able to enumerate through all of the objects in the collection. So there really is more functionality to the class... "How more additional functionality!?!?!?" Good question...

 

[mdlueck]

    [COLOR=green]' Insert Label7 before Item 3.[/COLOR]
    colThisCollection.Add Item:=Me("Label7"), Key:=CStr(7), [B]Before:=3[/B]
    
    [COLOR=green]' Insert Label8 after Item 3.[/COLOR]
    colThisCollection.Add Item:=Me("Label8"), Key:=CStr(8), [B]After:=3[/B]

Oh... so it is possible to insert in-between objects in the collection... so obviously the Collection is an ordered list.

That is the correct syntax, though? I understood the key was a string, so I would have expected to see quotes around the bare 3 in your example.

 

[mdlueck]

All right, ChrisO... you planted a thought seed that I could order the items per the user-defined sort order of the pick list entries possible to be selected from.

The UI of this screen has six Combo Boxes. People may set the various combo boxes to any of the choices in the lists... which all six are the same list.

The Validation code will read the six controls. In my Items class Add method, I will first check for the existence of the Item, and if found skip adding the dupe value.

When the six controls have been read / stored in the collection / once validation passes / THEN the record is stored to the table. So a neat capability would be to sort the selected Item values per the sort order of the Pick List.

I think the complicated part is that not necessarily all sequential pick list values may be selected. As example the selected items might be sort number 7, 2, 5 of the pick list.When adding 2, I could not say after 1 as it is not one of the selected values.

So since this is in memory already, would the code need to step through the list looking for the correct spot to insert the new entry?

The other technique that comes to mind is when saving to the DB table, scan the pick list table in sort order and check the collection IfExist based on the Key. At least that way there would be only one table scan operation rather than organizing each time a new Item is Added to the collection.

Thoughts?

 

[ChrisO]

>>That is the correct syntax, though? <<
In this case it can be either a number or a string.

If the Key was not a numerical expression then “3” would fail and 3 would work.

But it’s easy enough to test for yourself. You should be able to read more by placing the cursor on Add and pressing F1.




>>Thoughts?<<
Well yes, I’m thinking I haven’t a clue what you’re talking about.

Chris.

 

[mdlueck]

>>That is the correct syntax, though? <<
In this case it can be either a number or a string.

If the Key was not a numerical expression then “3” would fail and 3 would work.

But it’s easy enough to test for yourself. You should be able to read more by placing the cursor on Add and pressing F1.

I did that, and got some additional help on specifically the Add method of the Collection class:

before Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection before the member identified by the before argument. If a numeric expression, before must be a number from 1 to the value of the collection's Count property. If a string expression, before must correspond to the key specified when the member being referred to was added to the collection. You can specify a before position or an after position, but not both.

after Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection after the member identified by the after argument. If numeric, after must be a number from 1 to the value of the collection's Count property. If a string, after must correspond to the key specified when the member referred to was added to the collection. You can specify a before position or an after position, but not both.

So this seems to confirm what I was seeing in the Watches window. Even if I set a string (text) key, in Watches in the collection, the added Item object still has a numeric / Integer identifier under the collection.

In fact, the string (text) key is invisible in the watches window.

>>Thoughts?<<
Well yes, I’m thinking I haven’t a clue what you’re talking about.

I am headed off in the direction of NOT re-ordering the collection object while individual Item objects are added.

I will go with the plan of scanning in sorted order the contents of the Pick List table, perform an IfExist query against the Collection, if found add the attribute to the DB record, and either the table scan is exhausted or all memory slots (6) are filled, then leave the loop.

Thank you for the useful dialog on this thread!

 

[DrallocD]

The Key is very powerful when you use a collection as a dictionary for fast lookups. Obviously, the key acts as a PK and must be unique.

Public Sub ctest()
Dim c As New Collection
c.Add "Item 1", "a"
c.Add "Item 2", "b"
c.Add "Item 3", "c"
Debug.Print c("c")
Debug.Print c("b")
Debug.Print c("a")
End Sub

 

[mdlueck]

The Key is very powerful when you use a collection as a dictionary for fast lookups. Obviously, the key acts as a PK and must be unique.

Correct and understood. See my proposed use for it in this instance:

  'Check if this item is in the Collection already
  If Me.Exists(CStr(intID)) Then
    'To maintain consistent API, look up the Item and return the one which was already in the Collection
    Set Add = Me.Item(CStr(intID))
  Else
    'It was not, so add it
    With newItem
      .id = intID
      strKey = CStr(.id)
    End With
  
    'add to the private collection
    m_PrivateCollection.Add newItem, strKey
  
    Set Add = newItem
  End If

I would like to end up with a sorted list per an external to the class order scheme.

So I will scan through the possible Pick List entries by Sort order, check for existence of each Item in the Collection, up to the maximum number of slots. In my mind, this single scan operation is more efficient than having to maintain correct sort order of the Collection object's contents.

 

[mdlueck]

And........... It works flawlessly!!!

The smart Add method of the Items class successfully prevents duplicate entries.

The pick list values end up getting saved to the BE DB in sorted order based on the current sort order defined by the application administrators.

The one sharp spot was again ggggrrrrr.... that the Eval() function is unable to work with class objects which are loaded in the current variable pool context. So I had to go off and hard code SELECT blocks to accomplish what I wanted to use Eval() for:

'Obtain the Pick List ID the intFARejCounter pointer indicates
Select Case intFARejCounter
  Case 1
    intThisFARejID = Me.farej1id
  Case 2
    intThisFARejID = Me.farej2id
  Case 3
    intThisFARejID = Me.farej3id
  Case 4
    intThisFARejID = Me.farej4id
  Case 5
    intThisFARejID = Me.farej5id
  Case 6
    intThisFARejID = Me.farej6id
  Case Else
    Call errorhandler_MsgBox("Class: clsObjUIValidationQualityTbl, Function: Validate(), Error: Reached Case Else, intFARejCounter=" & intFARejCounter)
    Validate = False
    GoTo Exit_Validate
End Select

'Test if the pick list is actually set to a value
If intThisFARejID > 0 Then
  'It was, so attempt to add it to the Collection
  ObjQualityFARejTypeItems.Add (intThisFARejID)
End If