Problem converting a very simple nested IF function to a UDF

Hello Excel Gurus,

I haven’t been here for a while (my loss), and I am again in need of enlightenment.

I have the following nested IF function (below) that I’m trying to convert to a UDF, because the actual cell references to be used are long INDEX functions that make the IF function cumbersome to read and follow.

=IF(D$6=0,"NA",IF(D$6<0,((D$5/D$6-1)*-1),D$5/D$6-1))

The function is simply to calculate change, while avoiding #DIV/0! errors (returning an “NA” instead), and reversing the sign when the denominator is a negative number.

The VBA code I’ve been attempting to use is below, where the numerator is News (D$5 – above) and the denominator is Prev (D$6 – above):

Function CHANGE(News As Double, Prev As Double)

If Prev = 0 Then
CHANGE = "NA"

  Else
  If Prev < 0 Then
  CHANGE = ((News / Prev) - 1) * -1
  End If
  
CHANGE = (News / Prev - 1)

End If
End Function

The problem is that the code is skipping the "Else" or not recognizing the "If Prev < 0 Then" statement so when Prev is negative the resulting evaluation does not get multiplied by * -1, as it otherwise would if I were using the straight IF function from above.

I’ve scoured the forum and Internet for a clue as to what I’m doing wrong, but can’t find the solution. The worst part is that code is so simple the answer must be right below my nose.

Any help is greatly appreciated! Thanks,

Gabriel G

Hi protonLeah,

Thanks for your solution.

The way you describe the problem with my original code makes me think that I was conceptualizing the code in terms of how the straight IF function works, but not how IF statements work in VBA.

Your solution is much simpler, and makes much better use of VBA programming. I have seen Case before, but didn't really know how to apply it in this instance.

Thanks again for you help!

Regards,

GabrielG