Macro looping

[AndrewJ]

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

[Paul Herber]

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.

[Jumpy]

Here a basic loop structure using For...Each:

Code

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

[JohnGoldsmith]

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

Best regards

John

[AndrewJ]

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

[JohnGoldsmith]

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

[AndrewJ]

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

[Jumpy]

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 = """@+"""

Code

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

[AndrewJ]

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

[kennyj]

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) 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.

- 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:

Here a basic loop structure using For...Each:

Code

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

[AndrewJ]

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.

[kennyj]

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 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", 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]

[AndrewJ]

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!