Morning all,
I currently have an array of tag elements which I have extracted from an element. I am looking to ensure that the tag's values are always in a particular order so that I can maintain consistent results when working with CSV's in excel. I have found some instances where certain drawings were reporting values out of sync when the data was viewed within excel, i.e. certain tags were found in a different column than they should have been. So I am wondering, when iterating through all tag values in the array using:
For I = LBound(oTag) To UBound(oTag)
can this be resorted in a order of my choosing, or is it easier to find the TagDefinitionName first (using a string comparison against the sequence of TagDefinitionNames that I desire) and then put the tag's value into the array indices manually?
Thanks
Unknown said: I am wondering, when iterating through all tag values in an array can this be resorted in a order of my choosing?
When you ask 'can this be resorted?' the answer is 'yes' — but you have to write the sort algorithm.
Unknown said: Is it easier to find the TagDefinitionName first and then put the tag's value into the array indices manually?
That would be one approach to implementing an algorithm.
In general, VB/VBA does not provide many ways to store data in memory. There's the array, and that's about it. Compared to the rich variety of storage methods available in other languages, VBA always has been the poor relation.
VBA does provide a Collection: a non-indexed heap of data. From the point of view of sorting, a collection is useless.
The Microsoft Scripting Runtime Library provides a Dictionary, which is just what you need. As well as the Dictionary object, it provides the File System Object, which is useful in other ways.
A dictionary does what you expect: you insert items into it, referenced by a unique key. In this case, you would use the TagDefinitionName as the key for the tag value found on each element. Now the order of tag data doesn't matter — you just ask the dictionary for the value having key 'abc' or whatever.
Regards, Jon Summers LA Solutions
Thanks Jon, I've used Collections before and only heard of Dictionary (though never used one) so I shall have a look into using one of them and see if I can get it working.
Hi,
One thing to check, if you're doing vba, would be the speed of the Scripting Library vs doing what you need purely in vba. I know that if you use a FileSystemObject, it is much slower than a vba "DIR" command. So that is just one thing to consider.
--Robert
Hi Robert,
I will check the performance once I have the script working though at this stage I cannot think of a way in vba without references to achieve the consistent results that a dictionary are meant to yield. Achieving this is of the upmost importance as the last thing I want is incorrect/irregularly sorted data and I'm more than willing to shed some processing time if thats what is needed to ensure the desired results.