If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trouble with VBA loop
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 |
#2
|
|||
|
|||
Trouble with VBA loop
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 ahead into the next century, so I'd suspect the values being passed into the function. If you really do need to count that far ahead declare Counter as Long. However, you don't really need the loop. You can do the same thing more efficiently with: Dim DaysPassed As Double Dim CurrentBalance As Currency Dim MoneySpentPerDay As Currency Dim Counter As Long DaysPassed = DateDiff("d", BeginDate, Date) MoneySpentPerDay = CurrentSpent / DaysPassed CurrentBalance = AppropriatedAmount - CurrentSpent Counter = (CurrentBalance / MoneySpentPerDay) + 1 DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, Date) Or with: Dim DaysPassed As Double Dim MoneySpentPerDay As Currency Dim Counter As Long DaysPassed = DateDiff("d", BeginDate, Date) MoneySpentPerDay = CurrentSpent / DaysPassed Counter = (AppropriatedAmount / MoneySpentPerDay) + 1 DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, BeginDate) Ken Sheridan Stafford, England jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Trouble with VBA loop
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 "jwebster1979" wrote in message ... 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 |
#4
|
|||
|
|||
Trouble with VBA loop
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 -- Daryl S "jwebster1979" wrote: 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 |
#5
|
|||
|
|||
Trouble with VBA loop
I appreciate your help works great. I did have a curiosity question why add 1
to the integer value of Counter? "KenSheridan via AccessMonster.com" wrote: 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 ahead into the next century, so I'd suspect the values being passed into the function. If you really do need to count that far ahead declare Counter as Long. However, you don't really need the loop. You can do the same thing more efficiently with: Dim DaysPassed As Double Dim CurrentBalance As Currency Dim MoneySpentPerDay As Currency Dim Counter As Long DaysPassed = DateDiff("d", BeginDate, Date) MoneySpentPerDay = CurrentSpent / DaysPassed CurrentBalance = AppropriatedAmount - CurrentSpent Counter = (CurrentBalance / MoneySpentPerDay) + 1 DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, Date) Or with: Dim DaysPassed As Double Dim MoneySpentPerDay As Currency Dim Counter As Long DaysPassed = DateDiff("d", BeginDate, Date) MoneySpentPerDay = CurrentSpent / DaysPassed Counter = (AppropriatedAmount / MoneySpentPerDay) + 1 DayAppropriatedAmountWillBeMet = DateAdd("d", Counter, BeginDate) Ken Sheridan Stafford, England jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com . |
#6
|
|||
|
|||
Trouble with VBA loop
The value is used in the DateAdd expression.
jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Trouble with VBA loop
Sorry, misunderstood the question. I think it depends on whether you are
looking for the day when the last "payment" is made, or the day when there are no more payments to be made. Best would be to test it with a situation where you know the expected result. jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Trouble with VBA loop
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 jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Trouble with VBA loop
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! "KenSheridan via AccessMonster.com" wrote: 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 jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com . |
#10
|
|||
|
|||
Trouble with VBA loop
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 "jwebster1979" wrote: 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! "KenSheridan via AccessMonster.com" wrote: 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 jwebster1979 wrote: 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 -- Message posted via http://www.accessmonster.com . |
Thread Tools | |
Display Modes | |
|
|