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.
Unknown said: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
You're following, correctly, the zeroth rule of optimisation: correct code is always better than fast code that is incorrect.
The other rules of optimisation , by the way, are …
Unknown said:One thing to check would be the speed of the Scripting Library vs doing what you need purely in VBA
Somewhat alarmist! What reason do you have to suppose that the Scripting Library (a COM server) is any slower than VBA (also a COM server)? How do you compare the performance of one library to another library when one supplies additional functionality that is lacking in the other (i.e. apples and oranges)?
Unknown said:One thing to check, if you're doing VBA, would be the speed
VBA is a Rapid Application Development (RAD) tool. RAD lets you get code working fast, but doesn't necessarily get you fast code.
If application performance is a criterion, then don't use VBA in the first place. Microsoft made a great RAD tool when they developed VB/VBA, but their measurement of performance was ease-of-use, not how fast the resulting application runs. VBA has a lot of stuff going on under the hood, such as hidden COM interfaces, that impose a processing hit and which we can do nothing about.
If application performance is a criterion, then use a language that doesn't impose all the overheads of VBA. C++ is a good starting point for a high-performance application.
Hi Jon,
I've made great progress with the macro since I last posted. I can successfully export tag values to a CSV file while maintaining the desired order using the dictionary as you suggested. I've written most of the CSV to tags procedure but I have a query which I hope you can clear up. When amending multiple tag values in a model reference, should you call rewrite after each amended tag value or only once before opening another file?
Unknown said:Should you call rewrite after each amended tag value?
A tag is an element. When you modify an element whose state you want to be persistent then you must rewrite it.
Thats not quite what I meant so allow me to expand upon my query to be fully clear. I am updating multiple existing tag elements in a file and this is achieved using select case with the cases checking against tagdefinition name. When the macro finds a tag that is to be updated, after it process the instructions to update the tag value, should I rewrite the tag before moving to the next tag to be updated in another case statement, or can I update all the tag values and then call rewrite before my loop opens another file? I realise they need to be rewritten, its just that I had been encountering an error when call rewrite with each case statement and I was unsure if that was not allowed. Thanks
Unknown said: I had been encountering an error when call rewrite with each case statement
The Case statement and TagElement.Rewrite are not interdependent. I would re-examine my program logic.
My first thought was that it should not matter. I've been checking the logic this morning and I've added extra code that was missing to ensure that the values in the CSV are updated into the correct file.
I have found the cause of the rewrite error, I had set OpenDesignFileForProgram to readonly hence the issue. I do however have issues with the relationship of files being processed and the parsing of the CSV file so I'll need to spend some time trying to work out how to properly arrange the code.