Changing Pages - An introduction into VBA Codes

First of all I apologise for the recent slackness, I've had a change in computer, which necessitated a few changes in applications and hardware. Glad to be back on line.

In the last few weeks, the topic of discussion had been how to set up various scales - Multiple Responses, Continuous, and Likert. I haven't written a single word of code yet. All that is about to change - we're going into Visual Basic for Applications (VBA) codes.

This is triggered by a colleague's query. He is writing a study which involves two questionnaires, with the manipulation and filler questions in between. Easy enough to just put them all into a single Excel file, let the participants scroll down, right? No - it's a memory study; therefore he does not want his subjects to see their response to the first survey by the time they reach the second survey. With its current setup, subjects can easily just scroll back up, and they remember exactly what their response was. The manipulation and fillers in between would be rendered useless!

One way to do this is to put the surveys, manipulations and filler questions into separate sheets in an Excel file. Ordinarily, you navigate between the sheets by hitting the tabs at the bottom of your sheet:

But once again, same problem: participants can easily return to the sheet that contains their previous answers, and cheat their way in subsequent questions. We need something that allows participants to (1) easily switch pages and (2) can't go back to see their previous answers.

Say hi to VBA Land!

The Visual Basic Window

To access this window, press Alt-F11 (or it is available under Tools --> Macro --> Visual Basic).

Go to "Insert" --> "Module".

Now, get ready to write you first codes: In the "Module1" box, write the following:

Sub ChangePage()

Then hit enter.

You will notice that, two rows below that, the words "End Sub" would also appear. That is normal - "Sub" stands for Sub-Procedure, which are commands that tell Excel to automatically carry out certain instructions automatically. Also, the word "ChangePage" carries no significant meaning - you can change it to "PageChange", "Page1to2", or "Mickey Mouse" as you please - so long as you remember what it's for. That whole bunch of codes you just wrote is called a "Macro"

(Just a note here: as I said earlier on, as much as I'd love for this to be a one-stop shop for all VBA-related queries, that is just not possible. Not only do I have a lot to learn about VBA; I'm just not completely up to speed with every technical detail involved in VBA programming. For a pretty good summary of the technicalities of VBA, see this tutorial from FunctionX.)

Now, in the space between Sub ChangePage() and End Sub, type the following:

Sheets("sheet2").Visible = True
Sheets("sheet2").Select
Sheets("sheet1").Visible = False

Now your Module1 should look like this:

(You might wonder why "Sheets..." are all indented. Excel VBA does not automatically do that for you. I prefer to keep relevant blocks of codes indented, so I can find them easily. This is not so important now - it won't affect its performance - but will be very useful when you have bigger blocks of codes.)

Not meaning much to you? Don't worry. Let's move on to see how to put this jibberish into action.

Go back to your blank Excel sheet and choose Sheet1. Go to Developer --> Controls --> Insert --> Form Controls --> Form Control.

(In Excel 2003, you have to right click in your menu bar, tick "Controls" to bring up the Controls toolbar. You should see the button in that toolbar.)

Once you have selected the Button, either click somewhere on the sheet (which will bring up a button with default size), or drag it on the sheet to create a button with your desired size. I did the latter because I like to control. When you release the mouse button, a dialogue box appears as follows:

You are now asked to assign the Macro to this particular button.

Notice that the Macro we created earlier, ChangePage, is in this box. In fact, the "Assign Macro" box would contain every Macro that you create. Select ChangePage, and press OK. That would make your new button follow the ChangePage procedure we just created. Click OK, click anywhere on the sheet, then click on the button.

Yes, you should find yourself in Sheet2, with Sheet1 nowhere in sight. The ChangePage Sub-procedure that we created has served the purpose of switching to another sheet, and hiding the first page.

This method is not entirely fool-proof. A slightly Excel-savvy subject could bring Sheet1 back by going to Format --> Sheets --> Unhide, and choose "Sheet1" from the dialogue box. (By the way, that's how you could bring Sheet1 back yourself.) How to get around that requires a little security - that's for another article.

So the take-home points are:

• The Visual Basic window is where you write commands - or Macros - to run automated actions.
• The Macro can be assigned to a control - most commonly a button - to become useful.

Stay a step ahead,

Alex.