user def function for significant figures

[stuck]

I've come across some code I didn't know I had. It was originally written by a work colleague way back in 1994 as an Excel 5 macro and it formats numbers to a given number of significant figures. I added it an Excel 2010 workbook to see how it behaved and it appears to work as expected.

The code was devoid of any comments so I've been trying to annotate it however there are aspects of the code that I'm not that clear about (e.g. the last DIM that has '* 1' on the end, what's that doing to the string?). I feel the code could be streamlined but I'm not sure how (e.g. is it really necessary for the function to call another function to do a simple division? Perhaps it was in Excel v5 but surely not now?). I'd value any comments / recoding suggestions that anyone would care to offer.

Thanks,

Ken
PS I can't speak the original author, he retired many moons ago.

Code: Select all

Option Explicit

Public Function SIGFIG(value As Double, length As Integer, max_DP As Integer, neg_flag As Integer) As Variant
'
' User def. function to format numbers to a given number of significant figures.
' Code originally developed as Excel 5 module
'

    'function arguments:
    ' value: the cell ref to be formatted
    ' length: number of sig figs required
    ' max_DP: why require this limit? 
    ' neg_flag: 0, 1 or anything else, to handle data beginning either '-' (0) or '<' (1) or return an error if neither (anything else)
    
    On Error GoTo 0

    'declare variables
    Dim sign As Integer
    Dim shift As Double
    Dim left As Integer
    Dim right As Integer
    Dim digits As Long
    Dim olddigits As Long
    Dim absvalue As Double
    Dim newvalue As Double
    Dim sigformat As String
    Dim negchar As String * 1

    'trap v. large or v. small numbers
    If Abs(value) > 10 ^ 9 Then GoTo lblErrorHandler
    If Abs(value) < 10 ^ -9 Then GoTo lblErrorHandler
    'trap invalid number of sig. figs
    If length < 1 Then GoTo lblErrorHandler
    If length > 9 Then GoTo lblErrorHandler
    'why this check?
    If max_DP < 0 Then GoTo lblErrorHandler
    If max_DP > 9 Then GoTo lblErrorHandler

    'decide how to handle data that is negative, less than or anything else
    Select Case neg_flag
    Case 0 'data is negative
        negchar = "-"
    Case 1 'data is less than
        negchar = "<"
    Case Else 'anything else, return error
        GoTo lblErrorHandler
    End Select
    
    'populate variables
    sign = Sgn(value)
    absvalue = Abs(value)
    left = Int(Log10(absvalue)) + 1
    right = length - left
    
    'derive the required value
    shift = 10 ^ (right)
    digits = Int(absvalue * shift)
    olddigits = digits

    If (absvalue * shift) - digits >= 0.5 Then
        digits = digits + 1
    End If

    If Len(Trim(digits)) > Len(Trim(olddigits)) Then
        left = left + 1
        right = right - 1
    End If

    newvalue = digits / shift

    If right > max_DP Then
        right = max_DP
        If newvalue * 10 ^ right < 1 Then
            newvalue = 1 / 10 ^ right
            sign = -1
        End If
    End If

    Select Case newvalue
    Case Is < 1
        sigformat = Trim$("0." & String((right), "0"))
    Case Is > 0
        Select Case right
        Case Is < 1
            sigformat = "0"
        Case Is > 0
            sigformat = Trim$("0." & String((right), "0"))
        End Select
    End Select

    'set return value and exit
    sigformat = "  " & Trim$(sigformat) & "; " & negchar & Trim$(sigformat)
    SIGFIG = (Format(newvalue * sign, sigformat))
    Exit Function

'error handler
lblErrorHandler:
SIGFIG = CVErr(2036) 'returns the #NUM! error

End Function

Private Function Log10(value)
'
' Sub for log10, called by SIGFIGS
'

    Log10 = Log(value) / Log(10)
    
End Function

[HansV]

The line

Dim negchar As String * 1

declares negchar as a string of fixed length 1. In the old days, this was used to reduce memory usage if the length of a string was known in advance.
If a variable is declared as String, and if its value is 5 characters long, the variable uses 5+1=6 bytes: 5 for the string itself, 1 to store its length.
If a variable is declared as String * 5, the length byte isn't needed, so the variable uses 5 bytes, whether its value is 0,1, 2, 3, 4 or 5 characters long.

Nowadays this is hardly used any more, for who cares about 1 byte of memory?

Instead of using the Log10 function, one could use the built-in worksheet function of the same name:

Code: Select all

    left = Int(Application.Log10(absvalue)) + 1

The max_DP argument is used to specify the maximum number of decimal places used in the return value.

[stuck]

Thank you Hans.

The max_DP argument is used to specify the maximum number of decimal places used in the return value.

I wonder why the author wanted to limit that? Probably because the data he was working with (from a Mass Spec.) would be meaningless if the number of dec. places was large. That's the sort of 'real world', rather than 'bare code', logic that the author would have cared about.

Ken