This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Mathematical Functions
VBA provides a rich, broad set of functions to perform mathematical and financial calculations. There are too many to cover in this section, so we provide an overview of the most commonly used functions.
The Abs Function
The Abs function returns the absolute value of a number, removing the sign. The following is the syntax for the Abs function, where number is any expression that evaluates to a numerical value:
Abs(number)
For example; this expression returns a 7:
Abs(-7)
The Int Function
The Int function removes any decimal value from a number, returning the integer portion. The function uses the following syntax, where number is any expression that evaluates to a numerical value:
Int(number)
For example; this expression returns 15 because it truncates the value, removing the decimal portion:
Int(15.9)
However, if the numerical value is negative, Int returns the nearest negative integer, so the following returns –16:
Int(-15.9)
Although seemingly the same, Int and Cint can't be used interchangeably. The Int function doesn't convert the data type of the argument. Using CInt is often the better option, but it doesn't always return the same result. So be careful in determining which one to use.
The Rnd Function
The Rnd function is used to generate a random number. It can be used with an optional argument represented as any valid numerical expression. The following is the syntax for the function:
Rnd(seed)
seed can be used to control the generated number as indicated in the following:
- If seed is a negative value, Rnd generates the same number.
- If seed is a positive number (other than 0) Rnd generates the next number in an internally determined sequence of numbers.
- If seed equals 0, Rnd generates the most recently generated number.
- If seed is omitted, Rnd generates the next number in an internally determined sequence of numbers.
The Rnd function generates a number in the range of 0 to 1, so if you need a whole number, you will have to multiply the generated value by a power of 10 and use the Int function to get your whole number.
A Mathematical Functions Example
To illustrate mathematical functions, let's create a function to generate a number between 1 and 99.
- Create a blank form and put two text boxes on it. Label the boxes txtSeed and txtPicks.
- Add a button to the form (turn off the wizard first). Name the button cmdGenerate.
- Set Record Selectors and Navigation buttons to No. Set Scroll Bars to neither.
- In the On Click event of the button use the following code:
Private Sub cmdGenerate_Click() 'Generate number between 1 and 99 Me.txtPicks = Int(Rnd(Me.txtSeed) * 100) End Sub - Save form as frmGenerate (see Figure 4.3).
Figure 4.3 The completed frmGenerate showing an example of a generated number.
)