CODEWITHSUNDEEP
excelvbaroundinguser-defined-functions
John Liu
John Liu

Reputation: 37

vba user defined function, dealing with decimal points and trailing zeros

I am trying to build an UDF, dealing with floating decimal points (like if input value was between 1 to 10, round value to 2 dp and if input value was between 10 to 100, round value to 1 dp, etc). And I would like to keep the trailing zeros.

I wrote the code based on these 2 threads:

How do I keep the trailing zero (or zeros) when I round with Excel VBA?
change cell format within functions in excel VBA

I know my major problem was that I am not used to UDF syntax.

Below are my lines:

Public Function FDP(Rng As Range)
    Dim DP As Integer, CelRef As Range
    
    For Each CelRef In Rng
        If CelRef.Value2 >= 100 Then
            DP = 0
        ElseIf CelRef.Value2 < 100 And CelRef.Value2 >= 10 Then
            DP = 1
        ElseIf CelRef.Value2 < 10 And CelRef.Value2 >= 1 Then
            DP = 2
        ElseIf CelRef.Value2 < 1 And CelRef.Value2 >= 0.1 Then
            DP = 3
        ElseIf CelRef.Value2 < 0.1 Then
            DP = 4
        Else
            FDP = WorksheetFunction.Round(CelRef.Value2, 3 - (Int(log(CelRef.Value2)) + 1))
        End If
    
        FDP = WorksheetFunction.Round(CelRef.Value2, DP)
        CelRef.NumberFormat = "0." & WorksheetFunction.Rept("0", DP) ' not sure if this line was correct
    Next CelRef
End Function

If I change the line:

 CelRef.NumberFormat = "0." & WorksheetFunction.Rept("0", DP)

to

FDP.NumberFormat = "0." & WorksheetFunction.Rept("0", DP)

the function does not work. While I keep this line, a cell with value of 3.40112458 will be converted to 3.4, not 3.40 as I expected.

How should I correct my lines so that it will work? Any help would be appreciated.

Below lines were my original version, dealing with the rounding to floating decimal points:

Public Function FDP(ByVal CelRef As Double) As Double   

Dim DP As Integer           
If CelRef >= 100 Then 
DP = 0                
ElseIf CelRef < 100 And CelRef >= 10 Then
DP = 1                
ElseIf CelRef < 10 And CelRef >= 1 Then
DP = 2
ElseIf CelRef < 1 And CelRef >= 0.1 Then               
DP = 3
ElseIf CelRef < 0.1 Then
DP = 4
Else
FDP = WorksheetFunction.Round(CelRef, 3 - (Int(log(CelRef)) + 1)) ' this line round to 3 significant figures
End If

FDP = WorksheetFunction.Round(CelRef, DP)
        
End Function

Upvotes: 0

Views: 552

Answers (1)

jalex
jalex

Reputation: 1524

Fist the decimal places can be quickly computed with

DP = 3-WorksheetFunction.Floor(WorksheetFunction.Log10(Abs(x)), 1)

with the following results

' x           DP
' 0.000502655 7
' 0.002513274 6
' 0.012566371 5
' 0.062831853 5
' 0.314159265 4
' 1.570796327 3
' 7.853981634 3
' 39.26990817 2
' 196.3495408 1
' 981.7477042 1
' 4908.738521 0
' 24543.69261 -1
' 122718.463  -2

and then to round with trailing zeros your only option with a UDF is to return a formatted string.

scr1

and

scr2

Function RoundCustom(ByVal x As Double) As String
    Dim DP As Long, s As Long, t As String
    s = CLng(Sgn(x))
    x = Abs(x)
    DP = 3 - CLng(WorksheetFunction.Floor(WorksheetFunction.Log10(Abs(x)), 1))
    If DP >= 4 Then DP = DP - 1 ' if value <1.0 reduce the DP by one
    DP = WorksheetFunction.Max(0, DP)
    x = Round(x, DP)
    t = Format(s*x, "0." & Strings.String(DP, "0"))
    RoundCustom = t
End Function

Upvotes: 1

Related Questions