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

Question

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!

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
– Patrick O'Connor, Commented Jan 3, 2018 at 3:25
If it can access directly to BDP function. That will be perfect! — Weber Chen
– Weber Chen, Commented Jan 3, 2018 at 3:39
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
– Patrick O'Connor, Commented Jan 3, 2018 at 4:01
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
– Weber Chen, Commented Jan 3, 2018 at 4:13

Gary's Student · Accepted Answer · 2018-01-03 03:20:30Z

2

try:

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

Note:

I used a different flavor of double quote
I doubled-up on the double quotes

answered Jan 3, 2018 at 3:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Weber Chen Over a year ago

Thank you! But one more question, How to apply cells(i,2) in a looping of my question instead of using B1 and C11?

chillin · Accepted Answer · 2018-01-03 06:42:43Z

0

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.

answered Jan 3, 2018 at 6:42

chillin

4,4861 gold badge11 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related