Numbers

When you think of numbers, you need to put them into two classifications:
Numbers that do not have a decimal point (whole numbers, integers)
Numbers that have a decimal point (real numbers, floating point)


Integers (No Decimal Point)

If a variable needs to contain a whole number you can use the Integer or Long data type.

Dim myByte As Byte           'very small: 0 to 255  
Dim myInteger As Integer 'small integers: -32,768 to 32,767
Dim myLong As Long 'large integers: -2.1 E+9 to +2.1 E+9

Floating Point Numbers

If a variable needs to contain a number that has a decimal point you can use the Single or Double data type.
These two data types have Floating Point precision.
The term floating point refers to the fact that the decimal point can "float/move" to the left or right, between the significant digits of the number.

Dim mySingle As Single     '7 significant figures  
Dim myDouble As Double '15 significant figures

The Single data type uses Single Precision Floating Point.
The Double data type uses Double Precision Floating Point.
The Single and Double data types allow us to specify extremely small and large numbers.
Depending on what the numbers represent, you can encounter rounding errors when performing operations with floating-point numbers.
If your calculations need to be very accurate then you might want to use a Scaled Integer / Fixed Point data type.


Currency and Decimal Data Types

If you need a high level of accuracy you can use the Currency or Variant/Decimal data type.
The variant has a special sub type called Variant/Decimal that can be used to hold very large numbers.

Dim myCurrency As Currency    '15 digits on the left, 4 digits on the right  
Dim myVariant As Variant '29 significant figures
myVariant = CDec(123.4562)

Application.WorksheetFunction.Pi

Only available in Excel

Application.WorksheetFunction.Pi() 
Application.Pi

Floating-Point Imprecision

When you work with floating-point numbers, remember that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations, such as value comparison and the Mod operator.
link - learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/troubleshooting-data-types


Since all floating-point division returns a floating-point value, you can't ever be certain that your result is accurate to every decimal place, even if you're performing division on Decimal or Currency values. There will always be an inherent possibility of rounding errors, although they're likely to be small.
If you're dividing integers, or if you don't care about the decimal portion of the result, you can use the integer division operator (\).
Integer division is faster than floating-point division, and the result is always an Integer or Long value, either of which requires less memory than a Double value.


Fixed Point Numbers

Also known as Scaled Integer data types
Examples are: Currency, Decimal


These can provide a high level of accuracy.
They are not as precise as the floating-point data types - that is, they can't represent numbers as large or as small.
This method has a fixed decimal point position and therefore always has a fixed number of digits before and after the decimal point.
If you can't afford rounding errors, and you don't require as many decimal places as the floating-point data types provide, you can use the scaled integer data types.


Currency
The Currency data type uses 8 bytes of memory and can represent numbers with fifteen digits to the left of the decimal point and four to the right, in the range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807.



Variant/Decimal
The Decimal data type uses 12 bytes of memory and can have between 0 and 28 decimal places. The Decimal data type is a Variant subtype; in order to use the Decimal data type, you must declare a variable of type Variant, and then convert it by using the CDec function.
The following example shows how to convert a Variant variable to a Decimal variable. It also demonstrates how using the Decimal data type can minimize the rounding errors inherent in the floating-point data types.


Sub DoubleVsDecimal() 
' This procedure demonstrates how using the
' Decimal data type can minimize rounding errors.

Dim dblNum As Double
Dim varNum As Variant
Dim lngCount As Long

' Increment values in loop.
For lngCount = 1 To 100000
dblNum = dblNum + 0.00001
' Convert value to Decimal using CDec.
varNum = varNum + CDec(0.00001)
Next

Debug.Print "Result using Double: " & dblNum
Debug.Print "Result using Decimal: " & varNum
End Sub

Any time you use the floating-point division operator (/), you're performing floating-point division, and your return value will be of type Double.
This is true whether your dividend and divisor are integer, floating-point, or fixed-point values. It's true whether or not your result has a decimal portion.
For example, running the following code from the Immediate window prints "Double":


Since all floating-point division returns a floating-point value, you can't ever be certain that your result is accurate to every decimal place, even if you're performing division on Decimal or Currency values.
There will always be an inherent possibility of rounding errors, although they're likely to be small.
If you're dividing integers, or if you don't care about the decimal portion of the result, you can use the integer division operator (\). Integer division is faster than floating-point division, and the result is always an Integer or Long value, either of which requires less memory than a Double value. For example, running this code from the Immediate window prints "Integer":


Floating Point Numbers

Examples are: Single, Double and Date


These are numbers that can be expressed as mm E ee, where mm is the mantissa and ee is the exponent (power of 10).
You can use floating point to save the value of number.
This method is called floating point because the the decimal point floats.
This method represents a number in an approximate way to a certain number of significant digits.
This is then scaled using an exponent.
The Single and Double data types are very precise, that is, they make it possible for you to specify extremely small or large numbers. However, these data types are not very accurate because they use floating-point mathematics.


Here multiplication and division are easy. In you just multiply/divide the floating parts and then add/subtract powers (In Division you have an extra step which is making sure that the floating part does not start with a zero).
In order to add or subtract you need to make sure that both numbers have the same power, so you have to shift the smaller number. Then you will do the addition/subtraction in the normal way and write the result in the scientific notation.


Single
The Single data type requires 4 bytes of memory and can store negative values between -3.402823 x 1038 and -1.401298 x 10-45 and positive values between 1.401298 x 10-45 and 3.402823 x 1038. The Double data type requires 8 bytes of memory and can store negative values between -1.79769313486232 x 10308 and -4.94065645841247 x 10-324 and positive values between 4.94065645841247 x 10-324 and 1.79769313486232 x 10308.


Double
Floating-point mathematics has an inherent limitation in that it uses binary digits to represent decimals. Not all the numbers within the range available to the Single or Double data type can be represented exactly in binary form, so they are rounded. Also, some numbers cannot be represented exactly with any finite number of digits, pi, for example, or the decimal resulting from 1/3.


Calculations

Using the "*" and "+" operators can return Overflow Run-Time Error.

link - stackoverflow.com/questions/17315650/overflow-when-multiplying-integers-and-assigning-to-long 

Dim myLong As Long 
Debug.Print TypeName(24) '= Integer
Debug.Print TypeName(60) '= Integer
Debug.Print TypeName(60) '= Integer
myLong = 24 * 60 * 60 ' Overflow

The literals 24, 60, and 60 are all of type Integer so by default, the * operator returns an Integer, which overflows because the result is greater than 32,767.


Debug.Print TypeName(32767)     '= Integer  
Debug.Print TypeName(1) '= Integer
myLong = 32767 + 1 ' Overflow.

The literals 32767 and 1 are all of type Integer so by default, so the + operator returns an Integer, which overflows because the result is greater than 32,767.


Debug.Print TypeName(32768)     '= Long  
Debug.Print TypeName(1) '= Integer
myLong = 32768 + 1 ' Works fine!

The literal 32768 is a Long by default, so the * operator returns a Long, so there is no overflow.


© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopNext