Static Variables Part 2: the Advantage of Static Variables

In part one of this tutorial - Using Static Variables - we examined the difference in behaviour between an ordinary local variable and a static variable.  We learnt that whilst both types can only be referenced within the same sub procedure or function in which they were declared, the value of a static variable is retained after code in the sub procedure or function has completed execution.  The terms we used to understand this behaviour were scope and lifetime respectively.

In part two, this current post, we are going to consider how we can use the unique scope and lifetime of a static variable to our advantage.  In order to illustrate this we shall examine the workings of an order counter custom control comprised of a textbox and two control buttons.

An Order Counter Control

This sort of control may be familiar to you if you order your groceries online. It usually appears under an image of a product and allows the customer to set the order amount by clicking the plus and minus buttons without having to type in the actual number in the textbox.  It is of interest to us because it lends itself to the use of a static variable to keep track of the order amount.

The advantage of Static Variables

So what is it about static variables that make them advantageous to use in contexts such as that of our order counter control?  To understand this lets take look at how the VBA code used to power this it is organised.

VBA Code Powering the Order Counter

As we can see, the coding for the form containing the order counter is comprised of five sub-procedures and a general declarations section where three constants, INITIALIZE, INCREMENT, and DECREMENT are declared.  Three of these sub-procedures, Form_Load(), cmdMinus_Click(), and cmdPlus_Click(),  contain event handling code which execute when the form opens, or the minus and plus buttons are clicked, respectively.  Each consist of one statement calling changeOrderAmount(...), the sub-procedure used to calculate the order amount displayed in the txtOrderValue textbox.  This is the location where our static variable, intOrderAmount, is declared.

It is in the changeOrderAmount(...) sub-procedure where the value of intOrderAmount is initialized, incremented or decremented within a SELECT...CASE statement, which is, in turn, based on the value of the intApplyChange argument it received when called. For example, if the  INCREMENT constant was received, the value of intOrderAmount is incremented by 1.  Likewise, DECREMENT decreases the value by 1, and INITIALIZE sets the value to 0. Note the fact that the actual order amount is not set directly when incremented or decremented, but is calculated from its previous value.  For this reason intOrderAmount has to persist across multiple calls to the sub procedure, and is why it is declared as Static.

In order to achieve the above without using a static variable, we could instead declare intOrderAmount in the general declarations section of the form's code module using a Dim  statement or the Private keyword. This gives the variable a module level scope and lifetime: it can now be referenced from any sub-procedure or function within the same module, and any value it contains will be retained while the form connected to the module remains open.  As such, the module level version of intOrderAmount has the same lifetime as our original static version declared within the changeOrderAmount(...) sub-procedure. However, since the variable only needs to be referenced from within the sub-procedure, to give it module level scope in the above mentioned way will leave it unnecessarily exposed to accidental editing as well as making our code less easy to read.

These issues arising from module level scope become more pronounced as more code is added to the module.  Other functions and sub procedures are likely to use their own variables, some of which may also have module level scope. These are generally less easy to keep track of because they can be modified from any location in the module, often under a variety of different conditions. In this sort of context it becomes increasingly difficult to read and understand our code, and  there is a greater chance of accidentally referencing a different variable to the one intended, especially if it has a similar name or performs a similar role. As such, using a static variable with a scope local to the sub-procedure or function in which it is declared, whilst also retaining it value over multiple calls, is the much better option.

Conclusion

The advantage of using static variables lies in the fact that they are, on the one hand,  declared within a sub-procedure or function, and thereby have a local scope; and on the other hand, retain their value once code in the parent sub-procedure or function has completed execution.  As a result, the variable is protected against accidental editing from code elsewhere in the project, whilst retaining its value in order to keep a running total over multiple calls to its parent sub procedure or function.  Furthermore, since static variables are declared within sub-procedures or functions, as opposed to an external location in the project, our code is more manageable, and hence easier to read and understand.  Generally speaking, our code is more streamlined and robust.

Using VBA Static Variables

In this post we are going to look at what static variables are, how they work, and how they are different from ordinary local variables.  In a future post we will build upon this and consider the advantage of  using static variables over locals, and look at an example of how they may be used in practice.

The Difference between Local and Static Variables

So what are static variables exactly? To understand this, let's begin by examining how ordinary local variables work. All variable types have there own particular scope and lifetime.  The first of these determines the locations within the program from which the variable can be accessed, and the latter determines how long the variable is active.  

Local Variables

Local variables have a scope which is limited to the sub procedure or function from which it is declared. For example:

Private Sub testThis()
    Dim intExample As Integer
    intExample = 10
    MsgBox ("The value of our test variable is " & intExample)
End Sub

Here we have declared an integer variable called intExample within a sub procedure called testThis().  Since the variable was declared within the sub procedure, it is local in scope - the value of 10 it  subsequently contains can only be accessed within the testThis() sub after the declaration statement. So if the MsgBox Statement  referencing the intExample variable is used outside the sub, the variable is not recognised. 

Furthermore, once code in the sub procedure completes execution, the intExample variable ceases to exist, and it's value of 10 is lost. The next time the testThis() sub executes, intExample's value will need to be set anew. As such ordinary local variables are limited to the length of time the sub procedure or function is executing. This is an example what is meant by a variables lifetime.

Static Variables

Now let's take a look at how the scope and lifetime of a static variable works in contrast to an ordinary local variable.  Consider the following code snippet:

Private Sub testThis()
    Static intExampleStatic As Integer
    Dim intExampleLocal As Integer
    intExampleStatic = intExampleStatic + 1
    intExampleLocal = intExampleLocal + 1
    response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
        ", whereas the value of intExampleLocal is " & intExampleLocal & ".",                     vbInformation, "Static & Local Variable Output")
End Sub

Here we have created another sub procedure called testThis(), and declared a static variable and an ordinary local variable, intExampleStatic and intExampleLocal respectively.  Notice how the declaration statement for the static variable uses the Static keyword rather than Dim ie: 

Static intExampleStatic As Integer

in contrast to ... 

Dim intExampleLocal As Integer   

Since both variables are declared as Integer, they are automatically initialized with a value of 0.  Then in the following lines of code we increment the value of  each of the variables by 1:

intExampleStatic = intExampleStatic + 1
intExampleLocal = intExampleLocal + 1

... and display both values using a message box statement: 

response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
        ", whereas the value of intExampleLocal is " & intExampleLocal & ".", vbInformation, "Static & Local Variable Output")

Now the scope of both kinds of variable, local and static, work in exactly the same way.  That is to say, static variables, along with ordinary local variables, can only be accessed from within the same sub procedure or function from which they were declared.  Where they differ is in their respective lifetimes.  Whereas a local variable ceases to exist after the code within the same sub or function completes execution, the value contained in a static variable will be retained - at least until the codes'  underlying form or report is closed, or in the case of a global module, until the database itself is closed. To demonstrate this in action, lets see what happens when the testThis() sub procedure starts being called.

The first time testThis() is called the message box output shows that the intExampleStatic and intExampleLocal variables have the same value ie 1:

It is not until the second and subsequent calls that we see the difference in lifetime between static and local variables taking effect.  This is because the value stored in the local variable is lost each time the sub procedure completes, and then re-initialized the next time it is called.  The static variable, on the other hand, retains its value from the previous call.  Hence, the output after the second call shows the static variable to have a value of 2, but the local variable again has a value of 1:

This pattern continues each time the sub procedure is called: the value of the static variable is retained and any increments are added to this retained value, whereas the local variable is created anew and any increments are applied to its initialization value of 0.  Hence the third time testThis() is called, the value of the static variable is increased to 3, and the value of the local variable remains at 1:

... and so on until the sub procedures's underlying form or report is closed.  It is not until the next time the form or report is reopened that the static variable's value is re-initialized to 0.

So this has shown us how static variables work and how they differ from ordinary local variables.  In the next blog post we are going to consider the advantages of using static variables over local variables, and will demonstrate this by creating a a simple order counter control where a user clicks two buttons to increment or decrement the quantity of an item to be ordered without having to type the actual number.

Display a Random Customer Record using VBA and Form Filter

Suppose you are responsible for managing an Access Database containing customer records.  You have been asked to introduce a contact management feature whereby the person operating the database can extract a random customer record.  This blog post walks you through the VBA code needed to generate a random number and use it to filter out a customer record, all at the click of a form button.

In order to demonstrate this we will be using the following data:

Table of Fictitious Customer Records

We will display the random customer record on the following form:

The VBA code starts when the user clicks the "Get Random" button to display a random customer record on the form. Lets begin by creating the control button.

Creating the Control Button

1. Open the form in DESIGN VIEW

2. Click the BUTTON icon in the CONTROLS group on the FORM DESIGN ribbon.

3. Click the location on the form grid where we will be positioning the button.  Resize the control button by clicking on one of the buttons' corner or edges and dragging to enlarge or contract as appropriate.

4. Click the PROPERTY SHEET icon from the TOOLS group of the FORM DESIGN ribbon if it is not already open.

5. Ensure the control button is selected. Click the button to select if it is not. Now the property    sheet is displaying the properties specific to our button.

6. Change the button CAPTION property on the FORMAT tab to "Get Random".

7. Change the button NAME property on the OTHER tab to "btnRnd"

Check how the button looks by changing  to FORM VIEW from the VIEWS group of the FORM DESIGN ribbon. If its OK, we are now ready to add the VBA code which runs when the command button is clicked.

Adding the VBA Code

1. Open the form in DESIGN VIEW, and reopen the PROPERTY SHEET if it isn't already.

2. Select the control button by clicking on it.

3. Select the EVENT TAB on the PROPERTY SHEET, and click inside the grid cell adjacent to where it says ON CLICK.

4. Click the ellipse button ("...") at the end of the cell to open the CHOOSE BUILDER dialog, Select CODE BUILDER from the drop down list and click OK to open the VBA editor.

 

You should now see an empty sub procedure called btnRnd_Click(). This is the control button's event handler.  This means the code we enter here will run when the user clicks the command button at runtime.

 

The VBA to Generate a Random Number

In order to retrieve a random customer record we need to generate a random number which corresponds to one of the customers in the database table. We can do this using an inbuilt VBA function called Rnd.  This generates a random decimal number between 0 and 1. For example, it  may return the value 0.5924582.  For this to be of any use to us, we need change the decimal number to an integer and multiply it by the number of customer records in the table.  So, for example, our table contains 10 customer records so we need to multiply the value returned by rnd by 10 and use another function called int to convert it from decimal to integer. We do this as follows:

    Int(rnd * 10)

This returns a value between 0 and 9.   We now need to add 1 to the value to get a number between 1 and 10:

    Int(rnd * 10)+1

Just one more thing to note: computers are unable to return a true random number on their own.  They return a seed number based on a pre-existing sequence of numbers. However, when we initialize the random number generator with the Randomize statement, the seed number returned is based on the system timer, which effectively makes the returned value a true random number.

So to put all this together we are going to create our own custom function to return a random integer number based on the number of records in our database table. The number returned will correspond to a particular record in the table. We shall call our function getRandom and will pass the number of customer records as the parameter. The code for the function is as follows:

Private Function getRandom(intRecordCount As Integer) As Integer  

    Randomize

    getRandom = Int((intRecordCount * Rnd) + 1)  

End Function

For more information on how custom functions work, please check out my post on Writing Custom Functions for Access VBA.

The next step is to write the VBA code to connect the random number returned by our getRandom function with it's corresponding customer record in the database table.  We shall do this by looping through a DAO Recordset containing records from the customer table.

Looping through the DAO Recordset

Before we can create the code to filter a customer record based on our random number, we need to find a way of mapping any potential number returned by the getRandom function  (in our case this is between 1 and 10) against the records in the database table. Whilst it is tempting to use the returned random number directly against the value stored in the customer_id field in a form filter WHERE clause - ie "customer_id = " & getRandom(intRecordCount) - there is a problem with this.  That is to say, although the customer id's in our table go up in an unbroken sequence from 1 to 10, it is possible that one of these records will be deleted in the future.  If and when this happens, no customer record would be found using the above WHERE clause if the random number corresponded to the deleted record. Moreover, the record count would not include the deleted record so the random number returned would be within a range which did NOT include the last record in the table.

What we must do, therefore, is create a DAO Recordset containing all the records in our customer table and use the value returned by getRandom to step through each customer record in the set until this value has been reached - eg if getRandom returns the value 8, we simply loop though the recordset to the 8th record contained therein.  Once we get to this record we can look up the actual value in the customer_id field, and then use this as the basis of our form filter WHERE clause.

If you are unfamiliar with how DAO recordsets work, you may want to check out my post on Accessing Data with VBA Code - Introducing the DAO Recordset; otherwise, lets jump straight in, and run through the code we will be using.

The first step is to create a new instance of a DAO recordset object and set its value to that of our  tblCustomer table.

Dim rstCustomers As DAO.Recordset

Set rstCustomers = Application.CurrentDb.OpenRecordset("tblCustomer", dbOpenSnapshot)

You may have noticed the second parameter we have used when opening the recordset is dbOpenSnapshot.  This means our recordset will be a static snapshot of the customer table. Unlike the versatile dbOpenDynaset type, records cannot be updated using dbOpenSnapshot, but in cases where editing is not required, the snapshot type is the more efficient option. 

In the next step we are going to count the number of records in the rstCustomers recordset and store the value in an integer variable called intCount.  To count the number of records in rstCustomers we will read the RecordCount property using the following syntax:  rstCustomer.RecordCount.  However, before doing so, it is first necessary to visit all the records in the recordset so they can all be counted. The best way to do this is to use the recordset MoveLast method which moves the recordset cursor though each of the records before pointing to the last record in the set. The syntax for this is rstCustomers.MoveLast. All this is done in three lines of code as follows:

Dim intCount As Integer

rstCustomers.MoveLast

intCount = rstCustomers.RecordCount

Now that we know how many records are in the customer table, we can use our getRandom function, passing intCount as the parameter, to obtain a random number.  We then step through the records in the recordset until we reach this number.  Once this record has been reached, we can read the value contained in the customer_id field.  

To do all this we shall begin by defining an integer variable called intCustomerId.  This will be used to store the customer id once we have arrived at the random customer record.  Before we set up the loop, we need to move the recordset cursor back to the first record using the MoveFirst  method.  This is because the cursor is still at the last record from when did the record count in the section of code above.  We will use a For...Next loop to step through the recordset, starting at the first record and ending at the record which corresponds to the value returned by our getRandom function. This is done with the following line of code: For i = 1 To getRandom(intCount).  The first statement within the loop sets the value of the intCustomerId variable to the value contained in the customer_id field of the record where the recordset cursor is located. We reference the field value using the recordset name ("rstCustomers") followed by an exclamation mark ("!") and then the field name ("Customer_Id") as follows: intCustomerId = rstCustomers!Customer_Id. Once this value is stored in our intCustomer variable we can move the recordset cursor to the next record using the MoveNext method: rstCustomers.MoveNext. Finally the loop counter is incremented by 1 with Next i.  If the end value of the loop counter had been reached before the Next statement had executed, the loop counter will still increment by 1, but the program flow will exit the loop and move to the statement following Next i.  

Dim intCustomerId As Integer

rstCustomers.MoveFirst

For i = 1 To getRandom(intCount)

           intCustomerId = rstCustomers!Customer_Id
           rstCustomers.MoveNext

Next i    

 

So we have stepped through the recordset and arrived at our random record storing the value of it's customer_id field in the intCustomerId variable.  Now the program flow has exited the loop we are going to use the intCustomerId value to display the record on our form.  This will be done by means of a form filter containing a WHERE clause based on the stored customer_id. The syntax for the WHERE clause is "Customer_id = " & intCustomerId and the syntax to create form filter is DoCmd.ApplyFilter filtername, whereclause, controlname.  Although the ApplyFilter method has three parameters, we are just going to use the second - the WHERE clause.  As such we write the full statement as follows:

DoCmd.ApplyFilter , "Customer_id = " & intCustomerId

(NB Although the first parameter is left blank we still need the separating coma just before the WHERE clause string.  The last parameter is also left blank, but a separating coma after the WHERE clause is not needed.)

Lastly we need to close the rstCustomer recordset and set its value to NOTHING.  This clears the memory that it had been taking up and allows us to reopen the recordset again the next time the user clicks the getRandom button. We place this code within a IF conditional statement which checks the recordset is still open before it executes thereby avoiding any error from occurring if it is not.

If Not rstCustomers Is Nothing Then

    rstCustomers.Close: Set rstCustomers = Nothing

End If

All we need to do now is look at how all the code we have run through above is organised in the code module for the frmCustomer form.

As you can see from the screenshot above, our code is divided into two Sub Procedures and one Function, btnRnd_Click(), RandomCustomerRecord() and getRandom() respectively.  When the user clicks the Get Random button on the Customer Form, the code in the btnRnd_Click() sub executes first. This is the event handling sub that Access created automatically when we set up the control button at the start of the tutorial.  There is just one statement here calling the second sub procedure which is RandomCustomerRecord().  This contains the main body of the code for retrieving the random customer record from the database and displaying it on the form.  The reason for giving this it's own sub rather than placing it in the code handler, is that we can potentially reuse this code by calling it from different locations in the program as required.  The only task this code does not do is generate the random number representing a specific customer record for selection.  This code is located in the getRandom() Function which is called from the FOR statement of the FOR...NEXT loop. The number returned from the function determines how many records are stepped through in the rstCustomers recordset before getting to the record which is to be displayed right at the end of the process.

Once RandomCustomerRecord() has the random number and retrieved the customer record from the recordset, the form filter is applied using the value of cutomer id.  The form will then display the record with the same customer_id as that stored in the sub's intCustomerId variable.

And there we have it: a random customer record at the click of a button!