Using String variables within a formula in VBA

[demian]

Hi,

I'm trying to code the user-defined function calculating the forward rates.
I do not understand why this code doesn't work:

Function ForwardRate(freq As Integer, Date1 As String, Date2 As String, Rate1 As Double, Rate2 As Double) As Double


     ForwardRate = (((1 + Rate2 / freq) ^ (freq * Date2) / (1 + Rate1 / freq) ^ (freq * Date1) ^ (1 / (freq * (Date2 - Date1)))) - 1) * freq


End Function

Should I declare Date1 and 2 as double, which is not in date format, but t=1.5, 2 , 2.5.. etc.
I did, but it is still displaying as N/A

Regards,
Demian

[Bob Phillips]

Works fine for me declared as double, well it returns a decimal answer at least.

[demian]

I am sorry to bother you, I am very new to VBA.
I am still running into difficulties for the following code.

Function ForwardRate(freq As Integer, Date1 As Double, Date2 As Double, Rate1 As Double, Rate2 As Double) As Double


 ForwardRate = (((1 + Rate2 / freq) ^ (freq * Date2) / (1 + Rate1 / freq) ^ (freq * Date1) ^ (1 / (freq * (Date2 - Date1)))) - 1) * freq
 


End Function

It looks like I am missing the very important concept
Could you please correct me?

Regards,
Demian

[mdmackillop]

I cannot get the function to work in your workbook. It works if I change the name (MyRate) or use it in a new workbook. I guess some corruption.

[Bob Phillips]

It's odd, it keeps showing #NAME. If I change the function to FRate, or ForRate, and change the calls on the sheet, it works fine. It just doesn't like ForwardRate.

[demian]

Yes, it's very odd.
I created the new workbook to change the name of user-defined function per your advice.
Now it works well.
Thank you very much for all your wise advice and Sorry for bugging you with corrupted file.

Regards,
Demian

[Paul_Hossler]

Don't use the same name for a module and a function, sub, or other Excel entity


Capture.JPG

[demian]

Got it, thanks again