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?
Barry Lothian 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.
Barry Lothian 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
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.
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.
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.
Barry Lothian 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 …
RobertArnold 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)?
RobertArnold 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.
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?
Barry Lothian 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
Barry Lothian 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.
I've not had much time to work on this but after having had to manually update the title block data in each file within my target folder last week, I just used my script to freshly export an updated CSV with the current tag values from the files. I was surprised to find that some rows within the CSV were showing irregular data such as:
FRC-P-___AVS-001-D-MC-ABU-17310.dgn,Sheet,FRC,FOR REVIEW,MAIN CROSSING SOUTH ABUTMENT,WALL ELEVATIONS SHEET 1,,BL/JC,JC,ADT,FRC-P-___AVS-001-D-MC-ABU-17310,AS SHOWN @ A1,02FRC-P-___AVS-001-D-MC-ABU-17320.dgn,Sheet,,,,SECTIONS SHEET 1,,,,,,,
As you can see above drawing 17320 shows no tag values bar 1 however when I go into the file I can see the all the values and I can also click on any of the tag elements and it will display the edit tag info dialog box indicating that the tags are valid and the link to the host element remains intact. I believe my previous orphan invalid tags issues may have been due to select all & drop association command to allow modifying associated dimensions and inadvertently breaking the link between tags and their host elements, but that is certainly not the case with this file. The macro scan for a specific named cell and then for any tags that are part of a predefined tagset. I've also ensured that there is only 1 host element using the name that is being scanned for. Can you think what would cause this flaky inconsistant tag reporting behaviour? Its extremely disheartening to think that I had finally nailed part of this macro only to find this happening.
Please post a small DGN example (no confidential data) that illustrates the problem.
Here is one of the files that is reporting incorrectly. I've removed everything except the tags and host element (point node at bottom right of sheet). I've also exported tag values to a CSV once again and the same behaviour is found.
Thanks for taking the time to look into this.
Here's what FlexiTable™ found in your example (see attached Excel workbook).
I can see it has found everything which validates that the file is fine so I'm wondering why Flexitable is successful retrieving the values yet my Macro is not. From the advice you provided me with and the examples on your website I believe I have logically arranged the code to extract the information. Assuming FlexiTable is written in MDL, would using any MDL functions in VBA solve the inconsistency?
The fact that I am able to retrieve the values in almost all files (excluding the problematic few) suggests that my code is fine, but I don't have the programming experience to know why VBA is failing in these instances. I can email you the macro if you wish to see what exactly I have?