Save Time & Simplify Your VBA Code With CallByName

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!

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!