-1

Apparently, the Application.CalculateFull is not returning the correct results on a worksheet with many formulae, but when I Press F9 it works fine. I had a similar file from Excel 2003 and Calculate command worked just fine for such worksheet with many formula but not with Excel 2010.

I tried to use Sendkeys "%^+{F9}" to emulate F9 and it seems to work, but I see this command doesn't complete until end of VBA and thus my internal code logic picks up wrong values as this isn't executed as soon as its called.

Is there a way I can execute SendKeys before my required code processing?

(I am in a fix and tried a lot of possbilities, seems no way out. The Finance team developer say the sheet is fine as it works with F9 so I should need to make it work with Macro.)

takanuva15
  • 1,286
  • 1
  • 15
  • 27
chandanrs
  • 19
  • 1
  • 1
  • 4
  • you say: "a way I can execute SendKeys before my required code processing". what is that required code? do you mean the automatic calculation of the sheet? – Goos van den Bekerom Oct 15 '14 at 06:45
  • Hi Goos, yes I want to use Sendkeys for F9 as it is correctly calculating the worksheet, whereas Application.calculatefull or Worksheet.usedRange.calculate isn't reflecting correct results for a few formula in the worksheet. The probelm with sendkeys is its only executed at the end of VBA, so If I use this in the code for refresh, the logic to pick up values from certain cells will pick incorrect values ... COuld you suggest any alternate solution to this. I have already banged my brains with it... Pls help... – chandanrs Oct 15 '14 at 07:34
  • I think I know what the problem is let me answer. – Goos van den Bekerom Oct 15 '14 at 07:35

1 Answers1

1

I think you mean that the code only presses the F9 key after the sheet is calculated.
What you could do to prevent that is turn off the automatic calculation before you run the code.

You do this by adding this to the top of your code:

Application.Calculation = xlCalculationManual

If you have other sheets that do need to be calculated automatically you should add this to the bottom of your code:

Application.Calculation = xlCalculationAutomatic
Goos van den Bekerom
  • 1,475
  • 3
  • 20
  • 32
  • It doesn't allow me it says it need reputation 10 for it to allow... Is there anyway I can email you the screenshot and you can help... – chandanrs Oct 15 '14 at 08:12
  • please open the link and check the uploaded document, if you can check. the application.calculation property I had already used correctly but still of now use. – chandanrs Oct 15 '14 at 09:34
  • 1
    I'm not going to download things.. add an image to the question or an imgur link. – Goos van den Bekerom Oct 15 '14 at 09:34
  • No Problem, I will try to solve my issue, but your answer doesn't work... thanks anyways. – chandanrs Oct 16 '14 at 02:29