0

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
John Liu
  • 37
  • 5
  • How are you calling this function? – BigBen Oct 05 '20 at 14:25
  • like .Cells(r, c).Offset(, 6) = FDP(anotherSheet.Cells(TrgR, 2)) in a sub – John Liu Oct 05 '20 at 14:30
  • @BigBen My function was originally written to deal with value of cell only like .cells(r,c).value = FDP(.cells(r,c).value), but I cannot fit the "keeping trailing zeros" part to the function that I have to switch to a function dealing with cell object. – John Liu Oct 05 '20 at 14:33
  • 1
    Why do you implement this as function if it is programmed to handle a Range? What do you do with the result? You cannot use it as UDF anyhow, an UDF shoudn't modify the sheet – FunThomas Oct 05 '20 at 14:57
  • @FunThomas I hope UDF can make my code looks shorter and easier to be maintained. My second reference thread seems to me it is possible to handle decimal points as well as cell format at the same time. – John Liu Oct 05 '20 at 15:09
  • to handle my table, I have to perform some calculations, to round results to certain decimal points, to store results to some target cells and to make sure the display of results be including trailing zeros. The calculations involves looping through multiple sheets and values. The rounding and cell formatting were the very last step of my procedures. – John Liu Oct 05 '20 at 15:16
  • 3
    In a UDF called from a worksheet the UDF cannot perform actions on the sheet - it can only return a value or array of values. So you cannot set the NumberFormat from a UDF. – Tim Williams Oct 05 '20 at 16:23
  • @TimWilliams I see. I may have to use conditional formatting instead. Thanks for enlightening. – John Liu Oct 05 '20 at 16:26
  • Just pointing [to an SO answer about rounding to significant digits](https://stackoverflow.com/a/374470/13813219) – JAlex Oct 05 '20 at 18:30

1 Answers1

1

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
JAlex
  • 1,486
  • 8
  • 19
  • thanks for your help. Precisely, my steps dealing with decimal points wasn't a significant figure type. For value of 76699039.4, I just need to round my value to integer i.e. 76699039. My situation is kind of job-specific. But really thanks for supplying your code. – John Liu Oct 05 '20 at 15:44
  • So what are the rules for displaying "XXX.YYY" values? And are you ok with storing text instead of numbers then? – JAlex Oct 05 '20 at 15:57
  • Please see my edits. Rules were like value >100, round to 0 dp; value <=100 and >10, round to 1 dp; value <=10 and >1, round to 2 dp, value <=1 and >0.1, round to 3 dp, value <=0.1 and >0.01, round to 4 dp, else round to 3 sig.fig. Storing as text is less okay. – John Liu Oct 05 '20 at 16:11
  • @JohnLiu There is no way to make a UDF to modify the formatting. So the best option is to store the formatted text next to the actual values so you have both. See edited answer now. – JAlex Oct 05 '20 at 18:43
  • thanks for your help. Your code helped my work a a lot. – John Liu Oct 05 '20 at 20:15