"The code is exactly
how I would like to write code and the algorithms used are very
efficient and welldocumented."
Van T. Dinh, Microsoft MVP


Module: MathGeneral in Category General VBA/VB6 : Math from Total Visual SourceBook
General math routines for VBA and VB6 such as rounding, factorials, and determining the minimum and maximum values from a set of numbers.
The code in this module also addresses rounding errors that exist in VBA/VB6 functions.
VBA/VB6 has builtin functions for various mathematical operations. Search online help for "Math Functions" and "Derived Math Functions" for more information.
Procedure List
Procedure Name

Type

Description

(Declarations)

Declarations

Declarations and private variables for the modMathGeneral module

GetGCF

Procedure

Get the greatest common factor (largest number that evenly divides into two numbers).

GetRandomInt

Procedure

Get a random integer value between the range specified in the arguments, inclusive.
The builtin Rnd function generates a value between 0 and 1. This function scales the value to return an integer between the range of numbers specified in the arguments.
The range is inclusive, so the lowrange and highrange values are as likely to be chosen as any of the other values between them.

IsNumberPrime

Procedure

Determine if a number is a prime number.

MaxOfThree

Procedure

Get the greater of three values. Null values are ignored; if all three values are Null, Null is returned.

MaxOfTwo

Procedure

Get the greater of two values. Null values are ignored; if both values are Null, Null is returned.

MinOfThree

Procedure

Get the smallest of three values. Null values are ignored; if all three values are Null, Null is returned.

MinOfTwo

Procedure

Get the lesser of two values. Null values are ignored; if both values are Null, Null is returned.

PrimeFactors

Procedure

Calculate the prime factors of a number.

RoundDown

Procedure

Round a number to the specified number of decimal places with 0.5 rounded down.
Numbers can be rounded to units on either side of the decimal point. Borderline numbers (0.5) are rounded down. Use RoundUp() to round borderline numbers up.

RoundUp

Procedure

Round a number to the specified number of decimal places (0.5 is rounded up).
The VBA/VB6 Round function performs Bankers Rounding which rounds 0.5 up or down depending on whether the previous digit is even or odd.
This RoundUp function always rounds 0.5 up to the next highest value. RoundUp also allows you to round to hundreds, thousands, or decimals.

ScalePercent

Procedure

Scale a value to a range between 0 and 100.
This procedure compares one value to another, and returns the percentage that the first number is of the second number, scaled to a value between 0 and 100.
This procedure is useful when you want to use a Progress Meter, ScrollBar, or another control with Min and Max properties.
Instead of changing the properties of the control to match your input values, leave the Min and Max properties at 0 and 100, then scale the value to a number between the Min and Max value of the control.
The number could be less than 0 or greater than 100 if the 'dblCurrent' argument is less than 0 or greater than the 'dblMax' value.

Subtract

Procedure

Subtract two numbers with decimals correctly.
In certain situations, VBA/VB6 does not subtract numbers with decimal places correctly (e.g. 100.8  100.7 = 0.099999999999943)
This procedure corrects the problem by rounding the numbers correctly since a subtraction result cannot have more decimal places than the maximum of the inputs.

Example Code for Using Module: MathGeneral
' Example of modMathGeneral
'
' To use this example, create a new module and paste this code into it.
' Then run the procedures by putting the cursor in the procedure and pressing:
' F5 to run it, or
' F8 to step through it linebyline (see the Debug menu for more options)
Private Sub Example_modMathGeneral()
' Comments: Examples of using the modMathGeneral module in VBA and VB6.
' Show min and max values
Debug.Print "The max of 1, 2, 3 is: " & MaxOfThree(1, 2, 3)
Debug.Print "The max of 1, 2 is : " & MaxOfTwo(1, 2)
Debug.Print "The min of 1, 2, 3 is: " & MinOfThree(1, 2, 3)
Debug.Print "The min of 1, 2 is : " & MinOfTwo(1, 2)
Example_modMathGeneral_Subtract
Example_modMathGeneral_Rounding
Example_modMathGeneral_PrimeFactors
Example_modMathGeneral_ScalePercent
End Sub
Private Sub Example_modMathGeneral_Subtract()
' Comments: Examples of using the modMathGeneral module related to Subtraction in VBA and VB6.
' Correctly subtract two numbers to avoid the problems associated with the standard VB6/VBA subtraction of numbers with decimals
' Example using VBA gives 9.99999999999943E02
Debug.Print "Inaccurate Subtraction of 100.8  100.7:", 100.8  100.7
' The correct answer is .1
Debug.Print "Correct answer:", Subtract(100.8, 100.7)
' Notice how this statement which looks true is not:
If 100.5  100.4 = 0.1 Then
Debug.Print "VBA math is correct"
Else
Debug.Print "VBA math isn't always correct"
End If
' But this fixes it:
If Subtract(100.5, 100.4) = 0.1 Then
Debug.Print "Function math is correct"
Else
Debug.Print "Function math is wrong"
End If
Debug.Print
End Sub
Private Sub Example_modMathGeneral_Rounding()
' Comments: Examples of using the modMathGeneral module related to Rounding in VBA and VB6.
Dim dblValue As Double
Dim intDigits As Integer
' Rounding examples:
' The builtin Round function does "Bankers' rounding", or "Round half to even." This method rounds 5 to the closest even number (e.g. 1.5 rounds to 2, 2.5 rounds to 2, 3.5 rounds to 4, etc.)
' For example, these both return 100.46:
Debug.Print "The Round function uses Bankers Rounding to round .5 up if the digit before it is odd and down if it's even"
Debug.Print "Bankers Rounding for 100.455", Round(100.455, 2)
Debug.Print "Bankers Rounding for 100.465", Round(100.465, 2)
Debug.Print
' modMathGeneral includes functions to force ..5 to always round up, or to always round down.
' Numbers can be rounded to units on either side of the decimal point.
' Pass a positive number to round to the specified # of places to the right of the decimal:
Debug.Print "Compare value using: Round, RoundDown, and RoundUp"
Debug.Print 100.455, Round(100.455, 2), RoundDown(100.455, 2), RoundUp(100.455, 2)
Debug.Print 100.465, Round(100.465, 2), RoundDown(100.465, 2), RoundUp(100.465, 2)
Debug.Print 100.455, Round(100.455, 2), RoundDown(100.455, 2), RoundUp(100.455, 2)
Debug.Print 100.465, Round(100.465, 2), RoundDown(100.465, 2), RoundUp(100.465, 2)
Debug.Print
' Pass a negative number to round to the specified # of places to the left of the decimal (e.g. round to tens, hundreds, thousands):
dblValue = 1150
intDigits = 2
Debug.Print "Round Up and Down 2 digits (hundreds)"
Debug.Print dblValue, RoundDown(dblValue, intDigits), RoundUp(dblValue, intDigits)
intDigits = 3
Debug.Print "Round Up and Down 3 digits (thousands)"
dblValue = 1500
Debug.Print dblValue, RoundDown(dblValue, intDigits), RoundUp(dblValue, intDigits)
dblValue = 1500.1
Debug.Print dblValue, RoundDown(dblValue, intDigits), RoundUp(dblValue, intDigits)
Debug.Print
End Sub
Private Sub Example_modMathGeneral_PrimeFactors()
' Comments: Examples of using the modMathGeneral module related to prime factors in VBA and VB6.
Const cintFactorTest As Integer = 990
Dim x As Integer
Dim strText As String
Dim lngCounter As Long
Dim alngTmp() As Long
' Displays to the immediate window the list of prime values from between 0 and 100.
strText = ""
For x = 0 To 100
If IsNumberPrime(x) Then
strText = strText & x & " "
End If
Next x
Debug.Print "Prime numbers between 0 and 100:"
Debug.Print strText
Debug.Print
' Example for PrimeFactors
strText = ""
Call PrimeFactors(cintFactorTest, alngTmp())
For lngCounter = LBound(alngTmp) To UBound(alngTmp)
strText = strText & alngTmp(lngCounter) & " "
Next lngCounter
Debug.Print "Prime factors for " & cintFactorTest & ": "
Debug.Print strText
Debug.Print
End Sub
Private Sub Example_modMathGeneral_ScalePercent()
' Comments: Examples of using the modMathGeneral module related to ScalePercent in VBA and VB6.
Dim lngCounter As Long
' Example for ScalePercent
Debug.Print "Percent Distance using ScalePercent"
For lngCounter = 0 To 5280 Step 100
Debug.Print lngCounter & " is: " & ScalePercent(CDbl(lngCounter), 5280)
Next lngCounter
End Sub
Overview of Total Visual SourceBook
The source code in Total Visual
SourceBook includes modules and classes for Microsoft Access, Visual
Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily
add this professionally written, tested, and documented royaltyfree code
into your applications to simplify your application development efforts.
Additional Resources

