Excel VBA Feature Update Only When Changed

Question

I have a function that I implemented as a VBA module that takes a single cell as a parameter and requests web services to return the result. Since the data source is deleted, this function may take several seconds.

The problem is that some actions, such as deleting any column, seem to cause recalculation of each formula, even if no changes are required. When my sheet has several hundred rows, it may take several minutes for Excel to not respond.

I would like to find a way to find that no changes are required and skip this step. Some things I tried:

• Determine the current value of the cell you want to change, from which I can determine if an update is required. But ActiveCell.Value is empty, and attempts to pass this cell as the second parameter are met with a cyclic reference warning.
• Created a second column to store the old input value, which is then passed to the function and compared with the new value. However, functions cannot update this old value upon successful execution, and attempts to update only with formulas are always satisfied with cyclic help warnings. In addition, it does not seem that the function can return “no change”, so I will need to save and use the old result.

Is there a way to prevent some steps from updating each formula in a worksheet?

Other requirements:

• There may be a way to turn off automatic formula calculation, but I definitely want each line to be updated as soon as the parameter cell is entered. I also do not want to force the user to click a button to update the function.
• I tried to make a custom spreadsheet in Visual Studio, but found that deployment is too difficult to support for my user base. The solution should be fully contained in a macro-enabled book.
• The Excel version for using the worksheet will be in 2007 and 2010.

Answer 1

I'd

• systematically blocks the recount by setting the "File" / "Parameters" / "Formulas / workbook calculation" value to "Manual" and turning off "Recount when saving", and

• create a Private Sub Worksheet_Change(ByVal Target As Range) and determine under what conditions recounting should happen ( Target.Worksheet.Calculate ) - for example, determine the target coordinate (using Target.Column and Target.Row ) and call the request from inside the Worksheet_Change trigger.

• In the end, I would put the recalculation instruction in my own Sub and run the button on the sheet so that the user could - at will - manually recalculate (just in case the F9 key is not known to users)

• You can also use the global logical variable need_Query , which is set by the Worksheet_Change trigger If Target.Row = X And Target.Column = Y (= parameter) and checks the query function (and reset) on Boolean and go into the query only If need_Query Then , otherwise exit without actions. Make sure that need_Query is also set to FALSE at the time the worksheet loads.

• Finally, you can convert the query function to Sub and formulate even more complex conditions in the Worksheet_Change trigger to run it (for example, if some column changes, etc.).