Table of Contents
The floor function in VBA is a mathematical function that rounds a given number down to the nearest integer. This can be useful when working with numeric data that needs to be converted into whole numbers. To use the floor function in VBA, the syntax is “Application.WorksheetFunction.Floor(number, significance)”. The “number” parameter represents the value to be rounded down, and the “significance” parameter represents the multiple to which the number should be rounded. For example, if the number is 3.75 and the significance is 0.5, the result would be 3.5. Some examples of using the floor function in VBA include converting decimal values to whole numbers for data analysis, calculating floor prices in financial models, and rounding down time values to the nearest hour for scheduling purposes.
Use Floor Function in VBA (With Examples)
You can use the Floor method in VBA to round the number in a cell down to the nearest multiple of significance.
Here is one common way to use this function in practice:
Sub ToFloor()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = WorksheetFunction.Floor(Range("A" & i), Range("B" & i))
Next i
End Sub
This particular macro will round each value in the range A2:A10 to the nearest multiple of significance specified in the range B2:B10 and display the results in the range C2:C10.
The following example shows how to use this syntax in practice.
Example: How to Use Floor Function in VBA
Suppose we have a list of values in column A and another list of multiples of significance in column B that we’d like to round down each value to:
We can create the following macro with the Floor method in VBA to round down each value in column A to the multiple of significance in column B:
Sub ToFloor()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = WorksheetFunction.Floor(Range("A" & i), Range("B" & i))
Next i
End Sub
When we run this macro, we receive the following output:
Column C shows the result of using the Floor method on each value in Column A.
For example, we can see:
- 12.2452 rounded down to the nearest multiple of .001 is 12.245.
- 14.927 rounded down to the nearest multiple of .01 is 14.92.
- -5.23 rounded down to the nearest multiple of .1 is -5.3.
And so on.
Note: You can find the complete documentation for the VBA Floor method .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: