Visio Guy

Smart graphics for visual people




You are here: Home / Development / Code / Save Time & Simplify Your VBA Code With CallByName

Save Time & Simplify Your VBA Code With CallByName

By 14 Comments

Read Full Article VBA in Visio is great for learning, prototyping and getting repetitive tasks done quickly. One problem I run into–when I’m not careful—is bloated code resulting from the copying and pasting of too many loops. But with the CallByName function in VBA, we can streamline our code!

Too Much Duplication

Most of my VBA code is involved with creating throw-away tools to help me build a set of shapes. And if you’re building a set of shapes, you need to do lots of “iterating over a collection.” You might need to do something to every master in a document, every shape on a page, every shape in a group, every geometry section in a shape… It’s so easy to keep copying loops over and over to each new Subroutine that you write. But pretty soon, your code gets long, hard to read, and difficult to modify. What if you could tell a loop to execute a specific function?

Re-use Your Loops

It turns out that VBA has a function called CallByName that allows you to generically call a procedure by name. With CallByName, you can write a procedure that, for instance, loops through all the shapes on a page. And when it gets to each shape, it calls a function that I passed in as an argument to the procedure! In the code below, you will find only one Sub that loops through all shapes in a Visio Page object. It is called ProcessAllShapes, and if you look inside the loop, you’ll see the CallByName function. ProcessAllShapes takes two arguments. One is a Visio.Page object, and the other is a string, called functionName. functionName is the name of a procedure that you want the loop to call. So you’ve essentially parameterized the iteration loop, and enabled easy re-use. This is cool!

Notes on the Sample Code

The code below has three, very simple Subs that do something to every shape on the active page:

  • ColorAllShapesRed
  • RotateAllShapesLeft
  • SetTextOnAllShapes

If you examine the code for these procedures, you’ll see that they have no loops, and that they don’t do much at all, other than passing the active page to some other procedure. Three other Subs perform the corresponding operations on a single shape:

  • ColorShapeRed
  • RotateShapeLeft
  • SetShapeText

The last subroutine, ProcessAllShapes, is called by the first three subroutines, using one of the second three as an argument! Now, you should note that you are passing the name of a function by enclosing it in quote marks. This is a bit weak.

For example, if you change the name of funcDoStuff to funcProcessItems, CallByName won’t know or care until run-time. That’s because CallByName will still have “funcDoStuff” as an argument, and the compiler doesn’t know that this is related to the procedure for which you just changed the name!

But its better than nothing!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

Option Explicit On
 
'// Subroutines for what I want to do to each shape.
 
Sub ColorAllShapesRed()
 
  Dim visPg As Visio.Page
 
  visPg = Visio.ActivePage
 
  Call ProcessAllShapes(visPg, "ColorShapeRed")
 
End Sub
 
Sub RotateAllShapesLeft()
 
  Dim visPg As Visio.Page
 
  visPg = Visio.ActivePage
 
  Call ProcessAllShapes(visPg, "RotateShapeLeft")
 
End Sub
 
Sub SetTextOnAllShapes()
 
  Dim visPg As Visio.Page
 
  visPg = Visio.ActivePage
 
  Call ProcessAllShapes(visPg, "SetShapeText")
 
End Sub
 
'// Subroutines that handle one shape.
Sub ColorShapeRed(ByRef shp As Visio.Shape)
 
  shp.CellsU("FillForegnd").FormulaForceU = "RGB(255,0,0)"
 
End Sub
 
Sub RotateShapeLeft(ByRef shp As Visio.Shape)
 
  Dim ang As Double
 
  '// Only handle 2D shapes:
 
  If shp.OneD = False Then
 
    ang = shp.CellsU("Angle").ResultIU
    shp.CellsU("Angle").ResultIU = ang + 90 * 3.14159265358 / 180
 
  End If
 
End Sub
 
Sub SetShapeText(ByRef shp As Visio.Shape)
 
  shp.Text = "Hello World!"
 
End Sub
 
 
'// Subroutine for iterating through all shapes on a page.
Sub ProcessAllShapes(ByRef visPg As Visio.Page, _
      ByVal functionName As String)
 
  Dim shp As Visio.Shape
 
 For Each shp In visPg.Shapes
 
   Call CallByName(ThisDocument, _
         functionName, _
        VbMethod, shp)
 
  Next
 
End Sub
Option Explicit On

'// Subroutines for what I want to do to each shape.

Sub ColorAllShapesRed()
 
  Dim visPg As Visio.Page
 
  visPg = Visio.ActivePage
 
  Call ProcessAllShapes(visPg, "ColorShapeRed")
 
End Sub
 
Sub RotateAllShapesLeft()
 
  Dim visPg As Visio.Page
 
  visPg = Visio.ActivePage
 
  Call ProcessAllShapes(visPg, "RotateShapeLeft")
 
End Sub
 
Sub SetTextOnAllShapes()
 
  Dim visPg As Visio.Page
 
  visPg = Visio.ActivePage
 
  Call ProcessAllShapes(visPg, "SetShapeText")
 
End Sub

'// Subroutines that handle one shape.
Sub ColorShapeRed(ByRef shp As Visio.Shape)
 
  shp.CellsU("FillForegnd").FormulaForceU = "RGB(255,0,0)"
 
End Sub
 
Sub RotateShapeLeft(ByRef shp As Visio.Shape)
 
  Dim ang As Double
 
  '// Only handle 2D shapes:
 
  If shp.OneD = False Then
 
    ang = shp.CellsU("Angle").ResultIU
    shp.CellsU("Angle").ResultIU = ang + 90 * 3.14159265358 / 180
 
  End If
 
End Sub

Sub SetShapeText(ByRef shp As Visio.Shape)
 
  shp.Text = "Hello World!"
 
End Sub


'// Subroutine for iterating through all shapes on a page.
Sub ProcessAllShapes(ByRef visPg As Visio.Page, _
      ByVal functionName As String)
 
  Dim shp As Visio.Shape
 
 For Each shp In visPg.Shapes
 
   Call CallByName(ThisDocument, _
         functionName, _
        VbMethod, shp)
 
  Next
 
End Sub

Notes About VBA

If you’ve dealt with modern programming languages like VB.NET or C#, then CallByName might look a bit hokey to you. And that’s because it is. In newer languages, you use something called a Delegate, that doesn’t depend upon a fragile string. We’ll talk about delegates in a future article. I also want to stress that, while VBA is super-convenient, it is not intended for wide-distribution of solutions. There are just too many problems involved with deployment and maintenance of VBA-based solutions. So learn and experiment and create tools in VBA, but move to VB.NET when it’s time to create a real solution!

Related posts:

  1. Create Visio Flowcharts Programmatically
  2. The Hidden World of Visio Shapes
  3. Visio and Xml Conference Resources
  4. Customized Visio HTML Export
  5. Text to the Bottom of the Shape

Reader Interactions

Comments

  1. Hi,

    First Thank you for this post
    I am trying to build a communication between Excel and Visio. Do you think that we can call a Visio function from Excel (by CallByName .. or other means)
    I have tried it without success …

    Thanks

  3. I just love your weblog! Very nice post! Actually you can do many thing to imporve it.

  5. Hello, I found your blog in a new directory of blogs. I dont know how your blog came up, must have been a typo, Your blog looks good. Have a nice day.

  7. One of the better pieces of info on this topic I’ve seen. Very useful to someone like me. Any other posts of this sort would be appreciated! Thanks!

  8. Hi all,

    I am very new to visio VBA and i am lookiking for some help on labels and text.

    Basically, I have imported data from excel using tolls->Add-Ons->building plan->import data to produce lots of boxes.

    then i imported label for those shapes (to insert the text data from the excel in the boxes) using tools->Add-ons->building plan->Label shapes.

    Now i am trying to automate something in visio and i need to get text from the box.

    however when i use the text property

    Objret = Object.Text

    It doesnt see any text in the shapes as it is Labels and not text.

    Could anyone please tell me how to get the text in the labels in the box please?

    Any help will be much appreciated.

  9. visio guy

    i want to do small module for visio to position and resizing shapes i am not familiar with that and i need you help(and if there is a fee no problem )

    please reply
    regards
    firas

  10. Thanks for sharing, I hadn’t noticed this method before. By using one parent procedure that can call any other child procedure, this could make a lot of VBA solutions even more powerful & flexible (while making for less code at the same time).

  11. Very useful and well presented information!
    However, I found that the code above does not run on my Visio Version2008 64bit.
    1st issue: Line#9: I had to replace visPg = Visio.ActivePage by Set visPg = ActivePage to avoid a runtime error 91.
    2nd issue: Line#72: Call CallByName(ThisDocument, functionName, VbMethod, shp) yields runtime error 438.
    Any help would be much appreciated.

  12. Just found out that the runtime error 438 does not appear if the code is put into “ThisDocument” whereas I had put it in a module first.
    So the next questions are:
    – how to use the code in a module?
    – how can I cross-call to a routine in another module?

Leave a Reply