Set long variable in VBA
Simple question that I can't find anywhere. When I do arithmetic, it seems that the natural type is value as a 16Bit Integer.
I'm trying to store the result 60 * 60 * 8 * 5
in long, but I get an error Overflow
before it even has a chance to store the number as Long
:
Dim secondsInAWorkWeek As Long secondsInAWorkWeek = 60 * 60 * 8 * 5
Long should happily store anything until 2 31 = 2,147,483,647
How can I perform the multiplication safely to convert to long
source to share
There are certain conventions to force a literal of a specific type from a generic type (default): 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 type safe, hardly more explanation / comment needed, and it will be easy to change when they vote for 10 hour workdays. :-)
Ok, according to Overflow when multiplying integers and assigning to longs , you need to provide start-up in Excel, otherwise it will default to use each value when multiplied by an integer.
Just start casting before CLng
and he will take care of the rest:
Dim secondsInAWorkWeek As Long secondsInAWorkWeek = CLng(1) * 60 * 60 * 8 * 5
source to share