Me, programs, examinations and coursework.

You have (1) New Message

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?

Class overbooking.

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.

A new and updated Welcome Page. The only visible thing that changed was a button that was only installed for developer purposes.

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.

The macro returns this when the classes are not overbooked.

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

Critical! A class is overbooked!

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.

Very Critical! There's more than one class overbooked!

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 new macro and most of its programming. This section misses two END IFs and an END SUB at the end.

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.

Declaring a public variable. See how it exists without any subs.

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:

An updated PresentLessonData sub. It is now empowered with the extra ability to do nothing.

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.

Comments on: "You have (1) New Message" (2)

  1. adriandakota said:

    A status update: I’ve looked at the original code, and realised that when it brings up a prompt, it will print a comma and space at the beginning of every class except for Monday Session 1.

    This is fine if Monday Session 1 is overbooked, but if it is not and other ones are, the final text will be like:

    ‘, Monday 5pm to 7pm is overbooked by 5 people.’

    I think the way to fix this is to declare another variable, and the function checks the value of that variable. If there’s no previous error, then it would not print the comma or space.

    This variable could also be used to change the final list: I could declare two variables, one that is the number of errors, the other is the number of errors printed; this way, I could add an “and” to the last error in the loop.

  2. […] You have (1) New Message ( […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s