PDA

View Full Version : [SOLVED] Using String variables within a formula in VBA



demian
09-29-2017, 10:19 AM
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
09-29-2017, 10:41 AM
Works fine for me declared as double, well it returns a decimal answer at least.

demian
09-29-2017, 11:53 AM
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: pray2:
Could you please correct me?

Regards,
Demian

mdmackillop
09-29-2017, 12:19 PM
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
09-29-2017, 12:29 PM
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
09-29-2017, 12:48 PM
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. :hi:
Thank you very much for all your wise advice and Sorry for bugging you with corrupted file.

Regards,
Demian

Paul_Hossler
09-29-2017, 02:29 PM
Don't use the same name for a module and a function, sub, or other Excel entity


20529

demian
09-29-2017, 05:48 PM
Got it, thanks again:)