Large values not being handled by UDF

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
257
Platform
  1. Windows
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.

VBA Code:
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?
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,613
Office Version
  1. 365
Platform
  1. Windows
The Long variable (which you have declared x as) has a limit of 2,147,483,647. Try declaring it as Double instead.
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,791
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi, try this code:
VBA 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
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,791
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Glad it helped! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,711
Members
412,613
Latest member
EFRATA
Top