VBA in Visio

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

VBA in Visio

Post by dasadler »

Does anyone know how to write VBA code that will look at every shape on a page and sum the value of one field for each shape where one boolean field=True?

I presume that Viso allows us to associate a VBA sub with a shape so we can click it to run the macro?
Don

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA in Visio

Post by dasadler »

Well, I gave it a shot with the code below. I got a compile error at Set pay = 0 with pay highlighted and a msg of 'Object Required'. What does this mean?

Code: Select all

Sub Paytotal()
Dim pag As Page
Dim shp As Shape
Dim pay As Long
'Set reference to correct page
Set pag = Application.ActivePage
Set pay = 0
'Loop through all shapes on page
For Each shp In pag.Shapes
If Prop.HIRED = True Then pay = pay + Prop.SALARY
Next shp

MsgBox pay
End Sub
Don

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA in Visio

Post by HansV »

A Long is not an object, so you should not use the keyword Set with it. Simply use

pay = 0
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA in Visio

Post by dasadler »

Thanks for that. My compile error now is that Prop.HIRED is highlighted with the msg of Variable not Defined.

Prop.HIRED is, I believe, the Visio name for the shape data field HIRED. Should I define this in VBA or am I using the wrong syntax? Should it be something like shape.Prop.HIRED? I expect I will have the same error with Prop.SALARY
Don

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA in Visio

Post by HansV »

What if you use shp.CellsU("Prop.HIRED").Result(visNone) and sh.CellsU("Prop.SALARY").Result(visNone)
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA in Visio

Post by dasadler »

That got a run time error. See images.
You do not have the required permissions to view the files attached to this post.
Don

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA in Visio

Post by HansV »

The following is air code (I don't have access to Visio at the moment):

Code: Select all

    If shp.CellExistsU("Prop.HIRED", False) = True And shp.CellExistsU("Prop.SALARY", False) = True Then
        If shp.CellsU("Prop.HIRED").Result(visNone) = True Then
            pay = pay + shp.CellsU("Prop.SALARY").Result(visNone)
        End If
    End If
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA in Visio

Post by dasadler »

Thanks Hans; the good news is that it does run without error. The MsgBox, however, always shows 0. I have arranged the shape data such that the result should be 100000.

BTW, every shape has HIRED and SALARY fields.
Don

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA in Visio

Post by HansV »

I'm on a computer with Visio now, but I only ever use it interactively, to design a floor plan or things like that. Nothing complicated.
I tried a few things in VBA, but I have to admit defeat - I can't get anything to work.
So I hope that VisioMVP or someone else will be able to help you.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA in Visio

Post by dasadler »

Thanks for your efforts.

In the last snippet of code, it looked as if you were testing first for the existence of those two fields then, if they did exist, to test for the value of HIRED and, if True, add SALARY to the variable pay.

Do I understand this correctly?
Don

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA in Visio

Post by HansV »

Yes, that was the intention, based on some code I found using Google. But when I tested it later, it didn't work... :sad:
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA in Visio

Post by dasadler »

No worries - I will continue to dig and when successful, I will post the code back here for future reference.
Thank you so much for helping. Is it my imagination or is it much more complex to write code for Visio than the other Office Apps?
Don

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA in Visio

Post by HansV »

I guess it's a matter of familiarity - if you're used to programming in, say, Excel, that seems "logical" and VBA in other Office application looks weird. Probably, once you become familiar with the concepts of VBA in Visio, it will seem logical too...
Best wishes,
Hans