Translate this Excel formula into VBA code (function)

Question

This is the formula in Excel that I'm trying to convert to VBA code:

=IFERROR(IF(effDate>curDate,0,IF((curDate-effDate+1)>nDays,nDays*(nSpend/365),((nSpend/365)*(curDate-effDate+1))-IF((curDate-effDate+1)-nDays>0,nSpend/365,0))),0)

For checking working purposes here are the values for each variable:

effDate: 1/1/17 (dimmed as value)
curDate: 3/31/17 (dimmed as value)
nDays: 60
nSpend: 1600

Correct answer: 263

I tried to make it work for two days now and I retyped in about a hundred different way and none of them work. The code may work for one cell but it doesn't calculate correctly for the others. Or it doesn't work at all. Here is one of the examples of what I made earlier (name of the function is APFcst):

If effDate > curDate then
APFcst = 0 
Exit Function 
End if

If (curDate - effDate + 1) > nDays then
APFcst = nDays * (nSpend / 365) 
Else
val1 = (nSpend / 365) * (curDate - effDatea + 1)
end if 

if (curDate - effDate + 1) - nDays > 0 then
val2 = nSpend / 365
else
val2 = 0
end if

APFcst = val1 - val2
Exit Function 
End if

NOTE: everything is dimmed properly, the issue that I'm having is just translating the actual formula so it'll give me the correct answer. Please help! I've been staring at this for so long that I might not see an obvious solution.

Why not try a somewhat direct translation? If() (the worksheet function) corresponds to the VBA function IIF() and IFERROR can be prefixed by Application.WorksheetFunction. and directly used in Excel VBA. — John Coleman
– John Coleman, Commented Jul 4, 2017 at 15:43
APFcst = iff(effDate > curDate, 0, IIf((curDate - effDate + 1) > nDays, nDays * (nSpend / 365), ((nSpend / 365) * (curDate - effDate + 1)) - IIf((curDate - effDate + 1) - nDays > 0, nSpend / 365, 0))) This is what I tried but its giving me an error (sub/function not defined). My coding skills are very beginner so Im not sure how to fix the error. — KateLO
– KateLO, Commented Jul 4, 2017 at 15:55

A.S.H · Accepted Answer · 2017-07-04 15:58:57Z

1

After noticing that the last IF check in your formula is redundant, here's a simplification of your equation:

Function xAPFcst(effDate, curDate, nSpend, nDays) As double
  xAPFcst = IIf(effDate > curDate, 0, (nSpend / 365) * Application.Min(nDays, curDate - effDate + 1))
End function

edited Jul 4, 2017 at 15:58

answered Jul 4, 2017 at 15:57

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

10 Comments

KateLO Over a year ago

What is "min"? It's asking me to define it but i'm not sure what it is. Sorry my coding skills are very beginner! Just learning.

A.S.H Over a year ago

@KateLO Application.Min, using the function available in Excel :)

KateLO Over a year ago

@ A.S.H I have other formulas within the same function and they are currently set as long. Will changing it to double change functionality of everything else or I could also use as long for this?

A.S.H Over a year ago

@KateLO sure you can use long, but you would be rounding the result if the division yields a decimal. It all depends on your project.

KateLO Over a year ago

@ A.S.H sounds good! I just tried to implement it that way but the result turns out to be 0. ( ElseIf freq = 0 Then xAPFcst = IIf(effDate > curDate, 0, (nSpend / 365) * Application.Min(nDays, curDate - effDate + 1)) End If). I cant seem to find an error.

|

Nathan_Sav · Accepted Answer · 2017-07-04 15:42:34Z

1

Something like this, maybe possible to shorten it a touch using IIF's and perhaps, looking at using (nSpend / 365) a bit more effectively, i'll take a look again later. I've not properly tested as yet.

Function formula_test(dtCurrentDate As Date, dtEffectiveDate As Date, nDays As Long, nSpend As Double) As Double

On Error GoTo eHandle

If dtEffectiveDate > dtCurrentDate Then
    formula_test = 0
Else
    If ((dtCurrentDate - dtEffectiveDate) + 1) > nDays Then
        formula_test = nDays * (nSpend / 365)
    Else
        formula_test = ((nSpend / 365) * (dtCurrentDate - dtEffectiveDate + 1))
        If (dtCurrentDate - dtCurrentDate + 1) - nDays > 0 Then
            formula_test = formula_test - nSpend / 365
        End If
    End If
End If

Exit Function

eHandle:
        formula_test = 0

End Function

answered Jul 4, 2017 at 15:42

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

2 Comments

KateLO Over a year ago

Thanks for the answer! My coding skills are very beginner (just heads up). I just tried to implement the code but it gives me a 0 for some reason.

Nathan_Sav Over a year ago

formula_test(dateserial(2017,3,31),dateserial(2017,1,1),60,1600) = 263.013698630137

Collectives™ on Stack Overflow

Translate this Excel formula into VBA code (function)

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related