0


I have written the user-defined function (UDF) below for an Excel sheet at work. It rounds to significant figure and is able to handle trailing zeros.

Actually, the UDF works as intended! The only thing, that you need to be aware off is that it convert the number to text.

However, I'm just a bit suspicious that I've overlooked something. Compared to the others functions, that I have found (Eg. https://www.vertex42.com/ExcelTips/significant-figures.html), it seems almost too simple.

Public Function ROUNDSF(num As Double, sigFig As Integer)

Dim sigPlace As Integer
Dim numFormat As String

sigPlace = sigFig - (1 + Int(Log(num) / Log(10)))

numFormat = "0." & String(sigPlace, "0")

ROUNDSF= Format(num, numFormat)

End Function

Are there anything, that I overlooked in this UDF? Or any suggestions?

reuss
  • 121
  • 1
  • 1
  • 9
  • Code reviews are generally candidates for code review site. If you don't have an actual programming problem this is basically asking for a code review. – QHarr Jul 24 '18 at 13:13
  • 1
    @QHarr, I wasn't aware such sites exists (of course, they do). I'll remember this for next time. Thank you! – reuss Jul 25 '18 at 06:02
  • It is an excellent site :-) Just rememeber to read their How To Ask Page. – QHarr Jul 25 '18 at 06:06

1 Answers1

2

If you want it to return a value, you could close the UDF with:

ROUNDSF = Val(Format(num, numFormat))

Keep in mind though, this will then use existing default formatting so make sure there is either none, or that it's compatible with your requirement.

As per Vincent's comment, if your user's locale settings might use something other than . as a decimal separator, use Cdbl:

ROUNDSF = CDbl(Format(num, numFormat))

You should also trap for -ve SigPlace values that could arise, like so:

Public Function ROUNDSF(num As Double, sigFig As Integer) As Double

    Dim sigPlace As Integer
    Dim numFormat As String

    sigPlace = sigFig - (1 + Int(Log(num) / Log(10)))

    If sigPlace < 0 Then sigPlace = 0

    numFormat = "0." & String(sigPlace, "0")

    ROUNDSF = Cdbl(Format(num, numFormat))

End Function
CLR
  • 11,284
  • 1
  • 11
  • 29
  • 2
    `Format` with `.` will use the current decimal separator defined by system or application, and can use `,` for exemple according to the locale, but `Val` is locale independent and will always expect `.` as decimal separator, so your code will work only on locales where `.\`` is the decimal separator. Use `CDbl` to get the correct locale decimal separator. – Vincent G Jul 24 '18 at 13:39