How to retrieve a cell value in Visio by using VBA ?? :D

[stewvba]

Hi All.

I have just started to use Visio and VBA since last week, so I am a completed newbie.

Anyway, back to the topic.

I have created a stencil contains 3 shapes, and given them name primary, secondary and selection.


Shape "primary & secondary"

For both shapes,I have created 4 customise properties in their shapesheet. They are called prop.a, prop.b ,prop.c and prop.name.

When the user drags any of the shapes into a page, a window will pop up and asks the user to input the unique name for the shape and the values for these 3 variables.

Shape "Selection"

Once shape "selection" is dragged to the page or it is double clicked, a user_form will show up and it displays all the primary and secondary shapes within the page and all associated values. And user can select which box they wish to use to perform some calculations.

here is my code for the user_form1 retrieve informations from all in-page primary and secondary shapes

Code Select Expand


Sub abc_frm()

Dim shp As Visio.Shapes
Dim doc As Visio.Document
Dim compare As Visio.Shape
Dim pagobj As Visio.Page
Dim qty_p, qty_s, qty_shape As Integer

Set doc = ActiveDocument
Set pagobj = doc.Pages(1)
Set shp = pagobj.Shapes

For qty_shape = 1 To shp.Count 'count the number of shapes.

Set compare = shp(qty_shape)

If compare.Name Like "Primary*" Then 'if it is the shape "primary", place all values into listbox1
qty_p = qty_p + 1
    With math_form
        .ListBox1.AddItem
        .ListBox1.List(qty_p - 1, 0) = compare.Name
        .ListBox1.List(qty_p - 1, 1) = compare.Cells("prop.a").Result(32)
        .ListBox1.List(qty_p - 1, 2) = compare.Cells("prop.b").Result(32)
        .ListBox1.List(qty_p - 1, 3) = compare.Cells("prop.c").Result(32)
    End With
   
ElseIf compare.Name Like "Secondary*" Then 'if it is the shape "secondary", place all values into listbox2
qty_s = qty_s + 1
    With math_form
        .ListBox2.AddItem
        .ListBox2.List(qty_s - 1, 0) = compare.Name
        .ListBox2.List(qty_s - 1, 1) = compare.Cells("prop.a").Result(32)
        .ListBox2.List(qty_s - 1, 2) = compare.Cells("prop.b").Result(32)
        .ListBox2.List(qty_s - 1, 3) = compare.Cells("prop.c").Result(32)
    End With
End If
       
Next

math_form.Show

End Sub



Here is the screenshot to illustrate this.




I have used a comparison variable "compare", once a newly dragged shape's name is called "primary", all the related values will be place into the upper listbox, if the shape's name is "secondary", all the values will be place into the bottom listbox.

The current issue is, the name displayed in the listbox shown in screenshot is generated by Visio, and it just adds a few random numbers after the shape name.

I have tried to add an invisible customise property cell in the shapesheet
For example, prop.tag, and assign a value, for each shapes. So the comparison variable can use this cell to determine what shape it is.
And my logical is something like this

Code Select Expand



If compare.cells("prop.tag") like "Pri*" Then



Code Select Expand



If compare.cells("prop.tag") like "sec*" Then




However, this creates unexpected ending error. I assume is that you cant retrieve a value from a cell??

Could anyone help to solve this issue?

Thank you

[aledlund]

If you check the visio sdk you will discover there are lot's of ways to read cell values in visio. The question I would begin with is that your code appears to be working correctly, so possibly you can share with us how you assigned the names. As a test check the shapes in question and display their shapesheets, the shape name should be at the top of the window.
al

[stewvba]

If you check the visio sdk you will discover there are lot's of ways to read cell values in visio. The question I would begin with is that your code appears to be working correctly, so possibly you can share with us how you assigned the names. As a test check the shapes in question and display their shapesheets, the shape name should be at the top of the window.
al

Hi Al

Yes, my code works fine but there are some issues with it.
At the moment, i am using the following code to count the number of shapes and determine what shapes are they.

Code Select Expand



For qty_shape = 1 To shp.Count 'count the number of shapes.

Set compare = shp(qty_shape)

If compare.Name Like "Primary*" Then 'if it is the shape "primary", place all values into listbox1



So from the first shape to the last shape

If the "Name" of the shape is Primary, all the associated values from shape Primary will be dumped into listbox1.

If the "Name" of the shape is Secondary, all the associated values from shape Secondary will be dumped into listbox2.

The issue is, As you can see from the screenshot under name column, It returns a random number after the shape name. Eg Primary, Primary.4, Primary8,Secondary and Secondary.7

My question, instead of using "Name" of the shape to differentiate between Shape "primary" and "Secondary", Is it possible to use a cell within each shape to determine which shape it is?

So I have tried to use

the following code

Code Select Expand



If compare.cells("prop.tag") like "Pri*" Then



instead of

Code Select Expand



If compare.Name Like "Primary*"




where I set prop.tag as an invisible customised property with a given value, eg, Pri and Sec, represents primary and secondary.


but it produces an unexpected error.

[stewvba]

I have figure it out by myself.

right click at  Format > Special dialog

I just add a tag word for individual shape under data1



and I have modified my comparison code to

Code Select Expand


If compare.data1 Like "Pri" then



by doing this, User can input any name they wish and the software can differentiate difference between different shapes by checking values in "Data1"

[Visio Guy]

If you've got data in Shape Data fields, you can get at it like this:

string s = shp.get_Cells("Prop.tag").ResultStr(Visio.VisUnitCodes.visNoCast):

or

Dim s as String
s = shp.Cells("Prop.tag").ResultStr(Visio.VisUnitCodes.visNoCast)

[stewvba]

If you've got data in Shape Data fields, you can get at it like this:

string s = shp.get_Cells("Prop.tag").ResultStr(Visio.VisUnitCodes.visNoCast):

or

Dim s as String
s = shp.Cells("Prop.tag").ResultStr(Visio.VisUnitCodes.visNoCast)

Thanks very much.

I just realised I have to use this type of code in the future part of my program !!

Many thanks.

[stewvba]

If you've got data in Shape Data fields, you can get at it like this:

string s = shp.get_Cells("Prop.tag").ResultStr(Visio.VisUnitCodes.visNoCast):

or

Dim s as String
s = shp.Cells("Prop.tag").ResultStr(Visio.VisUnitCodes.visNoCast)

Thanks again.

Btw, could you please give me good website explain the syntax of this kind of code?

As I have seen some code with out the "ResultStr/Result" would still return a value, but I guess without "ResultStr", it wouldnt return a string, am I correct?

And what does  (Visio.VisUnitCodes.visNoCast) this mean? Coz in the another application, I have used 

Code Select Expand



.ListBox1.List(qty_p - 1, 0) = compare.CellsU("prop.name").ResultStr(231)



where within the (), i just have to put a desired Visio code, like 231 represents visUnitstring

[Visio Guy]

Go to the VBA debug window and type:

Code Select Expand


? Visio.VisUnitCodes.visNoCast
252


252 comes back as the answer. The 252 is the value for that constant. There are a whole bunch defined in the "Visio.VisUnitCodes" enumeration, and they are used as arguments for Result() and ResultStr().

"NoCast" means "just give me the result, whatever Visio thinks it should be". You usually use this when you are expecting text, not numbers.

Some more examples: here we can get the width of the active drawing page in several units:

Code Select Expand


?ActivePage.PageSheet.Cells("PageWidth").Result(Visio.VisUnitCodes.visInches)
8.5

?ActivePage.PageSheet.Cells("PageWidth").Result(Visio.VisUnitCodes.visFeet)
0.708333333333333

?ActivePage.PageSheet.Cells("PageWidth").Result(Visio.VisUnitCodes.visCentimeters)
21.59


[stewvba]

Thanks so much

Really appreciated !!

[Visio Guy]

Regarding your other question, VB and VBA allow for "default properties". If you don't type .Result, .ResultIU, .ResultStr after Cells(... )then you'll get a value as a default. My guess is that the default property is inches, which Visio uses as internal units.

Using the page as an example again, here we see usage of the default property for Cells(...):

Code Select Expand


'8.5" x 11" US-size paper:
?ActivePage.PageSheet.Cells("PageWidth")
8.5

'A4 Metric drawing:
?ActivePage.PageSheet.Cells("PageWidth")
8.26771653543307


I don't think C# supports default properties, and being picky, I usually don't use them if I can avoid it. I usually work with pure numbers, so I use ResultIU, which takes no arguments, and returns a number in inches.

So for a 2-inch wide shape:

  Debug.Print shp.Cells("Width").ResultIU

will yield 2.0.

For a 2-centimeter wide shape:

  Debug.Print shp.Cells("Width").ResultIU

will yield 5.08.

[stewvba]

Regarding your other question, VB and VBA allow for "default properties". If you don't type .Result, .ResultIU, .ResultStr after Cells(... )then you'll get a value as a default. My guess is that the default property is inches, which Visio uses as internal units.

Using the page as an example again, here we see usage of the default property for Cells(...):

Code Select Expand


'8.5" x 11" US-size paper:
?ActivePage.PageSheet.Cells("PageWidth")
8.5

'A4 Metric drawing:
?ActivePage.PageSheet.Cells("PageWidth")
8.26771653543307


I don't think C# supports default properties, and being picky, I usually don't use them if I can avoid it. I usually work with pure numbers, so I use ResultIU, which takes no arguments, and returns a number in inches.

So for a 2-inch wide shape:

  Debug.Print shp.Cells("Width").ResultIU

will yield 2.0.

For a 2-centimeter wide shape:

  Debug.Print shp.Cells("Width").ResultIU

will yield 5.08.

Thanks again

the word you use "value" is both number and string in that cell or its a number only??

I have just done a  few small tests where took off Result(32) and Resultstr(231) .

If the cell contains a value, the system will display a value correctly

If the cell contains a string, the system will only display a 0.

[wapperdude]

As far as sorting your shapes by name, you might try something like the following code.  The ".Master" strips off the shape ID number appended to the shape name.  But, you must also eliminate shapes that have no master, as these will produce an error in the code.  I'm not sure if the "shape.Master is Nothing" test is sufficient.

Sub FindShape()
    Dim shape As Visio.shape
    Dim name1 As String

    name1 = "Position"                                                    'Add more variables for additional shapes
    For Each shape In ThisDocument.Pages(1).Shapes
        If (shape.Master Is Nothing) Then                           'Use this to trap shapes that have no master.
            MsgBox "This shape has no master"
        Else
            If shape.Master = name1 Then MsgBox shape.Master     'This is where you sort your shapes by name
        End If
    Next
End Sub

HTH
Wapperdude