Using excel vba, how to write a formula in a cell

0
votes

In Excel, A1 is equal to =B1/ BDP(C11&”Corp”, “ds036”) Which BDP(Corp, ds036) is a function and parameters from Bloomberg.

How to use Excel VBA for this formula?

I was trying different ways in VBA to solve my point. One of them is like the line below,

Cells(1,1)=“B1/ BDP(C11&”Corp”, “ds036”)”

An other way I tried, to simplify,

For i=1 to10
    Cells(i,1)=“Cells(i,2)/ BDP(cells(i,3)&”Corp”, “ds036”)”
Next

Also, if it can access directly to BDP function. That will be perfect!

2
excelvbaformula
Are you trying to write the formula into the cell or access the BDP function from VBA to write the response into A1? – Patrick O'Connor
If it can access directly to BDP function. That will be perfect! – Weber Chen
fix those smartquotes, they'll cause problem in Excel – phuclv
For direct access to the BDP function, see this existing answer: link. You can use Application.Evaluate to execute your BDP function, or work with the Bloomberg add-in to directly call the functions natively in VBA. If you use Application.Evaluate, be sure to fix your issues with quotation marks as identified by other commenters. – Patrick O'Connor
I’m still confused about the second way I used. How to apply cells(i,2) in a looping of my question instead of using B1 and C11? – Weber Chen

2 Answers

2
votes

try:

Cells(1,1).Formula = "=B1/ BDP(C11&""Corp"", ""ds036"")"

Note:

  1. I used a different flavor of double quote
  2. I doubled-up on the double quotes
0
votes

Does this do what you want?

Range("A1:A10").Formula = "=B1/BDP(C3&""Corp"",""ds036"")"

If you assign a formula to a multi-cell range, I think Excel will automatically adjust relative cell references for subsequent rows/columns - similar to CTRL+D or dragging down.

This means you don't need to loop through each row and re-construct the formula string for each loop iteration.