Explanation of RoundSF Function in VBA

The provided code defines a VBA (Visual Basic for Applications) function named RoundSF, which rounds a given number to a specified number of significant figures. Below is a breakdown of its structure and functionality.

Function Declaration

Function RoundSF(num As Variant, sigs As Variant) As String

• Function Name: RoundSF
• Parameters:
  • num: The number to be rounded, of type Variant, allowing it to accept any data type.
  • sigs: The number of significant figures to round to, also of type Variant.
• Return Type: Returns a String, which can also represent an error through the CVErr function.

Variable Declaration

Dim exponent As Integer
Dim decplace As Integer
Dim fmt_left As String
Dim fmt_right As String
Dim numround As Double

• exponent: Holds the base-10 exponent for scientific notation.
• decplace: Determines the number of decimal places needed after rounding.
• fmt_left & fmt_right: Strings to format the final output.
• numround: Holds the number rounded to a specified format.

Input Validation

If IsNumeric(num) And IsNumeric(sigs) Then

• This checks if both num and sigs are numeric values. If either is not numeric, the function returns a "#N/A" error.

Handling Insufficient Significant Figures

If sigs < 1 Then
    RoundSF = CVErr(xlErrNum)

• If sigs is less than 1, the function returns a "#NUM!" error, indicating an invalid parameter.

Rounding Logic

numround = WorksheetFunction.Text(num, "." & String(sigs, "0") & "E+000")

• This line converts the number to scientific notation, retaining the specified number of significant figures.

Calculating the Exponent

If num = 0 Then
    exponent = 0
Else
    exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1)
End If

• For zero, the exponent is set to zero.
• For non-zero numbers, it calculates the exponent based on the logarithm (base 10) of the absolute value of numround, effectively determining the scale of the number.

Determining Decimal Places

decplace = (sigs - (1 + exponent))

• This calculates how many decimal places are necessary after the significant figures are accounted for.

Formatting the Output

If decplace > 0 Then
    fmt_right = String(decplace, "0")
    fmt_left = "0."
Else
    fmt_right = ""
    fmt_left = "0"
End If

• If more decimal places are needed, it constructs the formatting strings accordingly.

Final Formatting and Return

RoundSF = WorksheetFunction.Text(numround, fmt_left & fmt_right)

• The formatted rounded number is converted back to a string and returned as the function's output.

Error Handling for Non-Numeric Inputs

Else
    RoundSF = CVErr(xlErrNA)
End If

• If the function receives non-numeric inputs, a "#N/A" error is returned.

Summary

The RoundSF function effectively rounds a numeric value to a specified number of significant figures, handling input validation and formatting appropriately. If errors occur due to invalid inputs, the function returns corresponding Excel error messages.

Key Concepts

1. Significant Figures: The digits that carry meaningful information about the precision of a number.
2. Logarithms: Used here to derive the exponent for scientific notation.
3. Error Handling in VBA: Properly returns specific error messages using CVErr.

Additional Example

To illustrate its usage:

Dim result As String
result = RoundSF(123.456, 3) ' This would return "123"

Here, 123.456 is rounded to three significant figures.

This comprehensive breakdown aims to enhance the user’s understanding of the function’s internal mechanics and its application within Excel VBA. For further exploration of similar concepts, consider courses offered on the Enterprise DNA platform.