Array reordering (not sorting)

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

Parents
  • 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.

    VBA Arrays, Collections & Dictionaries

    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.

  • 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.

  • Rules of Optimisation

    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 …

    1. Don't optimise
    2. Don't optimise yet
    3. Measure performance first

    Compare Apples with Apples

    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)?

    High-Performance Applications

    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.

    Regards, Jon Summers
    LA Solutions

     
    Regards, Jon Summers
    LA Solutions

  • 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?

    Thanks

  • 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.

     
    Regards, Jon Summers
    LA Solutions

Reply Children
  • 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.

     
    Regards, Jon Summers
    LA Solutions

  • 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,02
    FRC-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.

    Thanks

  • Please post a small DGN example (no confidential data) that illustrates the problem.

     
    Regards, Jon Summers
    LA Solutions

  • Hi Jon,

    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.

    FRC-P-___AVS-001-D-MC-ABU-17320.dgn
  • Here's what FlexiTable™ found in your example (see attached Excel workbook).

    Regards, Jon Summers
    LA Solutions

     
    Regards, Jon Summers
    LA Solutions

    FRC-P-___AVS-001-D-MC-ABU-17320 [Sheet].zip
  • 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?

  • Unknown said:
    I can email you the macro if you wish to see what exactly I have?

    No thanks!  This would become a one-to-one debugging tutorial conducted in a public Forum.

    Unknown said:
    The fact that I am able to retrieve the values in almost all files suggests that my code is fine

    Well, that suggests that it's almost fine.  Probably the code is OK, but you've overlooked some quirk of tag elements or tag definitions.

    Unknown said:
    Would using any MDL functions in VBA solve the inconsistency?

    VBA provides everything that's implemented in MDL (it's just a layer of VB wrapped around the mdlTag_api).

     
    Regards, Jon Summers
    LA Solutions

  • Unknown said:

    I can email you the macro if you wish to see what exactly I have?

    No thanks!  This would become a one-to-one debugging tutorial conducted in a public Forum.[/quote]

    No, I don't want to take up your time debugging my code - thats my job, it was only suggestion that you may have been able to quickly spot if what might be causing the error.

    Unknown said:

    The fact that I am able to retrieve the values in almost all files suggests that my code is fine

    Well, that suggests that it's almost fine.  Probably the code is OK, but you've overlooked some quirk of tag elements or tag definitions.[/quote]

    Agreed, however there are very few examples covering this issue; none in the supplied VBA help though your own article on the Tags and the TextFilewriter example were both extremely useful and the knowledge gained has helped form the basis for my own version. I believe I have followed the same code procedures to extract the values and I have just stepped through the code with the debugger and I can see that when I use objHost.gettags on the host element, it is only finding 3 tags as displayed in the locals window. To my knowledge there are no quirks that I have missed relating to this particular process as all I am doing in scanning for a named cell and extracting its tag values. What I can tell you is, if I use the drop tag associations command on the tags and the use reassociate tags to the same original host element and then run my macro, the CSV contains the correct results. This leaves me with a couple questions:

    1. I don't know what under-the-hood coding operations your FlexiTable performs (though I imagine it to be very similar),  how is it able to extract the values without the extra step I just had to go through?
    2. Is it possible to programatically refresh/regen/redraw the tags before I use objHost.gettags so that when it is called the DGN will recognise the values of the tags as currently it doesn't seem to be doing so?

    Thanks Jon