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?
VBA in Visio
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
VBA in Visio
Don
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA in Visio
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in Visio
A Long is not an object, so you should not use the keyword Set with it. Simply use
pay = 0
pay = 0
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA in Visio
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
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in Visio
What if you use shp.CellsU("Prop.HIRED").Result(visNone) and sh.CellsU("Prop.SALARY").Result(visNone)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA in Visio
That got a run time error. See images.
You do not have the required permissions to view the files attached to this post.
Don
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in Visio
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
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA in Visio
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.
BTW, every shape has HIRED and SALARY fields.
Don
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in Visio
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.
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
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA in Visio
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?
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in Visio
Yes, that was the intention, based on some code I found using Google. But when I tested it later, it didn't work...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA in Visio
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?
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in Visio
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
Hans