Experimenting with Visual Basic:

In this example, I am going to use VBA to do some simple mathematics - add, subtract, multiply and divide.

This shows the form. The idno and ans are bound to a file, the numbers be entered for calculation are unbound. In other words the answer will be put on the file.

At the time this was run, a calculation was done on the first record and the answer is stored on the file. No other records had calculations done.

idno

ans

111

300

222

0

333

0

444

0

555

0

666

0

Now I am going to look at the code that goes with the buttons. Note there are buttons to Add, Subtract, Multiply, Divide and Clear. All of them are based on the click event associated with the button. Note that when I put the buttons on the form, I had the wizard deactivated because I wanted to write my own code. The code is a simple calculation except for the Divide. Here, I check to make sure that I am not going to be dividing by zero. If I am, then a message box goes up and the divide is not done. The divide is only done when I will not be dividing by 0.

See the code for the events:

 

Private Sub cmdAdd_Click()
   txtAnswer = Val(txtFirst) + Val(txtSecond)
End Sub

Private Sub cmdClear_Click()
   txtAnswer = 0
   txtFirst = 0
   txtSecond = 0
End Sub

Private Sub cmdDivide_Click()
   If txtSecond = 0 Then
       MsgBox ("Cannot divide by 0")
   Else
       txtAnswer = Val(txtFirst) / Val(txtSecond)
   End If
   
End Sub

Private Sub cmdMultiply_Click()
   txtAnswer = Val(txtFirst) * Val(txtSecond)
End Sub

Private Sub cmdSubtract_Click()
   txtAnswer = Val(txtFirst) - Val(txtSecond)
End Sub

Now I am going to get into another example involving a calculation. This time I am going to add records to a file as I fill in the form. The user will fill in the Name and Address of the customer - I let the ID be an automatic number generated by the system. In addition the user will fill in the weight of the object being shipped in pounds and ounces. The calculation is that the first pound costs $1.50 and all additional weight is in 4 ounce increments. Each additional 4 ounces or portion of 4 ounces adds $.50 to the cost. There is one click event to calculate the total amount due based on the weight. The calculate checks to see if the weight is over a pound. If not, the cost of $1.50 is assigned. If the weight is over a pound, then a loop is executed which increments the cost for every four ounces or portion thereof. The final result is displayed as the amount due. The information from the form ends up being a record on the table. The loop that is used is the DO WHILE loop.

First lets look at the form. The ID is entered automatically, the Name, StAdr, CSZ, Pounds and Ounces should be entered by the user. Clicking the Calculate button will result in the TotalDue being calculated.

The code for the Calculate button is shown here:

Private Sub cmdCalc_Click()
Rem up to 1 lb costs $1.50
Rem over 1 lb costs $1.50 plus $.50 for every
Rem additional 4 0unces
Const cstFirstLb As Single = 1.5
Const cstEvery4oz As Single = 0.5
Dim wrkWeight As Integer
Dim wrkCost As Single
wrkWeight = Val(txtPounds) * 16 + Val(txtOunces)
MsgBox (wrkWeight)
If wrkWeight < 17 Then
   wrkCost = cstFirstLb
   MsgBox (wrkCost)
Else
   wrkWeight = wrkWeight - 16
   wrkCost = cstFirstLb
   Do While wrkWeight > 0
       wrkCost = wrkCost + cstEvery4oz
       wrkWeight = wrkWeight - 4
   Loop
End If
txtTotalDue = wrkCost
End Sub

A few notes:

Rem means remark and is ignored

Const declares a constant

Dim declares or dimensions a memory variable

The MsgBox is being used to display information for debugging purposes - when the program is working those can be eliminated. I put them in for demonstration purposes.

First the weight is calculated by adding the number of pounds times 16 plus the number of ounces. If the weight is less than 16 then the weight is a pound or less so the total amount that is due is assigned a value of $1.50. If the weight is over 16 ounces then 16 ounces is subtracted from the total weight and the amount owed receives $1.50 (remember the first pound costs$1.50). Then the loop is entered and will continue as long as the weight is greater than 0. Inside the loop the $.50 (the constant is used) for each additional four ounces is added to the total cost and the weight is decreased by 4 ounces. When the weight is no longer greater than 0, the loop ends. Finally the cost is displayed on the screen.

The data in the table, after testing with three records, is shown below.

ID

Name

StAdr

CSZ

Pounds

Ounces

TotalDue

1

Mary Reynolds

43 Peach St

Providence, RI 02345

0

15

1.5

2

Linda Anderson

45 Oak St

Braintree, MA 02184

4

2

8

3

Donald Jones

345 East St

Hingham, MA 02143

2

0

3.5

The next example uses an array to find the state name when a state code is put in. This example does not use a table. However, the concept would work well when a input of a code can be used to retrieve something like a state name, an inventory item etc.

The states and codes are 1 for Maine, 2 for Vermont, 3 for New Hampshire, 4 for Massachusetts, 5 for Rhode Island and 6 for Connecticut.

This has to be done in two steps. First we need to load the table or array into memory when the form is loaded. The definition of the table is outside the routines so that it is in the general area and is therefore public and available to all of the routines. The second routine uses the state number to go to the array and get a state name. Note that the state code that is inputted is first checked to make sure that it is in the correct range (1 through 6). An out of range number that is used to access the array causes a program crash.

Option Compare Database

Dim StateName(6) As String

Private Sub cmdGetName_Click()
    Dim Ptr As Integer
    If Val(txtStateCode) > 0 And Val(txtStateCode) < 7 Then
        Ptr = Val(txtStateCode)
        txtStateName = StateName(Ptr)
    Else
        txtStateName = "Invalid State Code"
    End If
End Sub

Private Sub Form_Load()
    StateName(1) = "Maine"
    StateName(2) = "Vermont"
    StateName(3) = "New Hampshire"
    StateName(4) = "Massachusetts"
    StateName(5) = "Rhode Island"
    StateName(6) = "Connecticut"
End Sub

The two lines at the top are in the General area and available to both the Form_Load() which uses the array to put data into it and the cmdGetName_Click() routine which tests the array and if it is in the valid range gets the name from the array and displays it on the form.