Excel function as a string, called from VBA

Question

If i enter the following excel function in a cell and press enter, it works:

"=T_DisplayReport(A1)"

but if I write the following in VBA:

Sheets("Sheet1").Cells(1, 1).Formula = "=T_LoadReport(""X"",""Y"",true)"
Sheets("Sheet1").Cells(1, 1).Calculate

Sheets("Sheet1").Cells(2, 1).Formula = "=T_DisplayReport(A1)"
Sheets("Sheet1").Cells(2, 1).Calculate

it doesnt work. How can I hardcode the cell A1 into the above VBA so that it is recognised and the function works?

EDIT: A1 contains another function which fetches data and saves it to the local cache. T_DisplayReport() grabs cell A1 to then display this data.

Hmmm, I just tested it and it works... Can you show us what the function does? — Siddharth Rout
– Siddharth Rout, Commented Jul 2, 2012 at 16:10
I cant really show, as its a custom addin function, but basically i have one function which goes and gets a load of data and puts it into the local cache. This second function then retrieves the data from the local cache and pastes it onto the sheet. I didnt write these so cant really offer code. — intrigued_66
– intrigued_66, Commented Jul 2, 2012 at 16:13
Hmmm... Can you test this for me... What does this give you? MsgBox Application.Evaluate("=DisplayReport(A1)") — Siddharth Rout
– Siddharth Rout, Commented Jul 2, 2012 at 16:15
I had to remove the MsgBox bit, but it still didnt evaluate cell A1 — intrigued_66
– intrigued_66, Commented Jul 2, 2012 at 16:18
If =T_DisplayReport(A1) works in the Excel cell and MsgBox Application.Evaluate("=T_DisplayReport(A1)") doesn't then there is not much I can suggest without seeing the function... — Siddharth Rout
– Siddharth Rout, Commented Jul 2, 2012 at 16:34

Charles Williams · Accepted Answer · 2012-07-02 17:43:24Z

1

Depending on which version of Excel you are using, your Range.Calculate statements may not correctly handle asynchronous function dependencies.
Try removing your first Range.Calculate and replace your last range.calculate with Sheets("Sheet1").Calculate or Application.Calculate

answered Jul 2, 2012 at 17:43

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Excel function as a string, called from VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related