Formula with Dynamic Variables

[jooyoon82]

Hello Everyone,

This is my first time on this forum because I've been stuck on this issue for a while now.

Basically I have five variables declared as strings/dates/times:
- tempStart
- tempEnd
- tempCurrency
- tempDealer
- InputDate

And I want this formula to be entered into the activecell (variables bolded):
=+BDH(tempCurrency &" "& tempDealer &" curncy","TRADE",TEXT(InputDate,"mm/dd/yyyy")&" "&TEXT(tempStart,"h:mm:ss AM/PM"),TEXT(InputDate,"mm/dd/yyyy")&""&TEXT(tempEnd,"h:mm:ss AM/PM"),"Dir=v","Dts=s","Sort=A","IntrRw=True","BrkrCodes=S","CondCodes=H")

Im having a hard time getting this into "vba code", can someone please help??

Thanks in advance,
Desperate Excel Rookie

 

[schielrn]

Welcome to the Board!

Are these variables named ranges within the workbook or do you assign them values somewhere in the code?

I would maybe start by recording a macro by entering the formula with cell references or times that would be in place of those variables and then substituting the variables into the recorded code.

Hope that helps.

 

[jooyoon82]

Thanks for the quick reply!

They are actually signed within the code. I've tried to substitute them like you suggested, but some reason once they are defined in the code my formulas do not work.

Is there a formula builder or something in vba?

 

[schielrn]

I'm not sure what the BDH formula is and how it is used and I don't know exactly where quotes are supposed to be and not to be. What does the formula look like without the variables, with hard-coded data?

 

[jooyoon82]

BDH is a bloomberg formula, but it would be the same as if it were an if or a sum formula.

tempCurrency= AUD
tempStart= 4:56:00 AM
tempEnd= 5:01:00 AM
tempDealer= SGXX
InputDate= 9/11/2009

Sample Formula:
'=BDH(AUD SGXX &" "& "Curncy",TRADE,"9/11/2009 9:00:00 AM","9/11/2009 9:00:59 AM","Dir=V","Dts=S","Sort=A","IntrRw=True",,"CondCodes=H")

 

[schielrn]

I just want to make sure that is exactly how it is entered into the cell with all the correct quotes? and I know without the first apostrophe.

 

[jooyoon82]

=BDH(AUD SGXX &" "& "Curncy","TRADE","9/11/2009 9:00:00 AM","9/11/2009 9:00:59 AM","Dir=V","Dts=S","Sort=A","IntrRw=True",,"CondCodes=H")

Yup, with the quotes. Thanks for looking into this!

 

[schielrn]

I have tried working with it for a little, but have had no luck. I will check back tomorrow when I get back into the office. I don't think vba can put the ampersand in the code for the formula as that is used in vba to concatenate.

 

[jooyoon82]

thanks!