Trouble with VBA loop

[jwebster1979]

I am trying to buile a funtion that counts the number of days I will reach a
set dollar amount based off of what I am currently spending per day. The code
is as following:

Public Function DayAppropriatedAmountWillBeMet(BeginDate As Date,
CurrentSpent As Currency, AppropriatedAmount As Currency)
'Function will figure what day Appropriated Amount will be reached based off
current spending _
or when you will run out of money

Dim DaysPassed As Double
Dim M As Currency
Dim U As Currency
Dim MoneySpentPerDay As Currency
Dim Counter As Integer

DaysPassed = DateDiff("d", BeginDate, Now())
MoneySpentPerDay = CurrentSpent / DaysPassed

M = AppropriatedAmount
Counter = 0
U = CurrentSpent
Do Until U >= AppropriatedAmount
U = U + MoneySpentPerDay
Counter = Counter + 1
Loop
DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, Date)
End Function

For some reason I keep getting an overflow error on Counter. my logic is
that this thing will loop U (that is incremented by MoneySpentPerDay) until
it is greater than my set amount of money or AppropriatedAmount. Counter is
counting the number of times it goes through this loop, but I always get the
overflow error that debugs to my Counter = Counter +1. I am not sure if I
need a different kind of loop or my loop can't handle doing that. PLEASE HELP
it is driving me nuts.
thanks

 

[Gina Whipp]

jwebster1979,

"my logic is that this thing will loop U (that is incremented by
MoneySpentPerDay) until it is greater than my set amount of money or
AppropriatedAmount."

Then your loop will never end... Perhaps you should elaborate on what you
are trying to accomplish.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I am trying to buile a funtion that counts the number of days I will reach a
set dollar amount based off of what I am currently spending per day. The
code
is as following:

Public Function DayAppropriatedAmountWillBeMet(BeginDate As Date,
CurrentSpent As Currency, AppropriatedAmount As Currency)
'Function will figure what day Appropriated Amount will be reached based off
current spending _
or when you will run out of money

Dim DaysPassed As Double
Dim M As Currency
Dim U As Currency
Dim MoneySpentPerDay As Currency
Dim Counter As Integer

DaysPassed = DateDiff("d", BeginDate, Now())
MoneySpentPerDay = CurrentSpent / DaysPassed

M = AppropriatedAmount
Counter = 0
U = CurrentSpent
Do Until U >= AppropriatedAmount
U = U + MoneySpentPerDay
Counter = Counter + 1
Loop
DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, Date)
End Function

For some reason I keep getting an overflow error on Counter. my logic is
that this thing will loop U (that is incremented by MoneySpentPerDay) until
it is greater than my set amount of money or AppropriatedAmount. Counter is
counting the number of times it goes through this loop, but I always get the
overflow error that debugs to my Counter = Counter +1. I am not sure if I
need a different kind of loop or my loop can't handle doing that. PLEASE
HELP
it is driving me nuts.
thanks

 

[Daryl S]

Jwebster1979 -

If no money has been spent so far, then the loop will never end and will
eventually hit the integer limits. If no days have passed, then you will be
dividing by zero which will cause an overflow error.

There is an easier way to get your answer...

Public Function DayAppropriatedAmountWillBeMet(BeginDate As Date,
CurrentSpent As Currency, AppropriatedAmount As Currency)
'Function will figure what day Appropriated Amount will be reached based off
current spending _
or when you will run out of money

Dim DaysPassed As Double
Dim MoneySpentPerDay As Currency
Dim DaysToGo As Integer

If DateValue(BeginDate) = Date() Then 'This this is the start date - return
BeginDate (or choose another date type default)
DayAppropriatedAmountWillBeMet = BeginDate
Else
If CurrentSpent <= 1 Then 'If nothing spent, then return BeginDate (or
choose another date type default) DayAppropriatedAmountWillBeMet =
BeginDate
Else
DaysPassed = DateDiff("d", BeginDate, Now())
MoneySpentPerDay = CurrentSpent / DaysPassed
DaysToGo = (AppropriatedAmount - CurrentSpent)/MoneyspentPerDay) + 1
'Add one because this is an integer field
DayAppropriatedAmountWillBeMet = DateAdd("d", DaysToGo, Date()) End If
end If

End Function

 

[jwebster1979]

I appreciate your help works great. I did have a curiosity question why add 1
to the integer value of Counter?

 

[jwebster1979]

Function works great. Unfortunatly after playing with it the output of it is
so large at the begining it wont mean anything to the people looking at it.
But it did help me to work through it thanks for all the help!

That way it mirrors the logic of your loop solution and returns the date
when the appropriated amount will be completely expended. It is possible
that this could happen one day earlier if the division works out precisely,
but that would probably be unusual.

Do note what the others said about there being no expenditure to date. If
the value of DaysPassed or CurrentSpent is zero this would cause an error.
However, calling the function in such a situation would serve no purpose as
the basis of the computation is the continuation of a regular level of
expenditure to date. Probably the most appropriate way to handle this would
be to declare the function to return a Variant rather than a Date and return
a Null in such a scenario:

DaysPassed = DateDiff("d", BeginDate, Date)
If DaysPassed > 0 And CurrentSpent > 0 Then
MoneySpentPerDay = CurrentSpent / DaysPassed

Counter = (AppropriatedAmount / MoneySpentPerDay) + 1
DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, BeginDate)
End If

Ken Sheridan
Stafford, England

I appreciate your help works great. I did have a curiosity question why add 1
to the integer value of Counter?

The code looks fine in itself (though the M variable is redundant), but the
overflow on the integer data type variable suggests that you are counting

[quoted text clipped - 69 lines]

it is driving me nuts.
thanks

 

[Daryl S]

Jwebster1979 -

The 1 is added because Counter is an integer variable. When you divide the
amount by the days spent, you will most likely get a result that includes a
fractional part of a day. For example, if the AppropriatedAmount was $150,
and the MoneySpendPerDay was $28, then the counter would be 5.357 days, but
since counter is integer, it would only show 5 days. The 1 will take care of
these fractional days by saying it will be paid out on the 6th day.

--
Daryl S

Function works great. Unfortunatly after playing with it the output of it is
so large at the begining it wont mean anything to the people looking at it.
But it did help me to work through it thanks for all the help!

That way it mirrors the logic of your loop solution and returns the date
when the appropriated amount will be completely expended. It is possible
that this could happen one day earlier if the division works out precisely,
but that would probably be unusual.

Do note what the others said about there being no expenditure to date. If
the value of DaysPassed or CurrentSpent is zero this would cause an error.
However, calling the function in such a situation would serve no purpose as
the basis of the computation is the continuation of a regular level of
expenditure to date. Probably the most appropriate way to handle this would
be to declare the function to return a Variant rather than a Date and return
a Null in such a scenario:

DaysPassed = DateDiff("d", BeginDate, Date)
If DaysPassed > 0 And CurrentSpent > 0 Then
MoneySpentPerDay = CurrentSpent / DaysPassed

Counter = (AppropriatedAmount / MoneySpentPerDay) + 1
DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, BeginDate)
End If

Ken Sheridan
Stafford, England

I appreciate your help works great. I did have a curiosity question why add 1
to the integer value of Counter?

The code looks fine in itself (though the M variable is redundant), but the
overflow on the integer data type variable suggests that you are counting
[quoted text clipped - 69 lines]
it is driving me nuts.
thanks