Convert this Excel formula into VBA code

Question

=IF((effDate)-curDate>0,0,IF(curDate-(effDate)+1>nDays,0,nSpend/4))+IF((effDate+365/4*1)-curDate>0,0,IF(curDate-(effDate+365/$4*1)+1>nDays,0,nSpend/4))+IF((effDate+365/4*2)-curDate>0,0,IF(curDate-(effDate+365/4*2)+1>nDays,0,nSpend/4))+IF((effDate+365/4*3)-curDate>0,0,IF(curDate-(effDate+365/4*3)+1>nDays,0,nSpend/4))

effDate: 1/1/2017 (as value)
curDate: 1/31/2017 (as value)
nSpend: 1600
nDays: 60
Correct answer: 400

Above is a long formula I'm trying to covert to VBA code. The way I've been trying to do it is long and I tried breaking it up into smaller functions, but it's not giving me the right answer. My VBA skills are very beginner so I'm not sure what else to try. I keep getting the wrong answer or no answer at all.

This is what I've been trying:

If effdate - curdate > 0 Then
val1 = 0
Exit Function
End If

If curdate - effDate + 1 > nDays Then
val1 = 0
Else
val1 = nSpend / 4

End If

If (effDate + (365 / 4)) - curdate > 0 Then
val2 = 0
Exit Function
End If

If curdate - (effDate + (365 / 4)) + 1 > nDays Then
val2 = 0
Else
val2 = nSpend / 4
End If

If effDate + (365 / (4 * 2)) - curdate > 0 Then
val3 = 0
Exit Function
End If

If curdate - (effDate + (365 / (4 * 2))) + 1 > nDays Then
val3 = 0
Else
val3 = nSpend / 4
End If

If effDate + (365 / (4 * 3)) - curdate > 0 Then
val4 = 0
Exit Function
End If

If curdate - (effDate + (365 / (4 * 3))) + 1 > nDays Then
val4 = 0
Else
val4 = nSpend / 4
End If
End If

APFcst = val1 + val2 + val3 + val4


End Function

I dimmed everything correctly, it's the actual conversion that I have problems with. I would appreciate the help! This is also a huge learning stretch for me since I just started learning VBA coding. Thank you!

Siddharth Rout · Accepted Answer · 2017-07-04 18:58:55Z

2

If you know the formula then you can use Evaluate() to achieve what you want.

Sub Sample()
    Dim f1, f2, f3, f4

    f1 = "=IF((effDate)-curDate>0,0,IF(curDate-(effDate)+1>nDays,0,nSpend/4))"
    f2 = "=IF((effDate+365/4*1)-curDate>0,0,IF(curDate-(effDate+365/4*1)+1>nDays,0,nSpend/4))"
    f3 = "=IF((effDate+365/4*2)-curDate>0,0,IF(curDate-(effDate+365/4*2)+1>nDays,0,nSpend/4))"
    f4 = "=IF((effDate+365/4*3)-curDate>0,0,IF(curDate-(effDate+365/4*3)+1>nDays,0,nSpend/4))"

    '~~> Change Sheet1 to the relevant sheet code name
    Debug.Print Sheet1.Evaluate(f1) + Sheet1.Evaluate(f2) + Sheet1.Evaluate(f3) + Sheet1.Evaluate(f4)
End Sub

answered Jul 4, 2017 at 18:58

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Z. Over a year ago

This is interesting. I did not know this could be done. However, I don't think the OP wants the formula at all. Just wants pure VBA to produce the same results.

Doug Coats · Accepted Answer · 2017-07-04 16:53:45Z

0

 DateAdd ( interval, number, date )

You should investigate this and apply it towards what youre trying to do :)

https://www.techonthenet.com/excel/formulas/dateadd.php

answered Jul 4, 2017 at 16:53

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Comments

jamheadart · Accepted Answer · 2017-07-04 18:08:01Z

0

I was intrigued by this to see why it wasn't working, I've rewrote the if > else > else logic to an if or > else but it achieves the same thing. Like Doug Coats' answer says, you can use DateAdd to work with subtraction/adding dates together. I've just converted the date strings to double:

Private Sub CommandButton1_Click()
Dim x As Date: Dim y As Date
Dim effDate As Double
Dim curDate As Double
Dim nSpend As Double
Dim nDays As Double

x = "1/1/17"
y = "31/1/17"
effDate = CDbl(x)
curDate = CDbl(y)
nSpend = 1600
nDays = 60

If (effDate - curDate > 0) Or (curDate - effDate + 1 > nDays) Then
    val1 = 0
Else
    val1 = nSpend / 4
End If

If ((effDate + (365 / 4)) - curDate > 0) Or (curDate - (effDate + (365 / 4)) + 1 > nDays) Then
    val2 = 0
Else
    val2 = nSpend / 4
End If

If (effDate + (365 / (4 * 2)) - curDate > 0) Or (curDate - (effDate + (365 / (4 * 2))) + 1 > nDays) Then
    val3 = 0
Else
    val3 = nSpend / 4
End If

If (effDate + (365 / (4 * 3)) - curDate > 0) Or (curDate - (effDate + (365 / (4 * 3))) + 1 > nDays) Then
    val4 = 0
Else
    val4 = nSpend / 4
End If

MsgBox (val1 + val2 + val3 + val4)

End Sub

answered Jul 4, 2017 at 18:08

jamheadart

5,3737 gold badges37 silver badges87 bronze badges

1 Comment

KateLO Over a year ago

thanks for this help! I tried to apply it but its giving me inconsistent/incorrect answers as I try to drag the functions to other cells for some reason

Collectives™ on Stack Overflow

Convert this Excel formula into VBA code

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related