CODEWITHSUNDEEP
vbaexcel
user3074986
user3074986

Reputation: 33

Problems format time in VBA

I have a big problem with formats. When VBA write a number (Time() - actualtime) the result is in seconds, but excel thinks that is days! so, when i put this command: Selection.NumberFormat = "[h]:mm:ss.00" the seconds are days!

A solution is adding /(24*3600) but this is give me an error, perhaps because it is a really small number.

ActiveCell = ((Timer() - startTime) where starttime is a Timer() define some time back.

Upvotes: 1

Views: 377

Answers (3)

Sam
Sam

Reputation: 7303

It's due to the type conversion. Because 3600 and 24 are both integers, the result of multiplying them are converted to a 16 bit Int. A 16 bit Int has a range of -32768 to +32767

You can see this by running the following line which does not give an error and returns back 32767

MsgBox 32766 + 1

Whereas the below line fails with an overflow error due to it exceeding the maximum value.

MsgBox 32766 + 2

As it has already been mentioned, you can circumvent this by dividing the values separately

(Time() - actualtime) / 3600 / 24 will work..

Also, you can tell excel that you want to use a different type,

 MsgBox (Time() - actualtime) / (24# * 3600)   'use Double
 MsgBox (Time() - actualtime) / (24& * 3600)   'use Long

The hash or ampersand after the 24 tells excel that you want the value to be used as a defined type and these statements won't fail with an overflow error.

Upvotes: 1

Stewbob
Stewbob

Reputation: 16899

Fascinating.

actualtime / (24 * 3600)

produces an overflow error in VBA.

actualtime / 24 / 3600

does not.

Something about multiplying 24 and 3600 produces an overflow error.

Upvotes: 1

Soulus101
Soulus101

Reputation: 223

Try

Timeserial(0,0,Time() - actualtime)

Timeserial will return a time value, and putting the value you want in the "seconds" argument will ensure the number returned is treated correctly.

Let me know if this helps!

Upvotes: 3

Related Questions