Round Function

Returns a number rounded to a specified number of decimal places.

Syntax

Round(expression, [numdecimalplaces])

Parameters

• expression - Required. Numeric expression being rounded.
• numdecimalplaces - Optional. Number indicating how many places to the right of the decimal are included in the rounding. If omitted, integers are returned.

Return Value

Returns a value of the same type as expression that has been rounded to the specified number of decimal places.

Remarks

The Round function rounds numbers using "banker's rounding" (round to even), also known as "round half to even". This differs from the typical "round half up" method taught in schools.

Important Notes: - Uses banker's rounding (round to nearest even number when exactly halfway) - If numdecimalplaces is omitted, returns an integer - If numdecimalplaces is 0, rounds to nearest integer - If numdecimalplaces is positive, rounds to that many decimal places - If numdecimalplaces is negative, rounds to the left of decimal point - Returns same data type as input expression

Banker's Rounding Examples: - Round(2.5) = 2 (rounds to even) - Round(3.5) = 4 (rounds to even) - Round(4.5) = 4 (rounds to even) - Round(5.5) = 6 (rounds to even)

Rounding Behavior by numdecimalplaces:

Typical Uses

1. Financial Calculations: Round currency values to 2 decimal places
2. Display Formatting: Round numbers for user display
3. Statistical Analysis: Round computed values to significant digits
4. Data Normalization: Standardize precision across datasets
5. Measurement Values: Round sensor readings to appropriate precision
6. Grade Calculation: Round student scores to whole numbers
7. Percentage Display: Round percentages to desired precision
8. Scientific Notation: Round to significant figures

Basic Examples

Example 1: Round to Integer

Dim value As Double
Dim rounded As Integer

value = 3.7
rounded = Round(value)  ' Returns 4

Example 2: Round Currency

Dim price As Double
Dim roundedPrice As Double

price = 12.3456
roundedPrice = Round(price, 2)  ' Returns 12.35

Example 3: Banker's Rounding

' Demonstrates round-to-even behavior
Dim result1 As Integer
Dim result2 As Integer

result1 = Round(2.5)  ' Returns 2 (rounds to even)
result2 = Round(3.5)  ' Returns 4 (rounds to even)

Example 4: Round to Tens

Dim value As Long
Dim roundedToTens As Long

value = 2748
roundedToTens = Round(value, -1)  ' Returns 2750

Common Patterns

Pattern 1: RoundCurrency

Function RoundCurrency(amount As Double) As Double
' Round to 2 decimal places for currency
RoundCurrency = Round(amount, 2)
End Function

Pattern 2: RoundPercentage

Function RoundPercentage(percentage As Double, _
Optional decimals As Integer = 1) As Double
' Round percentage to specified decimal places
RoundPercentage = Round(percentage, decimals)
End Function

Pattern 3: RoundToSignificantFigures

Function RoundToSignificantFigures(value As Double, _
sigFigs As Integer) As Double
' Round to specified number of significant figures
Dim magnitude As Double
Dim factor As Double

If value = 0 Then
RoundToSignificantFigures = 0
Exit Function
End If

magnitude = Int(Log(Abs(value)) / Log(10))
factor = 10 ^ (sigFigs - magnitude - 1)

RoundToSignificantFigures = Round(value * factor) / factor
End Function

Pattern 4: RoundUpAlways

Function RoundUpAlways(value As Double, decimals As Integer) As Double
' Always round up (ceiling behavior)
Dim factor As Double

factor = 10 ^ decimals

If value > 0 Then
RoundUpAlways = Int(value * factor + 0.9999999) / factor
Else
RoundUpAlways = Int(value * factor) / factor
End If
End Function

Pattern 5: RoundDownAlways

Function RoundDownAlways(value As Double, decimals As Integer) As Double
' Always round down (floor behavior)
Dim factor As Double

factor = 10 ^ decimals

If value > 0 Then
RoundDownAlways = Int(value * factor) / factor
Else
RoundDownAlways = Int(value * factor - 0.9999999) / factor
End If
End Function

Pattern 6: RoundToNearest

Function RoundToNearest(value As Double, nearest As Double) As Double
' Round to nearest multiple of a number
' e.g., RoundToNearest(47, 5) = 45
RoundToNearest = Round(value / nearest) * nearest
End Function

Pattern 7: RoundArray

Sub RoundArray(arr() As Double, decimals As Integer)
' Round all elements in an array
Dim i As Integer

For i = LBound(arr) To UBound(arr)
arr(i) = Round(arr(i), decimals)
Next i
End Sub

Pattern 8: RoundIfNeeded

Function RoundIfNeeded(value As Double, decimals As Integer, _
threshold As Double) As Double
' Only round if difference from rounded value exceeds threshold
Dim rounded As Double

rounded = Round(value, decimals)

If Abs(value - rounded) > threshold Then
RoundIfNeeded = rounded
Else
RoundIfNeeded = value
End If
End Function

Pattern 9: RoundForDisplay

Function RoundForDisplay(value As Double) As String
' Round and format for display based on magnitude
If Abs(value) < 0.01 Then
RoundForDisplay = Format(Round(value, 4), "0.0000")
ElseIf Abs(value) < 1 Then
RoundForDisplay = Format(Round(value, 3), "0.000")
ElseIf Abs(value) < 100 Then
RoundForDisplay = Format(Round(value, 2), "0.00")
Else
RoundForDisplay = Format(Round(value, 0), "0")
End If
End Function

Pattern 10: SymmetricRound

Function SymmetricRound(value As Double, decimals As Integer) As Double
' Traditional "round half up" instead of banker's rounding
Dim factor As Double
Dim shifted As Double

factor = 10 ^ decimals
shifted = value * factor

If shifted >= 0 Then
SymmetricRound = Int(shifted + 0.5) / factor
Else
SymmetricRound = Int(shifted - 0.5) / factor
End If
End Function

Advanced Usage

Example 1: Financial Calculator

' Precise financial calculations with proper rounding
Class FinancialCalculator
Private m_precision As Integer

Public Sub Initialize(Optional precision As Integer = 2)
m_precision = precision
End Sub

Public Function CalculateInterest(principal As Double, _
rate As Double, _
periods As Integer) As Double
' Calculate simple interest with rounding
Dim interest As Double

interest = principal * rate * periods
CalculateInterest = Round(interest, m_precision)
End Function

Public Function CalculatePayment(loanAmount As Double, _
interestRate As Double, _
numPayments As Integer) As Double
' Calculate loan payment with rounding
Dim monthlyRate As Double
Dim payment As Double

monthlyRate = interestRate / 12

If monthlyRate = 0 Then
payment = loanAmount / numPayments
Else
payment = loanAmount * (monthlyRate * (1 + monthlyRate) ^ numPayments) / _
((1 + monthlyRate) ^ numPayments - 1)
End If

CalculatePayment = Round(payment, m_precision)
End Function

Public Function CalculateTax(amount As Double, taxRate As Double) As Double
' Calculate tax with rounding
Dim tax As Double

tax = amount * taxRate
CalculateTax = Round(tax, m_precision)
End Function

Public Function CalculateTotal(subtotal As Double, taxRate As Double) As Double
' Calculate total with tax
Dim tax As Double
Dim total As Double

tax = Round(subtotal * taxRate, m_precision)
total = subtotal + tax

CalculateTotal = Round(total, m_precision)
End Function

Public Function SplitAmount(totalAmount As Double, _
numSplits As Integer) As Double()
' Split amount evenly with proper rounding
Dim splits() As Double
Dim baseAmount As Double
Dim remainder As Double
Dim i As Integer

ReDim splits(1 To numSplits)

baseAmount = Round(totalAmount / numSplits, m_precision)

For i = 1 To numSplits
splits(i) = baseAmount
Next i

' Adjust for rounding errors
remainder = Round(totalAmount - (baseAmount * numSplits), m_precision)
splits(1) = Round(splits(1) + remainder, m_precision)

SplitAmount = splits
End Function

Public Sub SetPrecision(precision As Integer)
m_precision = precision
End Sub

Public Function GetPrecision() As Integer
GetPrecision = m_precision
End Function
End Class

Example 2: Statistical Rounder

' Round statistical values to appropriate precision
Module StatisticalRounder
Public Function RoundMean(values() As Double, decimals As Integer) As Double
' Calculate and round mean
Dim sum As Double
Dim i As Integer

sum = 0
For i = LBound(values) To UBound(values)
sum = sum + values(i)
Next i

RoundMean = Round(sum / (UBound(values) - LBound(values) + 1), decimals)
End Function

Public Function RoundStdDev(values() As Double, decimals As Integer) As Double
' Calculate and round standard deviation
Dim mean As Double
Dim sumSquaredDiff As Double
Dim i As Integer
Dim n As Integer

n = UBound(values) - LBound(values) + 1
mean = RoundMean(values, decimals + 2)

sumSquaredDiff = 0
For i = LBound(values) To UBound(values)
sumSquaredDiff = sumSquaredDiff + (values(i) - mean) ^ 2
Next i

RoundStdDev = Round(Sqr(sumSquaredDiff / (n - 1)), decimals)
End Function

Public Function RoundPercentile(values() As Double, percentile As Double, _
decimals As Integer) As Double
' Calculate and round percentile
Dim sortedValues() As Double
Dim index As Double
Dim lowerIndex As Integer
Dim upperIndex As Integer
Dim weight As Double
Dim result As Double

' Copy and sort array (simplified - would need sorting implementation)
sortedValues = values

index = percentile * (UBound(sortedValues) - LBound(sortedValues))
lowerIndex = Int(index) + LBound(sortedValues)
upperIndex = lowerIndex + 1
weight = index - Int(index)

If upperIndex > UBound(sortedValues) Then
result = sortedValues(lowerIndex)
Else
result = sortedValues(lowerIndex) * (1 - weight) + _
sortedValues(upperIndex) * weight
End If

RoundPercentile = Round(result, decimals)
End Function

Public Function RoundCorrelation(values1() As Double, values2() As Double, _
decimals As Integer) As Double
' Calculate and round correlation coefficient
Dim mean1 As Double, mean2 As Double
Dim sum As Double
Dim sum1Sq As Double, sum2Sq As Double
Dim i As Integer
Dim correlation As Double

mean1 = RoundMean(values1, decimals + 2)
mean2 = RoundMean(values2, decimals + 2)

sum = 0
sum1Sq = 0
sum2Sq = 0

For i = LBound(values1) To UBound(values1)
sum = sum + (values1(i) - mean1) * (values2(i) - mean2)
sum1Sq = sum1Sq + (values1(i) - mean1) ^ 2
sum2Sq = sum2Sq + (values2(i) - mean2) ^ 2
Next i

correlation = sum / Sqr(sum1Sq * sum2Sq)
RoundCorrelation = Round(correlation, decimals)
End Function
End Module

Example 3: Grade Calculator

' Calculate and round student grades
Class GradeCalculator
Private m_roundingMode As String  ' "banker", "up", "down", "nearest"

Public Sub Initialize(Optional roundingMode As String = "banker")
m_roundingMode = roundingMode
End Sub

Public Function CalculateFinalGrade(scores() As Double, _
weights() As Double) As Double
' Calculate weighted average grade
Dim weightedSum As Double
Dim totalWeight As Double
Dim i As Integer

weightedSum = 0
totalWeight = 0

For i = LBound(scores) To UBound(scores)
weightedSum = weightedSum + scores(i) * weights(i)
totalWeight = totalWeight + weights(i)
Next i

CalculateFinalGrade = RoundGrade(weightedSum / totalWeight)
End Function

Private Function RoundGrade(grade As Double) As Double
' Round grade based on configured mode
Select Case m_roundingMode
Case "banker"
RoundGrade = Round(grade, 1)

Case "up"
If grade > Int(grade) Then
RoundGrade = Int(grade) + 1
Else
RoundGrade = Int(grade)
End If

Case "down"
RoundGrade = Int(grade)

Case "nearest"
If grade - Int(grade) >= 0.5 Then
RoundGrade = Int(grade) + 1
Else
RoundGrade = Int(grade)
End If

Case Else
RoundGrade = Round(grade, 1)
End Select
End Function

Public Function GetLetterGrade(numericGrade As Double) As String
' Convert numeric grade to letter grade
Dim rounded As Double

rounded = RoundGrade(numericGrade)

If rounded >= 90 Then
GetLetterGrade = "A"
ElseIf rounded >= 80 Then
GetLetterGrade = "B"
ElseIf rounded >= 70 Then
GetLetterGrade = "C"
ElseIf rounded >= 60 Then
GetLetterGrade = "D"
Else
GetLetterGrade = "F"
End If
End Function

Public Sub SetRoundingMode(mode As String)
m_roundingMode = mode
End Sub

Public Function GetRoundingMode() As String
GetRoundingMode = m_roundingMode
End Function
End Class

Example 4: Measurement Precision Manager

' Manage precision for different types of measurements
Class MeasurementPrecisionManager
Private Type MeasurementType
Name As String
Decimals As Integer
Unit As String
End Type

Private m_types() As MeasurementType
Private m_count As Integer

Public Sub Initialize()
m_count = 0
ReDim m_types(0 To 99)

' Add default measurement types
AddMeasurementType "Temperature", 1, "°C"
AddMeasurementType "Distance", 2, "m"
AddMeasurementType "Weight", 3, "kg"
AddMeasurementType "Pressure", 1, "kPa"
AddMeasurementType "Voltage", 2, "V"
End Sub

Public Sub AddMeasurementType(name As String, decimals As Integer, _
unit As String)
If m_count > UBound(m_types) Then
ReDim Preserve m_types(0 To UBound(m_types) + 50)
End If

m_types(m_count).Name = name
m_types(m_count).Decimals = decimals
m_types(m_count).Unit = unit
m_count = m_count + 1
End Sub

Public Function RoundMeasurement(value As Double, _
measurementType As String) As Double
' Round value based on measurement type
Dim i As Integer

For i = 0 To m_count - 1
If UCase(m_types(i).Name) = UCase(measurementType) Then
RoundMeasurement = Round(value, m_types(i).Decimals)
Exit Function
End If
Next i

' Default to 2 decimals if type not found
RoundMeasurement = Round(value, 2)
End Function

Public Function FormatMeasurement(value As Double, _
measurementType As String) As String
' Round and format measurement with unit
Dim rounded As Double
Dim i As Integer
Dim decimals As Integer
Dim unit As String

decimals = 2
unit = ""

For i = 0 To m_count - 1
If UCase(m_types(i).Name) = UCase(measurementType) Then
decimals = m_types(i).Decimals
unit = m_types(i).Unit
Exit For
End If
Next i

rounded = Round(value, decimals)
FormatMeasurement = Format(rounded, "0." & String(decimals, "0")) & " " & unit
End Function

Public Function GetPrecision(measurementType As String) As Integer
Dim i As Integer

For i = 0 To m_count - 1
If UCase(m_types(i).Name) = UCase(measurementType) Then
GetPrecision = m_types(i).Decimals
Exit Function
End If
Next i

GetPrecision = 2  ' Default
End Function
End Class

Error Handling

The Round function generates errors in specific situations:

Error 5: Invalid procedure call or argument - Occurs when expression cannot be converted to numeric type - Occurs when numdecimalplaces is excessively large

Error 6: Overflow - Occurs when the rounded value exceeds the range of the return type

Example error handling:

On Error Resume Next
Dim result As Double
result = Round(userInput, decimalPlaces)
If Err.Number <> 0 Then
MsgBox "Error rounding value: " & Err.Description
result = 0
End If
On Error GoTo 0

Performance Considerations

• Round is very fast for reasonable decimal place values
• Performance degrades with very large numdecimalplaces values
• Consider caching rounded values if used repeatedly with same parameters
• For large arrays, consider rounding in batches
• Banker's rounding is slightly slower than simple truncation

Best Practices

1. Understand Banker's Rounding: Be aware of round-to-even behavior for .5 values
2. Document Rounding: Clearly document which rounding method is used
3. Consistent Precision: Use same decimal places throughout calculations
4. Round at Display Time: Keep full precision during calculations, round for display
5. Avoid Cumulative Errors: Be aware of rounding errors accumulating in loops
6. Test Edge Cases: Test with .5 values to verify rounding behavior
7. Financial Calculations: Use 2 decimals for currency, consider legal requirements
8. Validate Input: Check that decimal places parameter is reasonable
9. Use Helper Functions: Wrap Round in domain-specific functions
10. Consider Alternatives: For traditional rounding, implement custom function

Comparison with Related Functions

Platform and Version Notes

• Available in VB6 and VBA (added in VB6/Office 2000)
• Uses banker's rounding (IEEE 754 standard)
• Not available in earlier VB versions (use Int or Fix instead)
• In VB.NET, replaced by Math.Round with MidpointRounding enum
• Behavior differs from Excel's ROUND function (which uses "round half up")

Limitations

• Banker's rounding may be unexpected for users familiar with traditional rounding
• Cannot choose rounding mode (always banker's rounding)
• Limited precision for very large decimal place values
• Rounding errors can accumulate in iterative calculations
• Different from Excel ROUND function behavior
• No built-in round-up or round-down functions

Related Functions

• Int: Returns the integer portion of a number (truncates toward zero)
• Fix: Returns the integer portion of a number (truncates toward zero)
• CInt: Converts expression to Integer with rounding
• CLng: Converts expression to Long with rounding
• Format: Formats number with specified decimal places