VBA - function returns a #VALUE error

[ny248]

Hey everyone - new to the forum, hoping someone can help me! I have the following code for a function but am getting a #VALUE error when I enter the following arguments:

EDate: 10/10/18
RDate: 23/01/18
Rent: 300000
Area: 10000
SC: 10
VC: 15
VG: 1

The function is supposed to iterate through a row of dates which will change the RDate value, whilst the rest of the inputs remain the same. The output, SRENT, should change according to where RDate falls.
Thanks in advance if anyone can tell me where my error lies!

***
Public Function SRENT(EDate As Date, RDate As Date, Rent, Area, SC, VC, VG)




'Day counters
Dim D1 As Single
Dim D2 As Single
Dim D3 As Single
Dim D4 As Single
Dim D5 As Single
Dim D6 As Single
Dim D7 As Single


'Factors
Dim F1 As Single
Dim F2 As Single
Dim F3 As Single
Dim F4 As Single
Dim F5 As Single
Dim F6 As Single
Dim F7 As Single
Dim F8 As Single
Dim F9 As Single
Dim F10 As Single


'Charges
Dim VT As Integer 'total v_cost
VT = Area * VC


Dim ST As Integer 'total s_cost
ST = Area * SC


'Dates
Dim FDate As Date
Dim FDate_1 As Date
Dim FDate_2 As Date
Dim FDate_3 As Date


'Find the dates in the last year of the lease
FDate = DateAdd("yyyy", -1, EDate) 'year before end
FDate_1 = DateAdd("m", 3, FDate) '3 quarters from end
FDate_2 = DateAdd("m", 3, FDate_1) '2 quarters from end
FDate_3 = DateAdd("m", 3, FDate_2) '1 quarter from end


'Days
D1 = FDate_1 - FDate
D2 = FDate_2 - FDate_1
D3 = FDate_3 - FDate_2
D4 = EDate - FDate_3
D5 = 365 - D1
D6 = 365 - D1 - D2
D7 = 365 - D1 - D2 - D3


'Factors
'FOR: proportion of rent
F1 = D5 / 365 '3/4 of last year remaining
F2 = D6 / 365 '1/2 of last year remaining
F3 = D7 / 365 '1/4 of last year remaining


'FOR: proportion of service charge
F4 = D1 / 365 '1/4 of service charge
F5 = (D1 + D2) / 365 '1/2 of service charge
F6 = (D1 + D2 + D3) / 365 '3/4 of service charge


'FOR: proportion of void costs
If GC = 0 Then
F7 = F4
F8 = F5
F9 = F6
F10 = 1

Else
If GC = 1 Then
F7 = 0
F8 = D2 / 365
F9 = (D2 + D3) / 365
F10 = (D2 + D3 + D4) / 365

Else
If GC = 2 Then
F7 = 0
F8 = 0
F9 = D3 / 365
F10 = (D3 + D4) / 365

If GC = 3 Then
F7 = 0
F8 = 0
F9 = 0
F10 = D4 / 365

If GC = 4 Then
F7 = 0
F8 = 0
F9 = 0
F10 = 0

End If
End If
End If
End If
End If


'CALCULATE: net rent in each quarter
'proportion of rent less proportion of service charge less proportion of void costs


If RDate > FDate And RDate < FDate_1 Then


SRENT = (Rent * F1) - (ST * F4) - (VT * F7)

Else
If RDate > FDate_1 And RDate < FDate_2 Then

SRENT = (Rent * F2) - (ST * F5) - (VT * F8)

Else
If RDate > FDate_2 And RDate < FDate_3 Then

SRENT = (Rent * F3) - (ST * F5) - (VT * F9)

Else
If RDate > FDate_3 And RDate < EDate Then
SRENT = -ST - (VT * F10)

Else
SRENT = -ST - VT

End If
End If
End If
End If

End Function

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I tried running your code inside the VB Editor and the first problem it found was that GC was not defined.
The second problem was that this step overflowed:

Dim VT As Integer 'total v_cost
VT = Area * VC

Integers must be in the range -32768 to +32767 and 10,000 * 15 was over the top.

For general use I have taken the policy decision of never using either Integer or Single. I only use Long and Double.

So, if it was me, I would change every Single into Double and every Integer into Long then see what GC should be.

Regards,

 

[ny248]

Hi RickXL

Thanks for the comment - it has helped to resolve the issue. Really should have realised myself!

Appreciate the tip and will use it moving forward.

Best wishes

 

[RickXL]

No problem - I am glad you got it working.

I find the best way to check a User-Defined Function is to write a test program that calls it. Then you can step through its operation.

I used this:

Sub Test()
    Debug.Print SRENT("10/10/18", "23/1/18", 300000, 10000, 10, 15, 1)
End Sub

The result will be displayed in the "Immediate Window". Use the View menu in the VBE to display it or Ctrl + G.