It’s Chinese New Year! (Well, a bit later than CNY, anyway.) 祝大家新年快乐，身体健康，万事如意，学业进步，出入平安，龙马精神！
I used to dislike the Message Box function in VBA. I thought it was useless and annoying — I really hate it when I’m working and suddenly something pops up on my screen asking me a simple question that could have been displayed in a far less distracting format. But after thinking (and a lot of doodling of anime characters) later, I realised that Message Boxes could actually be useful, as long as I planned them so that they distracted the user when the user needed to be distracted for something important.
(I wanted a picture here of popups getting too annoying, but then decided against it after seeing the stuff that people get spammed.)
So I thought about it. What was a problem I still had not addressed that would often need the booker’s immediate attention?
I had known this before. My graphing display at the front page — grouping together bookings of similar types, regardless of order booked — worked perfectly until more than 20 people booked a class. There was no stopping; the macro was looped to continue until the bookings were completely over, meaning that they went on across the page and into bits I didn’t want it to.
It would be too complex to program something to stop it when it reached 20 people, so I came up with a compromise; the system would alert the user that the class was overbooked.
I took this thought all throughout my Chinese New Year half-term holiday (a week long, coincidentally) and finally programmed a workable system on the flight back from China.
The Welcome page hasn’t changed that much; all that visually happened was I added a new button, “Check Classes”. This will be removed in the last version, and, to be honest, the only reason why I put it there was because I wanted to run a macro without having to keep going into Developer-Macros.
I wanted to create something like this; a prompt that asked the user if they wanted to proceed. While I could have included this in every single macro, I chose to only write it here for one simple reason: this prompt only appears after a series of checks on overbooking has run.
In short, I wrote a macro that checks all the classes to see if any of them exceeded 20 people in the class. If it didn’t, then this would be returned, informing the user that there are no detectable problems with the classes and asking if them if they want to update the graph. This gives the user an extra option: they are given the ability to directly check for overbooking problems without updating the graph and wasting time.
(Updating the graph with the macro takes around 5 seconds, which is awkwardly long on the phone.)
As soon as a class is overbooked, however, alternative procedures are called into action. (Thanks to IF-THEN-ELSE.) The macro returns a message informing the user that there is a class overbooked. It tells the user which class (Monday 10am to 12pm, Wednesday 5pm to 7pm etc.) and by how many people it’s overbooked so the user can manually call up people and ask them if they’d like to move classes.
This is achieved with the
MsgBox parameters, which I so quickly learnt off the Excel object browser and this nice guide here.
The problem really only starts happening when more than one class is overbooked. (Unlikely for a couple of years.) The original code couldn’t handle it, so I modified it to display something like what’s on the left. While it’s very crude and grammatically terrible, it works and it does tell the user some simple information.
The next step to all this bother with overbooking would be to write a system that automatically rebooked people so that they were distributed across other classes that have empty spaces. Alternatively, the system could also issue an email to everyone in the overbooked classes, sadly informing them of the unfortunate mix up and asking if they would like to change classes; this could even list the classes that are still available.
But I like my third idea, which is to write a system that closes down the option for a class as soon as it’s fully booked. This would be utilised early in the booking process, where the macro checks to see if any classes are full and grey out that option, or, more simply, give a message that the class is full and ask for another class to be entered. This would reduce problems and make it easier for the user to ask the booker if they’d like to change.
The code needed for this simple job is very complicated; the second most complicated code I’ve entered in this project, after the code for the booking graph itself. The majority of it is just a bunch of IF-THEN-ENDIFs checking the number of bookings for each class. The important bit is the last couple of lines; if there are any overbooked classes, the system gives a “critical” message box and the information on the classes. Otherwise, it gives the simple prompt and assigns a different value to ClassResult depending on what the user wants to do.
Here, I’ve also learnt that variables can store anything. A number, a string of text, a reference, anything. I used variables to store the values of bookings per class, used a variable to help communicate the user’s response to a message box, and even used a variable to store the text finally displayed on the overbooked error alert.
Notice that, in the midst of variables declared at the beginning of the macro, none of them are called ClassResult. This is because the variable acts like a signal, telling the PresentLessonData macro whether it should proceed. When ClassResult stores a value of “Error”, the graph will not be updated; otherwise, the graph does. It does not matter whether the “Error” was caused by an overbooked class or by the user manually cancelling the macro; it will return “Error” to stop the macro.
The thing with VBA is that it destroys any variable within a sub as soon as the sub terminates at
End Sub. This is nice when you’re working on a similar bit of code that only changes slightly per sub; I repeatedly use the variables ‘i’,’j’,’a’,’b’, and ‘c’ in separate subs; but because I’m trying to communicate across macros, this presents a problem.
Of course there are solutions. There are two in this situation; rewrite one sub as a function and call it to return a value, which would be completely stupid considering that both of these were written as macros to begin with, or declare a public variable.
A public variable is a variable not attached to any particular sub; it exists outside of all macros and can be called and edited by any sub. This makes it perfect for cross-macro communication; the variable and its values are not destroyed when the sub terminates.
By doing this
PUBLIC declaration, I’ve made sure that the variable is not destroyed when the
CheckClass macro ends. This way, I can use the variable to tell the graphing macro to not proceed because there’s an error:
The blank line basically means: nothing. The macro does nothing if ClassResult holds a value of “Error”, entered earlier in the
CheckClasses sub; otherwise, it continues on with updating the graph and grouping everything and making a pretty looking display on the welcome page.
Additionally: I’ve remembered while writing this post that the user has no way of recording a person’s phone number with the system. This means that it’s not possible to call back someone and ask them if they’d like to change their time. I’ll have to look into changing the code so that it additionally records the phone numbers; but text masking is such an effort with Excel for Mac.
I’ll write about that next time.