Problem with VBA Code

[Briandc72]

I found this VBA code and am trying to format it to my specific spread sheet and I am having difficulties doing so. I am trying to find the date where my funds will run out and this code should work but it is not. I am not an expert in VBA so I am asking for some help in troubleshooting the Code. One Line on my spreadsheet is below along with the VBA code.

Thanks you to anyone that can help.


StartDate AvailableFunding AwardFee JuneForecast JulyForecast AugustForecast SeptemberForecast
6/1/2015 $430,000 $65,000 $137,000 $99,000 $121,000 $115,000



Public Function BurnoutDate(StartDate As Range, AvailableFunding As Range, AwardFee As Range, JuneForecast As Range, JulyForecast As Range, AugustForecast As Range, September As Range)
'Custom calculate value to zero
Dim curDate As Date
Dim curVal As Long, depVal As Integer
BurnoutDate = "#N/A"

If Not IsDate(StartDate) Then GoTo ExitOther
If Not IsNumeric(AvailableFunding) Then GoTo ExitOther
If Not IsNumeric(AwardFee) Then GoTo ExitOther
If Not IsNumeric(JuneForecast) Then GoTo ExitOther
If Not IsNumeric(JulyForecast) Then GoTo ExitOther
If Not IsNumeric(AugustForecast) Then GoTo ExitOther
If Not IsNumeric(SeptemberForecast) Then GoTo ExitOther

curDate = StartDate
curVal = AvailableFunding

Do While curVal > 0
Select Case curDate
Case Is = 1
depVal = JuneForecast
curVal = curVal - depVal
Case Else
depVal = JulyForecast
curVal = curVal - depVal
End Select
adjustment = JuneForecast + JulyForecast
If doDebug Then Debug.Print Format(curDate, "ddd"), depVal, curVal
curDate = curDate + 1

DoEvents
Loop

ExitNormal:
BurnoutDate = curDate
Exit Function

ExitOther:
udfBurnoutDate = "#N/A"

End Function

 

[ranman256]

I see you send everything in as a range. Are you allowed to campare a range to a number? Or does it need to be Isnumeric(JulyForecast.value)?
and curVal = AvailableFunding. does this give a number (AvailableFunding.value) , or a range?

and the loop DO WHILE CURVAL
are you looping thru records? Normally one would go row by row checking the data.

 

[V_Malkoti]

In your code you receive but never use values of AwardFee, AugustForecast and SeptemberForecast, why?

Also your code needs a few corrections:
1. Last function argument is named September but in your code you check SeptemberForcast.
2. depVal is assigned forecast values which are larger than integer range. Change that to long.
3. ExitOther label assigns value to udfBurnoutDate instead of BurnoutDate.

 

[Briandc72]

I changed the 3 things you listed in your reply. But your question "In your code you receive but never use values of AwardFee, AugustForecast and SeptemberForecast, why?" This is a code I found online and I am trying to get it to work with my data but that part of the code I have no clue how to expand with the other values.

Public Function BurnoutDate(StartDate As Range, AvailableFunding As Range, AwardFee As Range, JuneForecast As Range, JulyForecast As Range, AugustForecast As Range, SeptemberForecast As Range)
'Custom calculate value to zero
Dim curDate As Date
Dim curVal As Long, depVal As Long
udfBurnoutDate = "#N/A"

If Not IsDate(StartDate) Then GoTo ExitOther
If Not IsNumeric(AvailableFunding) Then GoTo ExitOther
If Not IsNumeric(AwardFee) Then GoTo ExitOther
If Not IsNumeric(JuneForecast) Then GoTo ExitOther
If Not IsNumeric(JulyForecast) Then GoTo ExitOther
If Not IsNumeric(AugustForecast) Then GoTo ExitOther
If Not IsNumeric(SeptemberForecast) Then GoTo ExitOther

curDate = StartDate
curVal = AvailableFunding

Do While curVal > 0
Select Case curDate
Case Is = 1
depVal = JuneForecast
curVal = curVal - depVal
Case Else
depVal = JulyForecast
curVal = curVal - depVal
End Select
adjustment = JuneForecast + JulyForecast
If doDebug Then Debug.Print Format(curDate, "ddd"), depVal, curVal
curDate = curDate + 1

DoEvents
Loop

ExitNormal:
udfBurnoutDate = curDate
Exit Function

ExitOther:
udfBurnoutDate = "#N/A"

End Function

 

[V_Malkoti]

Can you explain the logic you want to implement?

Do you want the function to return name of month in which the funding amount (AvailableFunding) will run out (June-September), starting from StartDate month? What should be return value if funding amount doesn't run out even after September?

 

[Briandc72]

I want to get the month day and year that the funds will run out from the start date and if the funds don't run out by September then nothing as a value.