Set Long Variable in VBA

Question

Simple question that I can't find anywhere. When I'm doing arithmetic, it seems the natural type is to treat value as 16Bit Integers.

I'm trying to save the result of 60 * 60 * 8 * 5 into a long, but I get an Overflow error before it even has a chance to save the number as a Long:

Dim secondsInAWorkWeek As Long
secondsInAWorkWeek = 60 * 60 * 8 * 5

Long should happily store anything up to 2³¹ = 2,147,483,647

How can I perform the multiplication safely to convert into a long

Answer 1

There are certain conventions in order to force a literal to a specific type from a generic (default) type: http://msdn.microsoft.com/en-us/library/dzy06xhf.aspx

Which leads to this code:

Dim secondsInAWorkWeek As Long

Let secondsInAWorkWeek = 60& * 60& * 8& * 5&

or:

Const DAYS_IN_WEEK = 5&
Const HOURS_IN_DAY = 8&
Const MINUTES_IN_HOUR = 60&
Const SECONDS_IN_MINUTE = 60&

Dim secondsInAWorkWeek As Long

Let secondsInAWorkWeek = _
     DAYS_IN_WEEK _
   * HOURS_IN_DAY _
   * MINUTES_IN_HOUR _
   * SECONDS_IN_MINUTE

A lot more to write, true, but is type safe, barely needs any more explanations/comments, and it will be easy to change when they'll vote for 10 hours workdays. :-)

Answer 2

Ok, according to Overflow when multiplying Integers and assigning to Long, you need to give Excel a running start, otherwise it will default each value in the multiplication to an integer.

Just start off by casting to long with CLng and it will take care of the rest:

Dim secondsInAWorkWeek As Long
secondsInAWorkWeek = CLng(1) * 60 * 60 * 8 * 5