Hello,
I am currently trying to make a macro loop and don't know how to do that.
I recorded a macro of me applying certain settings to a shape. If I replay the macro, it works perfectly for only that shape though. I would like to loop it in such a way that it would apply those settings to all the shapes on that page or even preferably in the whole Visio file.
Could somebody please tell me what to add to this code in order to achieve that?
Thanks in advance!
Andrew
The code:
Sub Macro2()
'Enable diagram services
Dim DiagramServices As Integer
DiagramServices = ActiveDocument.DiagramServicesEnabled
ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150
Dim UndoScopeID1 As Long
UndoScopeID1 = Application.BeginUndoScope("Shape Data")
Dim vsoShape1 As Visio.Shape
Dim intPropRow2 As Integer
Set vsoShape1 = Application.ActiveWindow.Page.Shapes.ItemFromID(1)
intPropRow2 = 1
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsType).FormulaU = "0"
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsFormat).FormulaU = """@+"""
Application.EndUndoScope UndoScopeID1, True
'Restore diagram services
ActiveDocument.DiagramServicesEnabled = DiagramServices
End Sub
Your code currently accesses just the one selected shape, you need to get the count of shapes on the active page, then loop through these shapes, and use this loop index to access the shape.
Look through the Programming and Code forum here for many examples of how this is done.
Also, download the Visio SDK, there will be many examples of this.
Here a basic loop structure using For...Each:
Dim pg as Visio.Page, shp as Visio.Shape
For Each pg in ActiveDocument.Pages
For Each shp in pg.Shapes
'Do sth. with shp:
shp.Text="Foo"
Next shp
Next pg
You might also be interested in post I wrote a while ago about looping through shapes:
http://visualsignals.typepad.co.uk/vislog/2007/11/looping-through.html (http://visualsignals.typepad.co.uk/vislog/2007/11/looping-through.html)
Best regards
John
Thank you Paul, Jumpy and John!
That helped very much, however I want to make this work in conjunction with my recorded macro and all I seem to be successful modifying is the first shape, even after changing the macro to loop.
John I went to your very helpful website and tried 2 different modifications to my code using yours, but they didn't yield the desired result. I would appreciate any tips you can give me on either of the 2 codes, thank you.
First Code:
Sub Macro1()
Dim pag As Page
Dim shp As Shape
'Set reference to correct page
Set pag = Application.ActivePage
'Run through all shapes on the page
For Each shp In pag.Shapes
'Enable diagram services
Dim DiagramServices As Integer
DiagramServices = ActiveDocument.DiagramServicesEnabled
ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150
Dim UndoScopeID1 As Long
UndoScopeID1 = Application.BeginUndoScope("Shape Data")
Dim vsoShape1 As Visio.Shape
Dim intPropRow2 As Integer
Set vsoShape1 = Application.ActiveWindow.Page.Shapes.ItemFromID(1)
intPropRow2 = 1
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsType).FormulaU = "0"
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsFormat).FormulaU = """@+"""
Application.EndUndoScope UndoScopeID1, True
'Restore diagram services
ActiveDocument.DiagramServicesEnabled = DiagramServices
Next shp
End Sub
Second Code:
Sub Macro1()
Dim pag As Page
Dim shp As Shape
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer
'Set reference to correct page
Set pag = Application.ActivePage
'Run through all shapes on the page
iStart = pag.Shapes.Count
iEnd = pag.Shapes.Count
For i = iStart To iEnd
Set shp = pag.Shapes.ItemU(i)
'Enable diagram services
Dim DiagramServices As Integer
DiagramServices = ActiveDocument.DiagramServicesEnabled
ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150
Dim UndoScopeID1 As Long
UndoScopeID1 = Application.BeginUndoScope("Shape Data")
Dim vsoShape1 As Visio.Shape
Dim intPropRow2 As Integer
Set vsoShape1 = Application.ActiveWindow.Page.Shapes.ItemFromID(1)
intPropRow2 = 1
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsType).FormulaU = "0"
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsFormat).FormulaU = """@+"""
Application.EndUndoScope UndoScopeID1, True
'Restore diagram services
ActiveDocument.DiagramServicesEnabled = DiagramServices
Next i
End Sub
Hello Andrew,
What is the desired result? Can you give us a bit more detail on what you would like to happen?
Best regards
John
Hi John,
Thanks for offering your help!
I would like to go into Define Shape Data, and change the Process Number Type to "String" and the Format to "@+". I would like to do this for all the process boxes in a Visio file.
The desired goal is to be able to enter process numbers for process boxes that include letters (which cannot be done by default).
Andrew
The problem is the mixing of macro recorder code with our loop example code. You are looping through all the shapes and could do sth. to each shape using the variable shp. But instead of shp you use visioshape1 from macro recorder code which is always same shape. It is good to learn from the macre recorder how sth. is done, but it is not good to use the macro recorder code blindly. In your example only two lines from the macrorecorder code are usefull, but you still have to adapt them to the rest of the code:
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsType).FormulaU = "0"
vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsFormat).FormulaU = """@+"""
Sub Macro1()
Dim pag As Page
Dim shp As Shape
Dim intPropRow As Integer
'If you want to use the next part, do it only once, not inside the For...Each loop
'Enable diagram services
Dim DiagramServices As Integer
DiagramServices = ActiveDocument.DiagramServicesEnabled
ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150
'Set reference to correct page
Set pag = Application.ActivePage
intPropRow = 1
'Run through all shapes on the page
For Each shp In pag.Shapes
'Prior to altering a shape, you should somehow check, if it is a shape you want to alter
'If ...
shp.CellsSRC(visSectionProp, intPropRow, visCustPropsType).FormulaU = "0"
shp.CellsSRC(visSectionProp, intPropRow, visCustPropsFormat).FormulaU = """@+"""
'alternative: shp.CellsSRC(visSectionProp, intPropRow, visCustPropsFormat).FormulaU = CHR(34) & "@+" & CHR(34)
'End If
Next shp
'Restore diagram services
ActiveDocument.DiagramServicesEnabled = DiagramServices
End Sub
Thank you so much Jumpy, that actually worked like a charm! And you are right, I did somewhat blindly copy the recorded macro but at the same time, I wasn't sure what to look for in the recorded macro that could be transcribed into the new code. I'm new at programming and only understand the basics.
So here are 2 more questions for you:
- What does this part of your code mean? 'alternative: shp.CellsSRC(visSectionProp, intPropRow, visCustPropsFormat).FormulaU = CHR(34) & "@+" & CHR(34)
- How would I do to make the macro run through all pages? It only runs through all shapes on one page
Thanks again!
Andrew
Quote from: AndrewJ on May 24, 2013, 06:23:40 PM
So here are 2 more questions for you:
- What does this part of your code mean? 'alternative: shp.CellsSRC(visSectionProp, intPropRow, visCustPropsFormat).FormulaU = CHR(34) & "@+" & CHR(34)
The ' designates the line as a comment for humans to read, but not to be executed by the computer.
Thus removing the ' would make it an executable line of code.
CHR(34) inserts the character represented by the ascii code 34 which = " (here's a table of ascii values (http://www.asciitable.com/)) for your reference.
& is an operator that concatenates strings
Thus: shp.CellsSRC(visSectionProp, intPropRow, visCustPropsFormat).FormulaU = CHR(34) & "@+" & CHR(34) is another way of saying: shp.CellsSRC(visSectionProp, intPropRow, visCustPropsFormat).FormulaU = """@+"""
which might be easier to read since all those """ can run together visually and potentially be confusing.
Quote
- How would I do to make the macro run through all pages? It only runs through all shapes on one page
Jumpy already answered this part for you earlier in this same thread:
Quote from: Jumpy on May 22, 2013, 06:24:08 AM
Here a basic loop structure using For...Each:
Dim pg as Visio.Page, shp as Visio.Shape
For Each pg in ActiveDocument.Pages
For Each shp in pg.Shapes
'Do sth. with shp:
shp.Text="Foo"
Next shp
Next pg
Cheers,
kenny-j
Thank you Kenny!
I tried that and it worked.
Yeah I feel pretty stupid now that you've explained that. It's just hard sometimes when looking at the code to know what it refers to.
It's also difficult to know, when you've recorded a macro, which lines of code are useful. But I guess there is a way to identify that.
Quote from: AndrewJ on May 28, 2013, 02:49:30 PM
Yeah I feel pretty stupid now that you've explained that. It's just hard sometimes when looking at the code to know what it refers to.
It's also difficult to know, when you've recorded a macro, which lines of code are useful. But I guess there is a way to identify that.
No reason to feel stupid at all! Not being put off by black boxes and diving right in is a great way to learn, albeit painful betimes :) Too few people are brave enough to do it and cut themselves short.
I'm a total beginner on VBA myself. Here are a couple resources I'm finding to be helpful so far:
http://msdn.microsoft.com/en-us/library/office/aa200947%28v=office.10%29.aspx This one (http://msdn.microsoft.com/en-us/library/office/aa200947%28v=office.10%29.aspx) is a classic bit of documentation published by microsoft concurrent with visio 2002, but lays an excellent foundation (IMHO).
Chris Roth (aka VG) has an excellent book out called "Using Microsoft Visio 2010 (http://www.amazon.com/Using-Microsoft-Visio-2010-Chris/dp/0789742977/ref=sr_1_1?s=books&ie=UTF8&qid=1369812776&sr=1-1&keywords=using+visio+2010)", which has an online accessible only chapter 11, containing a brief intro to Using VBA in visio, which might prove useful for you. You can access it via a free trial at safari books online, if you like.
And perhaps most helpful of all is the visio sdk which you can install locally for offline access or access online. This has a number of useful features, some of which are:
- a complete definition of the object model, so you can look up what the various things mean plus find new ones you might like to use, complete with explanatory code snippets;
- a decent intro to VBA, itself very useful
- a fair amount of sample code in VBA and other ms languages
One cool feature is when you're in the VBA code window, you can hi-lite any object name, etc; then hit f1 and the relevant definition / example shows up in the text window.
HTH,
-k
[/list]
Thanks Kenny that helped a lot! I installed the Visio 2013 SDK and have been reading up, but there are still things that I don't understand.
Hopefully I'll get there!