CODEWITHSUNDEEP
excelvba
intrigued_66
intrigued_66

Reputation: 17258

Excel function as a string, called from VBA

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.

Upvotes: 0

Views: 157

Answers (1)

Charles Williams
Charles Williams

Reputation: 23540

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

Upvotes: 1

Related Questions