Excel VBA and Bloomberg BDH formulas and dynamic cells in an Excel sheet?

[Helen_RMI]

Hi,

I was trying to develop the following Excel VBA code. The main aim is for the code to be able to automatically recognize the dynamic inputs tfor the variables and run the Blooomberg BDH function on the variable values. But it seemed that the "" marks seems to be prohibiting the BDH function to take values from the data variables. After running the code, the formula that has been populated in the excel is "=BDH(tickername, pricetype,dtStart,dtEnd)" instead of =BDH formula that accounts for the variable values.

Can any expert enlighten me? thank you a lot!

Sub testBDH()

Dim dtStart As Date, dtEnd As Date
Dim tickername, pricetype As String

'then apply the values you've worked out. e.g.

dtStart = CDate("6/30/2010")
dtEnd = CDate("6/01/2011")
tickername = "ST SP Equity"
pricetype = "PX_LAST"

'then use the variables as follows

ActiveCell.FormulaR1C1 = "=BDH(tickername, pricetype,dtStart,dtEnd)"

End Sub

 

[gregtx81]

Hi, welcome to the board

It seems like you're on the right track. When placing the formula, your variables shouldn't be w/in the quotes because you want to place the values of the variables; not the text of the variable name itself.

ActiveCell.Formula = "=BDH(" & tickername & "," & pricetype & "," & dtStart & "," & dtEnd & ")"

 

[Helen_RMI]

Hi, thanks a lot! It works!