As an Excel MVP, I know that Excel is an extraordinarily powerful tool that can be used to simplify work in countless situations. For example, fellow MVP Beth Melton recently designed a Gradebook template in Excel that makes teachers’ lives a whole lot easier. Based on Excel Tables technology which is built in to Excel 2010, the template includes grade calculation formulas and it even outputs a student progress report. All teachers need to do is select a student from the list, and the report is formatted and ready for printing.
Teachers who found this template on Office.com immediately liked what they saw and put it to use. Several teachers, however, wanted to make the template even more useful. They asked us whether the template could print all student progress reports at once.
To tackle this task, I knew recording a new macro wouldn’t cut it. It’s true that you could record a macro to print all of the class summaries in a batch. But this would not be a great solution because recorded macros are not smart. If the teacher were to add or delete students in the table, the macro would not function properly because the workbook would have been changed in a way that the recorded steps cannot fathom.
VBA is a fully fledged programming language built into Excel (and all other Microsoft Office applications, which supports variables (a place to store data) and programming logic. This allows it to interact with virtually all of Excel’s built-in application objects, creating a much more powerful way than macros to automate user tasks. With it, I knew I could create a tiny program, integrated into the Gradebook template workbook, which would let teachers print summaries in a batch for all of the students no matter how many new students were added or deleted.
Logic and variables are what separate a program in VBA from a mere recorded macro. If you are familiar with the IF() worksheet function, then you know the difference. VBA has its own version of IF and several other constructs that allow for the execution of logic to branch, depending on circumstances. Add looping to the flow of execution and defined variables that can hold different values at different points in that flow, and a program can run circles around any recorded macro, even though both are “coded” in VBA.
Figure 1 clearly lays out the algorithm that is needed to implement printing the student summaries in a batch. All algorithms are directions (or a recipe) that guide your coding. This one is simple:
Figure 1. Batch Print Algorithm.
All of the Office applications have a built-in editor for VBA. To display the editor simply press the ALT and F11 keys on the keyboard simultaneously.
Figure 2. Animation of how to insert a VBA code module.
On the left-hand side of the editor you will notice the Project Explorer, listing the worksheets in the Gradebook. While we could place VBA code in any of these worksheets, the best practice is to add a dedicated code module to the workbook. Just click on the Gradebook in the Project Explorer, and then select Module from the Insert menu at the top of the editor.
Notice that Module1 is added to the workbook. Now double-click on the module and you’ll be presented with a blank area on the right. This is where you enter the VBA code. The very first thing we should do at the very top of this code module is type the words: Option Explicit.
This is another best practice and should be at the top of all code modules. This forces the VBA interpreter to insist that all variables are defined before they are used. This may seem silly, but in larger programs this simple step can save untold grief when debugging your program.
To run our coded recipe, it needs to be contained in a defined subroutine. This is done by simply typing the keyword, Sub, followed immediately by the name we wish to assign to the routine. Let’s call ours PrintAllSummaries:
Sub PrintAllSummaries()
End Sub
Notice that as soon as we enter the line, the editor adds another line below it to terminate the subroutine container. All of the code for our little program will be entered between these two lines.
Next we need to define three variables that will be used while the program is executing:
Dim i as Long
Dim sCount as String
Dim vStudents as Variant
Dim is the VBA keyword that defines a variable. It stands for dimension. So the first line here reads, dimension i as long. This creates a variable that we can refer to by the name of “i,” and it is dimensioned as a long integer. In VBA when you create variables, you specify what kind of data each variable is allowed to hold. A Long (integer) refers to numeric data with no decimal component. A String variable type is one that will hold text values. And a Variant is one that can hold any kind of data. So we know sCount will hold text and something interesting must be in store for vStudents.
Now that our variables are defined, the very next step is to specify what should happen if an error occurs during program execution:
On Error Resume Next
Error handling is a large subject, but this little line of code is perhaps the easiest way to implement very basic error management. It simply tells the compiler to move on to the next line of code if any error is encountered. For many circumstances this would not be sufficient, but for our little program, this works well.
Now let’s get a list of all of the students in the class. It is possible to pluck them one at a time from the worksheet when needed, but another best practice is to retrieve all of the data at once. Fortunately this template comes with a defined name that references the column in the table that holds all of the student names: StudentLookup. It is currently being used to produce the data validation drop list in cell B8 of the Student Summary sheet.
So we can retrieve the entire list of students into our variant variable with the following line:
vStudents = [StudentLookup]
What this line really does is place a two-dimensional array within vStudents. Arrays are variables that contain more than one value at the same time. A two-dimensional array has rows and columns, just like a worksheet. To get at the student names by referencing this array is hundreds of times faster than repeatedly referencing the worksheet cells to get the names. Not that it would make much difference on this tiny program, but again, best practices.
Now we are ready to code the algorithm. In actuality, the code to implement our algorithm is short, but we need to add some supporting code to verify that a teacher truly wishes the batch of reports to be sent to the printer. For example, a class of 35 students will result in 35 sheets of paper being printed. If the routine is run by accident, we had better give teachers a way to opt out.
To do that, we need to display a prompt that tells a teacher how many reports will be sent to the printer, and asks if they wish to continue. To do that properly, let’s temporarily set i equal to the number of elements in the array, which is exactly the number of students in the table:
i = UBound(vStudents)
And now we can use our text variable to hold that number and the correct plurality of students:
If i > 1 Then
sCount = i & " students "
Else
sCount = i & " student "
End If
The ampersand character joins strings of text together. Now we can use the text string stored in sCount as part of the prompt we display to the user. In VBA, the easiest way to prompt the user is to use the MsgBox command. MsgBox can be used like a function in this manner:
If MsgBox("A progress report for " & sCount & "will be sent directly to the printer." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Student Gradebook") = vbYes Then
End If
Notice the underscore characters at the ends of the lines. In VBA, these are line continuation characters. We use them so we do not need to scroll horizontally to see an entire line. So the first three lines are really one line of VBA code. vbYes is a built-in constant in VBA. If the user clicks the “Yes” button when the prompt is displayed, then MsgBox will return a value of vbYes.
Whatever we want to happen when the user clicks the “Yes” button should be placed between the IF and End If lines above. If the user does not click the “Yes” button on the prompt, then our little program will end because there will not be any more code below this block.
Now all that is left to do is place the coding of our recipe within the IF/End If block:
For i = 1 To i
[StudentName] = vStudents(i, 1)
DoEvents
Sheet2.PrintOut IgnorePrintAreas:=False
Next
That’s the whole recipe, the algorithm from Figure 1, coded in VBA. Tiny isn’t it?
Notice the For/Next block? This is one of VBA’s many looping structures. It is very simple. It causes the flow of execution to loop a predefined number of times. If it read, For i = 1 to 10, then the flow would loop ten times and on each iteration, the value stored in the variable i would increase by one, starting with the value of one.
In our case, we are reusing the variable i. Since i already has a value before this loop is encountered, the loop will iterate whatever number of times is stored in i prior to the loop. Remember that number is equal to the number of students in the class.
So what happens in this loop? Cell B8 on the Student Summary worksheet was named, “StudentName” by the template builder. We can reference that cell by placing its name in square brackets. Consequently the following line will populate that cell with the current student in the list:
[StudentName] = vStudents(i, 1)
Remember vStudents contains our two-dimensional array of student names, organized into rows and columns. Even though it has two dimensions, all of our students are in the first column, so we can hard code the value of 1 in the second index. Notice that the variable, i, is in the first index. This is where the rows are. So when i is one, the first student is retrieved from the array; when i is two, the second student is retrieved; etc. After we place the current student name in cell B8, we need to let the workbook recalculate and update the report to show the data for the current student. This actually happens automatically, but to be safe we can use the DoEvents command, which momentarily allows the operating system to process queued events.
Finally we print the report for the current student:
Sheet2.PrintOut IgnorePrintAreas:=False
The code name for the Student Summary worksheet is Sheet2. This can be seen in the project explorer in the Visual Basic Editor. This allows us to refer to that sheet as simply Sheet2 from VBA. One of the many methods that worksheets expose to VBA is the PrintOut method, which prints the current sheet to the default printer. We call it via a dot after the sheet name. It has many optional parameters that can be tacked on at the end. We have chosen to honor the defined print areas, as defined on the Page Layout tab of the Ribbon. That’s it!
Remember that the For/Next loop ensures that a report is printed for each student.
Now save the workbook. You should select the *.xlsm file type when you do. This is for workbooks with macros, or VBA code in them.
Here is the entire subroutine:
Sub PrintAllSummaries()
Dim i As Long
Dim sCount As String
Dim vStudents As Variant
On Error Resume Next
vStudents = [StudentLookup]
i = UBound(vStudents)
If i > 1 Then
sCount = i & " students "
Else
sCount = i & " student "
End If
If MsgBox("A progress report for " & sCount & "will be sent directly to the printer." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Student Gradebook") = vbYes Then
For i = 1 To i
[StudentName] = vStudents(i, 1)
DoEvents
Sheet2.PrintOut IgnorePrintAreas:=False
Next
End If
End Sub
As you can see, this routine is custom coded to enable batch printing of the reports produced by this particular workbook. Similar techniques can be employed to create a custom batch printing solution for just about any workbook. The key is updating the correct cell or cells with the appropriate data and iterating the proper number of times. These details will be different in every situation, but the technique is the same. To run the routine, you can select it from the Macro dialog in the normal Excel window. Press ALT-F8 on the keyboard to bring it up. Alternatively, you can insert a text box on the worksheet and assign the macro to run when the text box is clicked. To do so, right-click the text box and select Assign Macro, and then select PrintAllSummaries from the list.
With Visual Basic for Applications you can automate virtually every part of Excel, and all of the other Microsoft Office applications as well. While this article has demonstrated an almost trivial (yet very useful) solution using VBA, truly impressive custom solutions can be developed this way. This author has made a living doing just that for a very long time. VBA is a subset of the venerable Visual Basic 6.0 general purpose programming language. Microsoft very cleverly incorporated this subset into the Office Suite, while at the same time exposing hundreds of native Office objects to the programming language. VBA’s heritage is much older than Excel, but the marriage of the technologies has produced an incredibly potent tool to develop custom business solutions.
To take a peek at the working script, download the Excel 2010 gradebook template.
--Daniel Ferry
Daniel Ferry is a Microsoft Excel MVP and solution developer. He operates an Excel enthusiast website and blog called Excel Hero. He also operates the Excel Hero Academy, where he teaches advanced Excel, online, to students from all over the world.
No comments:
Post a Comment