Dim intCounter, intAge, intWeight As Integer
Dim intCounter As Integer, intAge As Integer, intWeight As Integer
Dim intCounter As Integer
Dim strName As String
As you can see, strong-typing declares the name of the variable as well as the type of data it can contain. This type of declaration enables the compiler to catch errors, such as storing a string in an integer variable, before your program runs. If implemented properly, this method can also reduce the resources needed to run your programs by selecting the smallest practical data type for each variable.
Private Sub cmdOkay_Click
Dim strAnimal As String
strAnimal = "Dog"
Call ChangeAnimal
Debug.Print strAnimal ‘Still Dog
End Sub
Private Sub ChangeAnimal
strAnimal = "Cat"
End Sub
Private Sub cmdLocalAge_Click()
Dim intAge As Integer
intAge = intAge + 1
Me.txtNewAge.Value = intAge
End Sub
Each time this code runs, the Dim statement reinitializes intAge to zero. This is quite different from the following code, which illustrates the use of a static variable:
Private Sub cmdStaticAge_Click()
Static sintAge As Integer
sintAge = sintAge + 1
Me.txtNewAge.Value = sintAge
End Sub
[General Declarations]
Option Explicit
Private mintAge As Integer
You can change the value of a variable declared as private from any subroutine or function within that module. For example, the following subroutine increments the value of the private variable mintAge by 1. You can run this code by opening the form named frmScopeAndLifeTime and clicking the Module Age button.
Private Sub cmdModuleAge_Click()
mintAge = mintAge + 1
Me.txtNewAge.Value = mintAge
End Sub
Notice the naming convention of using the letter m to prefix the name of the variable, which denotes the variable as a private module-level variable. You should use private declarations only for variables that need to be seen by multiple procedures in the same module. Aim for making most of your variables local to make your code modular and more bulletproof.
Option Explicit
Public gintAge As Integer
Private Sub cmdPublicAge_Click()
gintAge = gintAge + 1
Me.txtNewAge.Value = gintAge
End Sub
Private Sub cmdIfThenElse_Click()
If IsNull(Me.txtName.Value) or IsNull(Me.txtAge.Value) Then
MsgBox "Name or Age is Blank"
Else
MsgBox "Your Name Is " & Me.txtName.Value _
& " And Your Age Is " & Me.txtAge.Value
End If
End Sub
This code tests whether the text box called txtName or the text box txtAge contains a Null. A different message is displayed depending on whether one of the text boxes contains a Null value.
If IsNull(Me.txtvalue.Value) Then MsgBox "You must enter a value"
Sub MultipleIfs(intNumber As Integer)
If intNumber = 1 Then
MsgBox "You entered a one"
ElseIf intNumber = 2 Then
MsgBox "You entered a two"
ElseIf intNumber >= 3 And intNumber <= 10 Then
MsgBox "You entered a number between 3 and 10"
Else
MsgBox "You entered some other number"
End If
End Sub
The conditions in an If statement are evaluated in the order in which they appear. For this reason, it’s best to place the most common conditions first. After a condition is met, execution continues immediately after End If. If no conditions are met, and there’s no Else statement, execution will also continue immediately after End If.
Function EvalSales(curSales As Currency) As String
EvalSales = IIf(curSales >= 100000, "Great Job", "Keep Plugging")
End Function
This function evaluates the curSales parameter to see whether its value is greater than or equal to $100,000. If it is, the function returns the string "Great Job"; otherwise, the function returns the string "Keep Plugging".
=IIf(IsNull(Forms!frmOrders.txtFreight.Value),0,_
Forms!frmOrders.txtFreight.Value)
This expression displays either a zero or the value for freight in the control called txtFreight.
Sub cmdConditionalCompilation_Click()
#If Language = "Spanish" Then
MsgBox "Hola, Que Tal?"
#Else
MsgBox "Hello, How Are You?"
#End If
End Sub
Sub ConditionalIf()
#If Language = 1 Then
MsgBox "Hola, Que Tal?"
#Else
MsgBox "Hello, How Are You?"
#End If
End Sub
Note
Private Sub cmdCase_Click()
Dim intAge As Integer
intAge = Nz(Me.txtAge.Value, 0) Select Case intAge
Case 0
MsgBox "You Must Enter a Number"
Case 1 To 18
MsgBox "You Are Just a Kid"
Case 19, 20, 21
MsgBox "You are Almost an Adult"
Case 22 to 40
MsgBox "Good Deal"
Case Is > 40
MsgBox "Getting Up There!"
Case Else
MsgBox "You Entered an Invalid Number"
End Select
End Sub
This subroutine first uses the Nz function to convert a Null or empty value in the txtAge control to 0; otherwise, the value in txtAge is stored in intAge. The Select Case statement then evaluates intAge. If the value is 0, the code displays a message box with You Must Enter a Number. If the value is between 1 and 18 inclusive, the code displays a message box saying You Are Just a Kid. If the user enters 19, 20, or 21, the code displays the message You are Almost an Adult. If the user enters a value between 22 and 40 inclusive, the code displays the message Good Deal. If the user enters a value greater than 40, the code displays the message Getting Up There!; otherwise, the user gets a message indicating that she entered an invalid number.
Sub cmdDoWhileLoop_Click()
Do While Nz(Me.txtAge.Value)< 35
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Loop
End Sub
In this structure, if the value in the txtAge text box is greater than or equal to 35, the code in the loop is not executed. If you want the code to execute unconditionally at least one time, you need to use the following construct (found under the Do…Loop While command button of the frmControlStructures form):
Sub cmdDoLoopWhile_Click()
Do
Me.txtAge = Nz(Me.txtAge.Value) + 1
Loop While Nz(Me.txtAge.Value) < 35
End Sub
This code will execute one time, even if the value in the txtAge text box is set to 35. The Do While…Loop in the previous example evaluates before the code is executed, so it doesn’t ensure code execution. The Do…Loop While is evaluated at the end of the loop, and therefore guarantees execution.
Sub cmdDoUntil_Click()
Do Until Nz(Me.txtAge.Value) = 35
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Loop
End Sub
This loop continues to execute until the value in the txtAge text box becomes equal to 35. The Do…Loop Until construct (found under the Do…Loop Until command button of the frmControlStructures form) is another variation:
Sub cmdLoopUntil_Click()
Do
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Loop Until Nz(Me.txtAge.Value) = 35
End Sub
As with the Do…Loop While construct, the Do…Loop Until construct doesn’t evaluate the condition until the end of the loop, so the code in the loop is guaranteed to execute at least once.
Private Sub cmdEfficient_Click()
Dim intCounter As Integer
intCounter = Nz(Me.txtAge.Value)
Do While intCounter < 35
intCounter = intCounter + 1
Loop
Me.txtAge.Value = intCounter
End Sub
Caution
Sub EndlessLoop()
Dim intCounter As Integer
intCounter = 5
Do
Debug.Print intCounter
intCounter = intCounter + 1
Loop Until intCounter = 5
End Sub
This code snippet sets intCounter equal to 5. The code in the loop increments intCounter, and then tests to see whether intCounter equals 5. If it doesn’t, the code in the loop executes another time. Because intCounter will never become equal to 5 (it starts at 6 within the Do loop), the loop executes endlessly. You need to use Ctrl+Break to exit the loop; however, Ctrl+Break doesn’t work in Access’s runtime version.
Sub cmdForNext_Click()
Dim intCounter As Integer
For intCounter = 1 To 5
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Next intCounter
End Sub
Note that intCounter is self-incrementing. The start value and the stop value can both be variables. A For…Next construct can also be given a step value, as shown in the following (the counter is incremented by the value of Step each time the loop is processed):
Sub ForNextStep()
' Note that this code is not in database Chap78ex.mdb
Dim intCounter As Integer
For intCounter = 1 To 5 Step 2
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Next intCounter
End Sub
Private Sub cmdWithEndWith_Click()
With Me.txtAge
.BackColor = 16777088
.ForeColor = 16711680
.Value = "40Hello World"
.FontName = "Arial"
End With
End Sub
This code performs four operations on the txtAge text box, found on the form it’s run on. The code modifies the BackColor, ForeColor, Value, and FontName properties of the txtAge text box.
Private Sub cmdForEachNext_Click()
Dim ctl As Control
For Each ctl In Controls
ctl.FontSize = 8
Next ctl
End Sub
This code loops through each control on the form, modifying the FontSize property of each control.
Private Sub cmdForEachWith_Click()
Dim ctl As Control
For Each ctl In Controls
With ctl
.ForeColor = 16711680
.FontName = "Arial"
.FontSize = 14
End With
Next ctl
End Sub
This code loops through each control on a form; the ForeColor, FontName, and FontSize properties of each control on the form are modified.
Private Sub cmdPassParameters_Click()
Call Initials(Nz(Me.txtFirstName.Value), Nz(Me.txtLastName.Value))
End Sub
Sub Initials(strFirst As String, strLast As String)
' This procedure can be found by selecting General in
' the Object drop-down list in the VBE window
MsgBox "Your Initials Are: " & Left$(strFirst, 1) _
& Left$(strLast, 1)
End Sub
Notice that the values in the controls txtFirstName and txtLastName from the current form (represented by the Me keyword) are passed to the subroutine called Initials. The parameters are received as strFirst and strLast. The code displays the first left character of each parameter in the message box.
Private Sub cmdReturnValues_Click()
Dim strInitials As String
strInitials = ReturnInit(Nz(Me.txtFirstName.Value), _
Nz(Me.txtLastName.Value))
MsgBox "Your initials are: " & strInitials
End Sub
Function ReturnInit(strFName As String, strLName As String) As String
' This procedure can be found by selecting General in
' the Object drop-down list in the VBE window
ReturnInit = Left$(strFName, 1) & Left(strLName, 1)
End Function
Notice that this example calls the function ReturnInit, sending values contained in the two text boxes as parameters. The function sets ReturnInit (the name of the function) equal to the first two characters of the strings. This returns the value to the calling routine (cmdReturnValues _Click) and sets strInitials equal to the return value.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Voted "YES" above.
Commented:
Her work is always worth reading.
"Yes" vote above.