1

I am trying to write a VBA routine that substitutes certain values into cell "B16" (going off a list on a separate workbook), recalculates the entire workbook after each substitution and records the value of the cell "I31" (post recalculation) in another workbook.

The behaviour that I observe is a lot different from what I expect. Suppose that the value of "I31" is 0 before I run the routine and that it needs to be equal to 1 after the first iteration and equal to 2 after the second iteration. When I run the routine, both the recorded values are 0 (instead of 1 and 2) and the value of "I31" itself is 2. The latter makes sense. It seems like Excel is not refreshing the cell values before copying them into other cells. Maybe it is not waiting for 'SendKeys "{F9}"' to terminate before copying the cell values. So I forced the routine to pause for one minute after each iteration (the recalculation should take about ten seconds) and this time both the recorded values are 0 again and "I31" itself is also 0. This is even worse.

On a related note Application.Calculate (or its variants like Worksheet.Calculate) has no effect whatsoever on the worksheet. Nothing is being recalculated when I use those instead of SendKeys "{F9}". This occurs even if the only line in the routine is Application.Calculate. I have no clue why this is happening.

My routine is below. Any help appreciated. Thanks.

Sub createData()

Dim x As Integer

For x = 1 To 2

    Range("B16").value = Worksheets("Types").Cells(x, 1)

    'Application.Calculate

    SendKeys "{F9}"

    'Application.Wait Now() + TimeValue("00:01:00")

    Worksheets("Results").Cells(x, 1).value = Range("I31").value

Next

End Sub
Improve this question
24
  • 1
    @Miqi180 The question states "Application.Calculate has no effect whatsoever on the worksheet" Commented Jun 28, 2018 at 18:50
  • 1
    My guess is that your use of "Range("B16") without specifying which worksheet is the problem (i.e., Excel is using the B16 value from whichever sheet was last active). But, it would be a lot easier to help you if you'd include sample data, either through a screenshot, linked workbook, or just type it out. I'm not clear on how many total worksheets are involved.Also, you say that B16 gets its value "going off a list on a separate workbook"). I don't see a second workbook referenced in your code. Commented Jun 28, 2018 at 18:59
  • 1
    Application.CalculateFull may do what you want Commented Jun 28, 2018 at 19:05
  • 1
    Excel's calculation mode is xlCalculationAutomatic, until some code (or the user) toggles it otherwise (e.g. xlCalculationManual) - AFAIK calculation being manual is the only way Excel won't automatically recalculate when cells change, barring non-volatile user-defined functions. Research the terms if you're unsure what they are, but if you don't have VBA functions invoked by cells, you don't have UDF's to worry about. Commented Jun 28, 2018 at 19:05
  • 1
    Also you should be aware that Range("...") is implicitly referring to whatever worksheet is currently active, while Worksheets("Results").Cells(...) is explicitly qualifying the range with a specific worksheet object: assuming that's written in a standard module and not a worksheet's code-behind, your code will behave differently depending on what worksheet is active when it's invoked. Commented Jun 28, 2018 at 19:07

1 Answer 1

Reset to default
1

Implicit ActiveSheet references are the main problem, I think.

If the 3 sheets you're working with exist at compile-time in ThisWorkbook (i.e. they're not created at run-time, and you can see them in the VBE's Project Explorer), then locate them in the Project Explorer (Ctrl+R), then bring up the Properties toolwindow (F4) and change their (Name) property (default would be e.g. Sheet1).

So you have a TypesSheet with a name "Types" - "Types" being the name on the sheet's "tab", and TypesSheet is an identifier you can use anywhere in the code to refer to that sheet.

Do the same for the other two, so you have TypesSheet, ResultsSheet, and MainSheet.

And now you can be both explicit and efficient:

TypesSheet.Range("B16").Value = TypesSheet.Cells(x, 1).Value
ResultsSheet.Cells(x, 1).Value = TypesSheet.Range("I31").Value

Not sure which specific sheets you meant to refer to, but this is unambiguous code that doesn't depend on what sheet is active when the macro starts running.

If the sheets are created at run-time (i.e. added to the workbook by some VBA code), then you can't use their code name and must dereference them from the Worksheets collection of a Workbook object.

If the sheets exist in ThisWorkbook (i.e. the same file where that VBA code is written in), then you can do this:

Dim typesSheet As Worksheet
Set typesSheet = ThisWorkbook.Worksheets("Types")

And so on. Note that retrieving worksheet references by sheet name like this, makes your code vulnerable to a user that would rename the tabs (assuming an unprotected workbook structure). That's why you should use the code names whenever possible.

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

5 Comments

Thank you very much. I will try your suggestions as well as others tomorrow and give an update.
I am afraid the macro behaves the same as before with your suggestions. Thanks anyway.
Are you assuming an automatic calculation mode by any chance? If calculation is manual, Excel won't calculate. Other than that, AFAICT the problem would not be in the code you supplied then.
I played with the calculation mode setting both via Excel menu and VBA. No changes occur. Pressing F9 in Excel results in some macros containing database queries being called. I don't know if this is some special case that requires a different treatment.
If you press F9 and VBA code runs, you're handling worksheet.calculate event, and/or have UDFs. If these functions involve the cells that aren't calculating, then make them volatile.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.