Me, programs, examinations and coursework.

VBA and 2012

Happy New Year!

The holidays mean a bit of extra time for me to work, so I’ve programmed my latest project with the spare time I have. There are a couple of things I learnt, jumping into 2012. Firstly, VBA is annoying. Secondly, VBA for Windows programs is slightly different from for a Mac.

This resulted in me smashing Alex’s keyboard many times when the code I had didn’t work. Because it had been written for Office 2010 for Windows, not Office 2011 for Mac.

Maybe Microsoft should look into that.

What this has meant, though, after all those annoying debug errors, that I have a working, almost-finished project. I have a couple of screenshots just to give the updates on what I’ve done.

Intelligent Macro-based Graphs.

My welcome page. It's okay, I don't like the colouring either.

It really irritated me how the original way of booking (way back) meant that the bookings were split up. While this was fine for the original booking system for seats, I thought it was out-of-place in this booking system. It doesn’t seem representative enough of what was booked — but rather, would show me in what order the lessons were booked.

So I plunged into the VB editor and decided to write another bit of code to replace it.

The result? A loop system that fills cells up with values until the loop reaches its limit. This is especially good because it means that bookings of similar type (Adult, Student, Discount) will be together on the screen. Instead of having a firework display of colours, everything is clumped together on the screen. It’s clearer, and it’s easy to see how many places have been booked, and what proportion of them are what type.

UserForm Input and Outputs.

Multipage 1 of the new client & booking userform.

Multipage 2 of the new client & booking userform.

My registration page. Note to self: there's too much information here for anyone to handle.

One of my most fascinating discoveries of 2011 in VBA was UserForms. Before, I had to make users input their data on a sheet and then work with it. Fine, maybe, but UserForms can do that in a better way.

UserForms in Excel are little windows that pop up asking for input. It’s a bit like those guys that stand outside the counter while you’re in line and ask you what you’re ordering for lunch. By the time you get to the counter, your lunch is already there and you just have to pick it up.

Okay, bad analogy. But UserForms give more control back to the designer. Now, I can define what kind of input I want, and create labels to help the user in entering information. I can also restrict entries to only a couple in a list with ComboBoxes and ListBoxes, as well as give options with checkboxes and option boxes. It’s all self-contained, and the user won’t be change anything else.

Displaying existing data on a UserForm.

When I had finally mastered the art of designing a UserForm (which, for someone artistically challenged like me, is pretty hard), I realised I had another problem. While the UserForm was fine at asking data from the user, could it display results to the user?

Some reading, a couple of days of programming, and several test runs later, the answer was: yes.

My Invoice Management UserForm. The data is returned by the UserForm's own macros.

This Invoice Management UserForm allows the user to see and check the data that’s being sent to the invoice for printing. All of the text is displayed using labels, and the macro in the UserForm just changes the value of those labels to match the values stored on the spreadsheet.

There’s also one textbox, prepared there to allow the user to enter a discount percentage for that client. This data is later sent to the invoice as it’s printed.

Pure Calculation Pages.

A pure data page. This page will later be hidden.

This was the first time that I’ve designed the workbook to have pages that store just data. This example is the lessons page, where the data from the registration is crushed and complied into lesson-related information. Most information on the lessons can be found on this page, including the number of people in each time that have booked each specific time on each day, to the number of classes that are running through the week.

I found this easier to deal with than my earlier approach of keeping data on the pages it was going to be used on, because I discovered that this data was going to have to be referenced from multiple places. Furthermore, keeping the data-crushing on a separate page allows it to be hidden from the user’s view, so there is a lower chance the user will be bewildered by this unreadable data, or, worse, mess around with it.

Data extrapolation.

My finances page. Note the Projected Balance. And unfortunately, I'm broke.

The only major programming difference I’ve done with the finances page is add in a new “Projected Balance” function. By changing the duration under consideration, it’s possible to see future balances in one week, three months, or even twelve years. This does rely on everything else being the same — so, according to this, if no classes run for the next six years, I’ll owe the bank about $900,000. Not a good prospect.

Data entering and printing by macros.

The first half the invoice. The name, address, and email is entered by the macro.

One major advancement in VBA programming I learnt is how to use a macro to enter existing data. The name, address, and email lines are filled in automatically by the macro based on the client selected. The system also knows what ticket type the client is, and charges the correct cost. In fact, the cost is not fixed in the macro or function, but rather referenced, so if prices change, the invoice knows the new ones immediately.

Another one is a macro for printing. I found this in my reference book, and it’s a short snippet of code that allows the sheet to be sent to the printer using a macro, overriding the program’s printer selection systems. It just uses the default or last used settings of the printing system and just prints, or at least, that’s what I think it does.

UserForm input entering.

The second half of the invoice. Note the new discount entry, and the discount calculated at the bottom.

I like discounts. I like it when things are cheaper than they used to be. So I strived to create a working discount system. In this, the discount entered from the earlier Invoice Management UserForm is reflected here, both in the item description and the final discount rate. The subtotals, discount, and total is worked out with simple functions. Oh, and of course, the SUM() function.

Smart Record System.

My records page. It needs formatting.

The thing with the recording system I was taught is that it creates a new record when the button is pressed. So that’s great, but what if I forget to press the button before I quit? Or what if I don’t want to have multiple records for the same day, but only one end-of-day record?

I wrote the Smart Record System to do that. It’s called when the workbook is opened, saved, or closed, and so runs at a relatively frequent pace. There’s also a couple of IF statements programmed into the macro, making sure that certain requirements are fulfilled and different actions happen in different situations.

Firstly, it checks the previous record on the right to see when it was recorded. If it’s anything older than today, then it copies the current column of data into a new one right next to it, pushing all the columns away and preserving yesterday’s data. But if it’s the same as today, meaning that a record was already created, it simply copies the current data over it, producing a fresh set of results.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: