.jpg "Microsoft Certified Professional")
VI - Operators
Whether you're programming a method or a function, there's an excellent chance you're going to be using formulas of some kind to get the results you need. In order to construct these formulas and perform the right calculations, you need to be familiar with the operators available in Visual Basic for Applications.
The operators in Visual Basic for Applications (VBA) are mostly the same as the ones that you used in high school math class (+, -, =, etc..) with a few extra that are specific to the programming environment. Here's a small example of how operators could be used in VBA. I've numbered the lines here for reference.
Public Function IsPrime(Candidate As Long) As Boolean
'Determine if the number passed in is a prime number.
'Declare the variables.
!: Dim TestLimit As Long, TestValue As Long
'Assume it is prime until proven otherwise.
2: IsPrime = True
'If the number is higher than 3, test against every
integer up to
'the square root of the number.
3: If Candidate > 3 Then
4: TestLimit = Int(Sqr(Candidate))
5: For TestValue = 2 To TestLimit
6: If Candidate Mod TestValue = 0 Then
7: IsPrime = False
8: Exit For
9: End If
10: Next
11: Else
12: IsPrime = False
13: End If
End Function
As the comments indicate, this function accepts a long integer (a data type capable of holding numbers between - 2,147,483,648 and 2,147,483,647) and determines if it's a prime number (a number only divisible by 1 and itself).
The function returns a boolean value and on Line 2, you can see where the code uses the assignment operator (=) to set the return value to True. In VBA, like other forms of BASIC, the equals sign doubles as the operator used to assign a value to a variable and to test for equality between two values.
On Line 3, the function uses the greater than operator (>) to find out if the number passed in is greater than 3 since there's no point testing values lower than 3. Then on Line 6, it uses the Mod (modulus) operator which returns the remainder when one number is divided into another. In this case, it's looking for a value of 0 indicating that there is no remainder. If the For Next loop is able to cleanly divide the candidate number by another number, a False result is returned.
The Access help files contain notes on all the different operators available and their behavior when used with different data types but here is a short listing of those you might use.
| Operator Sign | Description | Example |
| Arithmetic | ||
| + | Addition | Total = Price + Tax |
| - | Subtraction | Total = Price - Discount |
| * (asterisk) | Multiplication | Tax = Price * .06 |
| / | Division with floating point result | Days = Hours / 24 |
| \ | Division with integer result | YearValue = Months \ 12 |
| Mod | Division remainder | Remainder = Months Mod 12 |
| ^ | Exponent | 1000 = 10 ^ 3 |
| Comparison | ||
| < | Less than | Month < Year |
| <= | Less than or equal to | 364 Days <= Year 365 Days <= Year |
| > | Greater than | Decade > Year |
| >= | Greater than or equal to | 10Years >= Decade 20Years >= Decade |
| <> | Not equal to | Decade <> Century |
| Concatenation | ||
| & | Combine two string values | FullName = First & Last |
| + | Addition of two numbers | Total = Price + Tax |
| Logical (comparison of conditions) | ||
| AND | Both conditions are True. | Value = 14 - therefore: Value < 25 AND Value > 10 |
| EQV | Both conditions evaluate to the same result, either True or False. | (Year = Day * 7) EQV (Hour = Minute * 12) (Both are False so the operator returns True) |
| NOT | Condition does not evaluate to specified result. | IF NOT(24 > 100) (This would evaluate to True since 24 is not greater than 100.) |
| OR | Either of two conditions is True | IF Price > 10 OR Price < 5 |
| XOR | One, but not both, of the conditions is True. | IF Year = 2010 XOR Month = 2 (Will return False in Feburary 2010 as both conditions would be True. Returns True during the rest of 2010 or during February of any other year.) |
