VBA Code to convert calculated value to Hours,Minutes & Seconds is failing

[Ruts]

Hey guys and girls, this is doing my head in - the concept works fine if I write it in cells on a sheet - so it "SHOULD" work in my VBA but it isn't.

Quite possibly I am missing the simplest thing and just am too close to see it.

Anybody see where I have buggered it up ??
Below are the relevant snippets of the code - all the DIMS statements are at the start of the code with the variables defined immediately prior to display.

Dim timeCalcColumns As Integer       ' The number of columns of data - used to determine max cells to process
Dim timeCalcRows As Integer         ' The number of rows of data - used to determine max cells to process
Dim timeStart As String
Dim timeFinish As String
Dim processCells As Long
Dim processTime As Long
Dim actualHour As Long
Dim actualMinute As Long
Dim actualSecond As Long
Dim remainingMinute As Long
Dim remainingSecond As Long


    processCells = timeCalcRows * timeCalcColumns
    processTime = processCells * 6.10269370636987E-08
    actualHour = Int(processTime * 24)
    actualMinute = processTime * 24 * 60
    actualSecond = processTime * 24 * 60 * 60
    remainingMinute = Int(actualMinute - (actualHour * 60))
    remainingSecond = Int(actualSecond - (actualMinute * 60))

    
    MsgBox "About to process " & timeCalcRows & " personnel " & "and " & timeCalcColumns & " accomplishments." & Chr$(10) & Chr$(10) & "Processing should take approximately : " & actualHour & " hours " & remainingMinute & " minutes " & remainingSecond & " seconds."

timeCalcColumns returns a value of 54
timeCalcRows returns a value of 102
processTime is the value determined to process each cell * the number of cells (5508)

using these values processing time should = 29 secs - but it shows as ZERO

any help on what I have done wrong will be very greatly appreciated.

 

[Richard Schollar]

You have dimensioned process time as a Long - try declaring it as a Double instead as this statement sets it to zero currently:

processTime = processCells * 6.10269370636987E-08

You may also need to do this with all your other values too.

 

[Ruts]

Tried the Double variable - got yet another outcome -1 instead of 0 - still not the 29 I was hoping for.

 

[Ruts]

Img - http://www.facebook.com/photo.php?pid=1783663&l=beb1c0331a&id=597597747

and

Img - http://www.facebook.com/photo.php?pid=1783664&l=a7a245b806&id=597597747

They didn't seem to want to embed in the post above

 

[Richard Schollar]

Something else must be present in your code as it works as described for me (when processTime is declared as a Double). In the VBE, make sure you have the Locals window visible (View>Locals Window) as you can track what values your variables hold as the code is executed. If you step thru the code with F8, you can execute a line at a time, and see where the erroneous values are coming from.

 

[Ruts]

Thanks that did work - found the issues - it was in my calculation:

remainingMinute = Int(actualMinute - (actualHour * 60))
remainingSecond = Int(actualSecond - (actualMinute * 60))

now reads

remainingMinute = Int(actualMinute) - (Int(actualHour) * 60)
remainingSecond = Int(actualSecond) - (Int(actualMinute) * 60)