O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required