Excel-Based Rally Course Design Software

by Chuck Shultz

Face it, I'm a lazy person. In fact, I'll go out of my way to make things easy down the line. That's what motivated me to make this program. That and going to Rally matches and having to look at course maps that I couldn't understand. That's what this program does-it allows Rally judges and instructors to easily make and update Rally-Obedience course maps and other forms needed at a Rally trial. Right now, it works for AKC Rally rules and signs; Ultimately, I plan to create an APDT version.

The "program" is really a Microsoft Excel spreadsheet file. I've had a lot of experience with Excel and it does a lot behind the scenes that few people realize it's capable of doing. It uses buttons, forms and macros, along with a few special formatting tricks to provide all the tools we need to come up with clear, professional-looking course maps. This article describes how it all works.

The most current version of the program can be found on my web site off of the page http://www.chuckles.net/software.htm. Download and save the file on your PC without opening it first. I've found that when you try to open it using Microsoft's Internet Explorer (IE), sometimes IE will try to do you a favor and open up Excel "from within" IE. The result is that sometimes the macros don't work and that makes this program useless.

Once you save the file, you can make copies of it and rename those copies. Keep in mind that you can put more than one course into an Excel file. Thereís more at theend of this article about organizing your files.


Let's open one of the Rally Course Designer Excel spreadsheets and see how it works. When you first open up the file, if you are prompted to enable or disable macros, you must enable them. If you are not prompted, your Excel may be set up to automatically disable macros. If so, you need to change this. Do so by going to the Excel toolbar and selecting Tools -> Macros -> Security, then selecting the Medium level. You must close the file and re-open it again in Excel.

Excel files are made up of any number of worksheets. Each worksheet has a name that is displayed on a tab at the bottom of the Excel window. You navigate to a worksheet by clicking on its tab. The file will open to a worksheet called Directions, which gives you a consolidated version of what's in this article. Next to the Directions worksheet is a Judge's Checklist, which you can print to help remind you what to bring along when you judge.

If you've used Excel before, these worksheets may be different than what you are used to seeing. For all of the worksheets in this file that you can see, I've turned off the row and column headings and the gridlines. This gives the effect that each of these worksheets looks like a blank sheet of paper.

Begin by selecting the Courses worksheet and clicking on the Add A Course button. This pops up a form that asks you for everything it needs to set up a new course map.

Notice that the Class and Type of Event fields use pick lists. In the Class list, you can select from Novice A, Novice B, Advanced A, Advanced B, Excellent A, Excellent B, Novice, Advanced or Excellent. What you select in this list determines which set of signs will be available on the Course map and the number of signs required.

In the Type of Event list, if you select Match, Trial or Show & Go, then the course map will insist on a valid course, i.e. it has to have the right number of signs, it can't have too many stationary exercises, a Normal or the Finish sign must follow a Fast or Slow, and it has to have the right number jumps for the course's level. In the Type of Event list, if you select Class, Demo, Clinic or Seminar, then the course map will relax these rules.

Select the checkbox for Judge's Sheet and the program will create a sheet that the judge uses to score an individual competitor. Select the checkbox for Judge's Workbook and the program will create a sheet that the judge uses to summarize all of the scores in that class. Then tell the program what size course to create and press the Create Course button.

Excel will flash some screens back and forth, then sure enough, there's a new worksheet for a new Course Map and maybe a new Judge's Sheet and a new Judge's Workbook. On the Courses worksheet, a new line has appeared, listing this newly added course. Use the Delete A Course button on the Courses worksheet to completely remove a course.

On the Course Map worksheet, the upper-left table holds the information relevant to the course, all of which came from the pop-up form. Below that is the list of exercises for the course. Starting at the selected cell (don't forget that the cells really are there, there are just no grid lines), use the drop-down list to select the exercises in the course. Each time you select an exercise, the sign number for that exercise will pop up in the column to the left of the exercise description. Go down the table one row at a time and select enough exercises to make a legal course.

Below the list of exercises will be some pictures you can use on your course-numbers inside circles for the signs, cones, and for Advanced and Excellent courses, jumps. The area to the right is the actual course area, onto which you'll end up putting all of the signs.

Once you have a legal set of signs put into the table, press the Get Signs button. The first thing this will do is validate the signs you selected: Did you use the right number of stations? Did you use too many stationary signs? Did you use Fast and Slow properly? Did you use the right number of jumps? When you press the button, all of these are checked. In fact, Excel puts on another display of automation when you press this button-it flips back and forth between worksheets validating the course. If you happen to choose a set of signs that are invalid, an error message pops up and you are given the opportunity to select different signs. We would all hate to show up to judge, then pull out a course map that didn't follow the rules.

Once you have a valid course and you press the button, the software will pass the validation step and will copy all of the signs you need for your course onto this worksheet. These signs get copied to the right of the course area. Except for the Honor, the signs are connected with arrows in the sequence in which they appear in the table. The arrows are special-their heads and tails will stick to the sides of the signs, so that when you move a sign, the ends of the arrows stuck to the sign move right along with the sign. The other thing that'll happen is that the Editing Tools form will appear. It has buttons on it that you can now use to edit your course.

Now, there are two schools of thought on orienting the signs. Some people find it easier if the signs are all pointed in the same direction, others want the signs to run in the same direction as that part of the course. Ultimately, it's up to you how you want your course map to look. The program allows you to choose either way.

I find an interesting analogy to the navigation system I have in my car. In the center of the screen is an arrow that represents where the car is and what direction it's pointed. The system gives the viewer two options-either the car can spin around as I make a turn and the map always keeps north at the top of the screen, or the car can stay pointing to the top of the screen and the map can rotate under the car. Either way is valid, it's really all a matter of choice.

If you want to keep all of the signs oriented in the same direction, then from here you should simply move each sign into the position you want it to be in. Do so by hovering the pointer over the sign, then holding down the left mouse button. While the button is down, you can move the sign into the course map area and position it in the location you want it on your course.Just be careful when you are using the mouse to grab a sign; It's very easy to grab an arrow instead. Doing so will pull the arrow completely away from the signs it's attached to. If you do that, don't panic. Just let go of the arrow (anywhere), then go up and press Excel's Undo button (the one with a counterclockwise arrow on it or, Edit -> Undo does the same thing). The arrow will pop back to its correct position; Connected to the signs you just removed it from.

At this point, youíll need to move the ends of some of the connectors to different sides of the signs, otherwise they may cross over the signs. To move an arrow, first select it. Notice how a red circle appears at each end of the arrow. Grab the red circle on the end you want to move and drag it from its current position to the edge you want it to hook onto. Notice how the circle turns green when the arrow is not stuck to one of the edges of the sign. When the end of the arrow hovers over or near the sign, 4 blue dots appear around the sign. These are the spots that the end of the arrow can stick to. These are your visual clues for moving these arrows (in Excel terms, these arrows are really connectors).

If you want the signs to follow the direction that the course is flowing, first move the Start sign into the position you want it to be on the course map area. Then, rotate it into the direction you want it to point. In my example, I have moved it into the lower-left corner and I want the arrow coming out of it to travel to the right. Since the course will flow from top the Start sign to the right, you want to rotate the Start sign to the right. To do so, first select the picture of the Start sign. Then, press the button on the Editing Tools that has the arrow pointing to the right (clockwise). The Start sign will spin to the right 90 degrees. The clockwise and counterclockwise buttons rotate the selected sign in 90 degree increments.

You can spin a sign by something in other than 90 degree increments. Select the sign and notice the green ball that appears over the sign. Use the mouse to grab the ball and youíll see that dragging it will cause the sign to rotate, and at angles other than 90 degree increments.

Once the Start sign is in the location and direction you need, press the Adjust button. One at a time, each sign gets placed along the direction that the course is flowing. Each sign is also rotated to flow in the direction of the course. And, each arrow is moved to the appropriate side of the sign. For example, the tail of the first arrow is moved to the top of the Start sign and its head is moved to the bottom of the sign #1. Sign #1 moves into position and is spun to the correct orientation. The tail of the second arrow is connected to the bottom of sign #1, and so on.

The button does a fair job at moving the signs into position. But since there are often many ways to lay out the same sequence of signs, it generally isnít going to come up with the exact course you had in mind. And, if your course includes a mix of signs, some of which are and some arenít lined up with any of the edges, then the button has no way to anticipate those kinds of changes in direction. A few of the signs, such as the Spirals, even have more than one valid way of exiting the exercise. Still, the software does much of the mundane work associated with creating Rally courses.

At some point, youíll find yourself in a position where you need to adjust some of the signs. When fine tuning, I often use Excel's zoom feature (View -> Zoom, then select 100% or 200%).

In the example here, I put in a sign that ends up being in the wrong direction I intended. Notice sign #13, the Spiral Left, Dog Inside. I intended the Finish sign to be to the right of sign #13, so it really needs to be a Spiral Right, Dog Outside This is easy to change. Simply select the Spiral Left sign or the row in the table that holds the Spiral Left sign, the same way we did when rotating the Start sign. Then press the Change button on the Editing Tools. This pops up a form that allows you to change a sign. The form has the position of the sign being changed, along with the sign that it currently holds. If you've selected the right sign, you only need to change the second field, in this case, from Spiral Right to Spiral Left. Press the Continue button and again watch as Excel validates the course that you're changing, then goes ahead and changes the sign and the table entry.

Similarly, you can Add and Delete signs from the course, using the buttons on the Editing Tools. Each of the Add, Change and Delete buttons checks validates the course you are trying to make, so you can't try to put in less than 10 or more than 15 signs on a Novice course for example. You can't put in too many stationary exercises, nor use more than one of most of the signs. This is a good place to point out that some signs (like the Left Turn, for example) are not limited to being used a certain number of times on a course. Indeed, a rally course with 15 Left Turns is legitimate, but I doubt any judge would bother with such a trivial lineup. When you use more than 2 of any of these signs, the validation step will point out that this is legal, but remind you to bring more of those signs with you.

Continue adding, changing and deleting signs as you see fit. Once you are satisfied with your course, press the Adjust Numbers button to move the small numbered circles or squares next to the signs on the course. This helps to guide the reader through the course in the correct order. Again, you may need to fine tune the position of some of the signs. Move the cones onto the course, lined up next to the spiral, serpentine or weave signs. A nice touch is to add the logo of the club you are judging or teaching onto the course map.

If after all of that you still don't like it, you can always press the Reset All button to clear out all of the signs on this course and start all over again! When you first created the Course Map, you had the option of creating 2 other worksheets. Each Judge's Worksheet keeps score of one competitor. You should bring to a trial one copy of this sheet for each competitor. Print the Judgeís Book to summarize the scores of all the competitors at your event.



Another nice feature of Excel is being able to save a worksheet as an HTML page. This can be very useful for instructors who want to post the maps for their students, and for judges wanting to share their course maps with each other. I've found that print-outs of Course Map web pages can fit on one page if you go to File -> Page Setup and remove the Header and Footer before printing.


More about organizing filesÖ

Hereís a picture of the way I lay out the folders on my PC. This is one way of organizing your files. If you donít have a way of organizing your files, you probably need one. If youíve already got a good system, stick with it.

|†††† |
|†††† *--200501\
|†††† |†††† |
|†††† |†††† *--classes200501.xls
|†††† |
|†††† *--200411\
|†††† .†††† |
|†††† .†††† *--classes200411.xls
|†††† .
|†††† |
|†††† *--200410_CardiganCorgiClub
|†††† |
|†††† *--SNJGSDClub
††††††† |
††††††† *-RallyOJudgingForm_AKC0dot16.xls

I have a single folder for all my Rally files. This folder has a separate sub-folder for each class Iíve taught and in those I keep the files I use for that class. For a single class, Iíll have one Excel file that contains all of the courses I use in that class, plus one PowerPoint that I use that contains the notes I cove the first day of class. If I come up with any other files for that class, I use the same folder.


Along with separate folders for the Classes I've taught, I have folders for from Demonstrations I've given and Matches I've judged. I keep a separate Software folder that keeps the most current version of the software. When it comes time for me to start a new class, I create a new folder under the Class folder. I create this new folder using the 4-digit year and the 2-digit month so that all the folders under Classes will sort nicely. If I want to use the same courses as the last time I taught, I'll copy and rename the file from the last class's folder. Otherwise, if I want to create new courses, then I copy in and rename the most current version of the file from the Software folder. Within the class sub-folders, I will also keep copies of the PowerPoint presentations I give during the first day of class.



All Material on this page and this web site is Copyright © 2005, Charles E. Shultz Jr.