Share:

" /> Visio Guy » Save Time & Simplify Your VBA Code With CallByName
Home » Code

Save Time & Simplify Your VBA Code With CallByName

Submitted by on June 23, 2008 – 10:02 pm | | 27351 views 12 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!

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!

12 Comments »

  • […] saw a great post by the Visio Guy today ( Save Time & Simplify Your VBA Code With CallByName ) and thought it would be a good opportunity to demonstrate some of the simplification that you can […]

  • tit says:

    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

  • Visio Guy says:

    Hi T,

    Are you trying to call a VBA macro in Visio from Excel?

    I think that requires accessing the VBA project.

    – Chris

  • steve smith says:

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

  • jack parler says:

    Can you provide more information on this?

  • Chu Koetz says:

    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.

  • jonjo22 says:

    Keep up the good work. Look forward to reading more from you in the future.

  • Michael Ryan says:

    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!

  • Pretz says:

    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.

  • firas says:

    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

  • JP says:

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

  • […] I saw a nice post by Visio Guy. It's nearly two years old, but has some nice VBA code in it. Visit Save Time & Simplify Your VBA Code With CallByName to find out how to use the CallByName function.See you next time! Office Cafe | LinksLiked […]

Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.

*