Large values not being handled by UDF

[Juggler_IN]

The below UDF is executing for 9 digit values and fails beyond 9 digits.

I have tried decimal data type but no luck. How I can make this UDF take higher digit values.

It works for x = 127,098,075 but fails for x = 50,428,677,591.

Public Function Units(x As Long) As String

    Dim Seconds
    Dim Minutes
    Dim Hours
    Dim Days
    Dim DayString As String
    Dim HourString As String
    Dim MinuteString As String
    Dim SecondString As String

    Seconds = Int(x Mod 60)
    Minutes = Int(x \ 60 Mod 60)
    Hours = Int(x \ 3600 Mod 24)
    Days = Int(x \ 3600 \ 24)

    DayString = "d "
    HourString = "h "
    MinuteString = "m "
    SecondString = "s"

    Select Case Days
    Case 0
        Units = Format(Hours, "0") & HourString & _
                Format(Minutes, "0") & MinuteString & _
                Format(Seconds, "0") & SecondString
    Case Else
        Units = Days & DayString & _
                Format(Hours, "0") & HourString & Format _
                (Minutes, "00") & MinuteString & _
                Format(Seconds, "00") & SecondString
    End Select

End Function

Any possible tweak?

 

[jasonb75]

The Long variable (which you have declared x as) has a limit of 2,147,483,647. Try declaring it as Double instead.

 

[ZVI]

Hi, try this code:

Public Function Units(ByVal x As Double) As String
    Dim Seconds, Minutes, Hours, Days
    Seconds = FnMod(x, 60)
    Minutes = FnMod(x / 60#, 60)
    Hours = FnMod(x / 3600#, 24)
    Days = Fix(x / 86400#)
    Units = IIf(Days, Days & "d ", "") & _
            Format(Hours, "0") & "h " & _
            Format(Minutes, "00") & "m " & _
            Format(Seconds, "00") & "s"
End Function

Function FnMod(N As Double, Base As Long)
  FnMod = Fix(Fix(N) - Fix(N / Base) * Base)
End Function

Sub Test()
  Dim v As Double
  v = 60635462889#
  Debug.Print Units(v)
End Sub

 

[Juggler_IN]

@ZVI ; Thanks, it works!

 

[ZVI]

Glad it helped!