BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Looking for a list of dictionaries

Started by VisioForHVAC, May 28, 2024, 03:28:23 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.


I have a custom shape that identifies a part that needs to be ordered and added to the bill of material for the project. The shape has the following shape data fields, TAG, PART NO, QTY, MANUFACTURER, and DESCRIPTION. I am working on a VBA script that reads through all of these shapes and combines it into one list. Eventually I will want to export that list to Excel. I'm probably thinking a csv for that, but I haven't gotten there yet.

Right now, I use a separate dictionary for each shape data field. I use the "PART NO" entry as the key to keep them organized. I feel like this isn't very efficient and there is probably a better solution that I'm not thinking of. What I really want is a list of dictionaries with multiple key value pairs. Below is what I would do in python. Is there any way to do something similar to this in VBA? Does anyone have advice on a better approach? I'm still fairly new to VBA so any input is welcome.

ls = [
    {"tag": "A", "partNO": "a", "qty": "1", "manuf": "Manuf A", "description": "the letter a"},
    {"tag": "B", "partNO": "b", "qty": "1", "manuf": "Manuf B", "description": "the letter b"},
    {"tag": "C", "partNO": "c", "qty": "1", "manuf": "Manuf C", "description": "the letter c"},


Public Sub test()

Dim vPage As Visio.Page
Set vPage = ActivePage
Dim shp As Visio.Shape
Dim tag As String
Dim partNo As String
Dim qty As Integer
Dim manuf As String
Dim desc As String

Dim tagDict As New Dictionary
Dim partNoDict As New Dictionary
Dim qtyDict As New Dictionary
Dim manufDict As New Dictionary
Dim descDict As New Dictionary

For Each shp In vPage.Shapes
    If shp.CellExistsU("Prop.testTag" & THePropertyName, 0) Then             
        tag = UCase(shp.CellsU("Prop.tag").ResultStr(visNone))
        partNo = UCase(shp.CellsU("Prop.PartNo").ResultStr(visNone))
        qty = shp.CellsU("Prop.qty.Value").ResultInt(visNone, 0)
        manuf = UCase(shp.CellsU("Prop.manuf").ResultStr(visNone))
        desc = UCase(shp.CellsU("Prop.descr").ResultStr(visNone))

        If partNoDict.Exists(partNo) Then
            qtyDict(partNo) = qtyDict(partNo) + qty
            tagDict.Add partNo, tag
            partNoDict.Add partNo, partNo
            qtyDict.Add partNo, qty
            manufDict.Add partNo, manuf
            descDict.Add partNo, descDict           
        End If   
    End If
End Sub


You're pointing to a very vast aspect of Visio. Data handling and exchange with external apps.
There are so many possibilities.

Just to name some.
- reports - they export directly to Excel if wished
- linking shapes to an external DB or Excel file with refresh and so on.
- opening data sources by code for reading and writing
- and probably many others ...

Your method, writing data in individual dictionaries looks indeed clunky. A collection would make more sense???

Very cheap, but efficient: build up a string with defined separators.

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 155 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1055KB.
Tokens: post-login.
Cache hits: 14: 0.00235s for 26,766 bytes (show)
Cache misses: 3: (show)
Queries used: 15.

[Show Queries]