Can I write a VBA subroutine so that a property (not the property's value) is one of the parms?

[BobBridges]

Not sure VBA can do this, but it would be handy if it's possible. I'm writing a set of programs in Excel, and there are some situations where I want to sort a collection. Normally I could sort the data after I load them into a worksheet, but for various reasons that won't work here. We can talk about that in more detail if you care, but meanwhile:

Sorting a collection isn't hard. But so far in each case I want to sort each collection by the value of a single property. So rather than having three different routines to sort collections of three different objects, is it possible to write a single function that'll do the job? Like this:

' Sort the Coll collection by its key property.
Function SortCollection(Coll, KeyProperty)
  Set NewColl As Collection
  ' ...Here I sort the items of Coll into NewColl
  Set SortCollection = NewColl
  End Function

The three calls would go something like this:

Set Schools = SortCollection(Schools, District)
   '...where Schools is a collection of School objects, and District is a property of the School class
Set Users = SortCollection(Users, UserID) 'where UserID is a property of the objects in the Users collecton
Set Transactions = SortCollection(Transactions, Name) 'where Name is a property of the Transaction class

Is this even possible?

 

[Marc L]

Yes, just define a Collection parameter in the Sub or Function and if using a function its type should be too a Collection.​

​

But obviously it is useless as the efficient way is to directly create the Collection sorted​

just reading the VBA help of Collection.Add method or for a single value and under Windows only​

you could use the Windows ArrayList (which has a sort feature) or the Windows SortedList, see samples in this forum …​

​

 

[BobBridges]

I'll come back to the sorted-collection part of your answer, but before I do let me work on the first part; there may be other times I want to do this, even if not for a sort. "Define a Collection parameter in the function", you say. Like this?

' Sort the Coll collection by its key property.
Function SortCollection(Coll, KeyProperty) 'where Coll is a collection
  Set NewColl As Collection
  ' ...Here I sort the items of Coll into NewColl
  Set SortCollection = NewColl
  End Function

That's what I gave as my starting example; the first parm is a collection passed from the caller. Maybe I'd better include the sort routine, too, and you'll see the problem:

' Sort the Coll collection by its key property.
Function SortCollection(Coll, KeyProperty) 'where Coll is a collection
  Dim LowItem, LowKey, NewColl
  Set NewColl = Collection
  Do While Coll.Count > 0
    
    ' Find the lowest key.
    LowKey = "zzzzz"
    For Each oit In Coll
      vk = oit.KeyProperty
      If vk > LowKey Then GoTo IterateItem
      Set LowItem = oit
      LowKey = vk
IterateItem:
      Next oit

    ' Move the next lowest item to NewColl.
    NewColl.Add LowItem, LowKey
    Coll.Remove LowKey
    Loop

  ' ...Here I sort the items of Coll into NewColl
  Set SortCollection = NewColl
  End Function

So here's the problem: Suppose I call SortCollection this way:

Set Schools = SortCollection(Schools, District)

I want SortCollections to sort the Schools collection on Schools(Item).District. But when in SortCollections it refers to Item.KeyProperty, isn't VBA simply going to say "The Coll collection does not have a property named KeyProperty"?

 

[Marc L]

Coll is a Variant, why do not declare it as Collection ? …​

​

 

[BobBridges]

No very compelling reason. Extra typing is all, maybe. Why bother? I hardly ever assign scalar data types; variants seem to work for me, mostly.

More to the point, does it have anything to do with my question? What I want is to be able to construct a subroutine that I use to manipulate multiple collections of different objects, each by a different property. What I'm asking is whether that's possible. I doubted it, but you said it is; I'm hoping to learn how.

I'm not forgetting that you said there are better ways to have sorted collections, and I will come back to that. But just now what interests me is whether there's any way to write a subroutine that can handle different kinds of objects—that I can tell "do it with object A and property P this time, but during another call do it with object B and property Q".

Let's put it even more simply. Suppose I wrote this:

MsgBox X(Schools, Name) 'display the name of Schools(1)
MsgBox X(Subscribers, City) 'display Subscriber(1)'s location
MsgBox X(Inventory, Cost) 'display the cost of the first item in inventory

Function X(Coll, Prop)
  X = Coll(1).Prop
  End Function

What would I have to do to make that work? Can it be made to work?

 

[Marc L]

Really, did I write « better ways » ?!​

​

Now as I better see (*) your property concern the other ways ArrayList & SortedList can't work as they are just values lists …​

​

Using Collection it's possible according to the forum rules, in fact according to what any forum expects when a thread is created :​

a crystal clear explanation of the need / issue with nothing to guess, not theorical but technical !​

​

When I need a Collection to be sorted so I obviously code the Collection to be directly sorted each time​

a new item is added with an easy kid level logic, just inserting the item at the appropriate place​

- as I wrote « just reading the VBA help of Collection.Add method » -​

so that's more efficient than any routine sorting the Collection after all items added, that's useless …​

​

If the property is always the same do not pass it to the procedure, directly use it.​

If it can change, just pass its name to be used within the VBA function CallByName …​

​

(*) For further help, again, use the standard font far more easier to read than yours too small on some screens.​

​

 

[BobBridges]

You did mention the font, but I misunderstood and thought you were talking about the typeface. But the size, yeah, I get that. I used to have pretty high-resolution eyesight, and kept my text small. Now I find I'm needing to make it bigger. I can bump it up a bit. Is this better?

I was planning to protest, eventually, that of course I've read the documentation for Collecton.Add. Several times. Years ago. So long ago, in fact, that I'd long ago forgotten the Before/After feature, which I never had occasion to use before. You didn't say so, but I suppose that's what you're thinking of, right?

But still, how does that help me? Each time I add an item to the collection, I'll have to search through the items that are already there and decide where the new one belongs. Intuitively it seems it'll take about as long to execute to arrange them afterward as during. Unless (this thought just in) I write a binary-search routine and use it while building the collection; for large collections that would probably save some time; for anything under a few hundred items it probably wouldn't be noticeable.

As for the other, "CallByName" is what I was missing; I thought I might have heard of something that would work, but couldn't remember and was losing hope. I'll look it up; thanks.

 

[Marc L]

Yes.​

​

Right. As its VBA help is at kid level (not a joke !) …​

Reading first a documentation may be still in memory for a longer time than just copying / pasting …​

​

Not so long but how many items in the Collection ?​

Inserting item to directly sort the Collection does not waste any more ressource​

like your SortCollection function needing to create a new Collection.​

Imagine you need 3 Collections using each 500 MB RAM on a computer using Excel 32 bits version​

so managing 2 GB RAM maximum even if the computer has 32 GB !​

With another Collection just to sort one of the 3 Collections Excel may crash, at least hangs for a while, even Windows may crash …​

​

With CallByName you can call an object property by its name as a String​

so now you have the necessary to try the way you prefer, even both !​

​

If you know how to loop in VBA and if you need more help I can give you an VBA challenge​

- the one I gave to beginners, some kids like a man closer to 80 years old than 70 solved it -​

to create first a stand-alone procedure using the property hard coded, once achieved​

the next step is to revamp it in order to create a sub procedure directly allocating a sorted Collection …​

​

 

[BobBridges]

I've been programming in a couple dozen languages since the mid '70s; loops don't worry me. I've been in object-oriented code only the past decade or two, though, a relative newbie. Now that you've reminded me of CallByName—I still haven't looked it up, but I'm pretty sure it's what I need—I have in mind writing and testing at least that routine, just to prove to myself that it'll work. After that, a function that any code can call to insert a new object into a collection, in its proper order; it'll probably do a binary search for the proper location.

The collections I'm sorting this way, in the reports I'm writing now, top out around 600 members, I think—not nothing, but not terribly significant either. Might save a second or two of execution time, once I get it working. Normally I pour the data into the worksheet and then sort them, but in this project there are a few different situations where I can't, for one reason or another. (For instance, in one case the users are taking two columns apiece, with merged cells at the top. Sort declines to work unless all the cells are the same size. Although it occurs to me, now that I've already solved that problem, that I could have left the header cells unmerged at first, sorted the columns on the user ID combined with "1" or "2", and merged the header cells afterward.)

 

[Marc L]

Sometimes sort may work with merged cells but not all the time,​

with a sample attachment respecting exactly the original layout I could give it a try …​

​

Instead of merging headers you may use the horizontal aligment 'Center Across Selection'.​

​