run-time error '6' - overflow - once I declare a pre-used variable name as integer

[ker_01]

Excel 2003 on WinXP run-time error '6' - overflow

I'm writing more an more blocks of code on a program that is (or was) acting
as expected. I'm not a programmer by trade or training, so although I know I
should use option explicit on, it isn't one of the habits I've picked up
yet.

I often use a random variable name for loops, and often use the same
variable name in the same sub or in different subs. I'm just starting to
pass more and more information to functions, and I was having trouble with
Excel accepting the transferred parameters unless I declared the sending and
recieving variables explicitly (and the same, of course). One variable that
I need to pass to my function is my loop variable (it refers to a
position/location on a sheet or array).

So, since I use it as an integer in my function, I declared it as an integer
in my main sub. However, when it hits my loop, I now get a run-time error
'6' overflow. Can anyone explain why this happens, and the proper way to
effectively pass that loop variable as an integer to my function?

Many thanks,
Keith

'irrelevant code blocks deleted
Sub AssembleSchedule()

Dim EachDate As Date
Dim EachDay As Integer
'dim the other variables that are passed to my procedure

For EachDay = 1 To 50000 'error occurs here before looping begins;
'error did not occur here
before the dim
'but I had trouble passing
the data to the function
'do stuff
Next

For EachDay = 1 To 50000
'do other stuff
Next

For EachDay = 0 To 14
'do stuff for a target 2-week window of dates
Next

dummyvariable = BatchingSearch(BatchingDaysOut, EachDay,
RemainingCapacity, UseDept)

End sub

Function BatchingSearch(sDaysOut As Integer, sNowDate As Integer,
sRemainingCapacity As Long, CurrDept As String)
'do other stuff
End function

 

[Dave Patrick]

Try dimensioning as Long. An integer valid range is -32,768 to 32,767


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[smartin]

Excel 2003 on WinXP run-time error '6' - overflow
[snipped]

'irrelevant code blocks deleted
Sub AssembleSchedule()

Dim EachDate As Date
Dim EachDay As Integer
'dim the other variables that are passed to my procedure

For EachDay = 1 To 50000 'error occurs here before looping begins;

ker,

Integer type values cannot exceed +/- 32767 or so. Try Dim EachDay as
Long, or restricting the loop to a range Integer can accept.

I did not follow your code much beyond here so something else may be amiss.

 

[ker_01]

Doh! Thanks Dave and SMartin- I had made several changes and was eliminating
one potential cause at a time...and had forgotten about the integer
limitation. I'll change it to long, and I'm sure I'll have no problems.
Thanks!
Keith

Try dimensioning as Long. An integer valid range is -32,768 to 32,767


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Excel 2003 on WinXP run-time error '6' - overflow

I'm writing more an more blocks of code on a program that is (or was)
acting as expected. I'm not a programmer by trade or training, so
although I know I should use option explicit on, it isn't one of the
habits I've picked up yet.

I often use a random variable name for loops, and often use the same
variable name in the same sub or in different subs. I'm just starting to
pass more and more information to functions, and I was having trouble
with Excel accepting the transferred parameters unless I declared the
sending and recieving variables explicitly (and the same, of course). One
variable that I need to pass to my function is my loop variable (it
refers to a position/location on a sheet or array).

So, since I use it as an integer in my function, I declared it as an
integer in my main sub. However, when it hits my loop, I now get a
run-time error '6' overflow. Can anyone explain why this happens, and the
proper way to effectively pass that loop variable as an integer to my
function?

Many thanks,
Keith

'irrelevant code blocks deleted
Sub AssembleSchedule()

Dim EachDate As Date
Dim EachDay As Integer
'dim the other variables that are passed to my procedure

For EachDay = 1 To 50000 'error occurs here before looping begins;
'error did not occur here
before the dim
'but I had trouble passing
the data to the function
'do stuff
Next

For EachDay = 1 To 50000
'do other stuff
Next

For EachDay = 0 To 14
'do stuff for a target 2-week window of dates
Next

dummyvariable = BatchingSearch(BatchingDaysOut, EachDay,
RemainingCapacity, UseDept)

End sub

Function BatchingSearch(sDaysOut As Integer, sNowDate As Integer,
sRemainingCapacity As Long, CurrDept As String)
'do other stuff
End function

 

[Dave Patrick]

You're welcome.


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect