Programming Excel with VBA and .NET

Do Math

Visual Basic provides built-in operators and functions that perform many of the same calculations that you are used to using from Excel formulas. If you are new to programming, the way you write mathematical formulas in Visual Basic may seem backward:

    x = 43 + 37 / 2 ' Not 43 + 37 / 2 = x

That’s because the equals sign (=) performs an operation called assignment . The result of the preceding calculation is assigned to the variable x. In Visual Basic, the assignment operation is always performed last, after all other operations. Other operators are evaluated in the sequence shown in Table 3-4.

Table 3-4. Visual Basic mathematical operators’ order of precedence (left to right)

( ) group	^ exponent	- negation	* multiply	/ divide
\ integer divide	Mod modulus	+ add	− subtract	= assign

Most of these operators are self-explanatory, but there are two exceptions:

• Use \ to divide two numbers and ignore the remainder.

• Use Mod to divide two numbers and return only the remainder.

For example, the following simple function divides two numbers and returns the result as a string:

    Function IntegerMath(numerator As Integer, denominator As Integer) As String
        Dim quotient As Integer, remainder As Integer
        ' Find the quotient.
        quotient = numerator \ denominator
        ' Find the remainder
        remainder = numerator Mod denominator
        ' Return the result
        IntegerMath = "Result is " & quotient & " remainder " & denominator
    End Function

Mod is frequently used in loops to perform ...