How can I force Excel to calculate a cell in VBA?

[cr731]

For starters, I'm using Excel 2007.

I have an Excel add-in that retrieves data from another program. However, the cells do not automatically recalculate as most cells would in Excel (such as by clicking "Calculate Now"). The only two ways I can see to make the cells recalculate are to either use the add-in's "Refresh" feature or else to click on the cell, hit F2, and then Enter.

So my question: is there a way in VBA that I can make the cell "Refresh?" Basically I would need it to effectively be the same thing as entering the cell, hitting F2 and Enter. But I'm looking for a way other than Sendkeys, if possible. I already tried:

Sheet1.Range("E5").Calculate

But that does not seem to do the job. And as far as I know there is not a way to control add-ins through VBA.

Is there a way to accomplish this? Thanks!

 

[Juan Pablo González]

You could probably do

Sheet1.Range("E5").Formula = Sheet1.Range("E5").Formula

Another alternative would be

Sheet1.Range("E5").Dirty

to tell Excel to calculate it in the next calculation.

There are ways to control addins. Easiest would be to use Application.Run, but you'll need to find the correct method to call from the addin. Highlight the addin in the Project Explorer and press F2 to go to the Object browser, to list all the public methods that it exposes.

 

[C Moore]

I realize this is old, but an alternative method is to delete an entire row you're not using. I assume it would work for columns as well. (i.e. Rows(300).EntireRow.Delete)