What is your method of choice for programmatically triggering the value of a cell to recalculate based on an equation in the design sheet? I feel like I must be missing something obvious.
We have a database in which we’re using procedure-style programming (we run into this same issue with our other databases in spreadsheet mode, too). So as to not get bogged down in details, I’m going to modify an example from the manual. I have the fields “total”, “A”, “B”, and “C”. A, B, and C get updated using
formulafill, grabbing numbers from other databases. The design sheet equation for “total” is “=A+B+C”. Three methods I’ve looked into:
A) After changing the values in A, B, and/or C, I want to trigger “total” to update. Our current method is to run a little macro:
Copy/paste is the only way we could figure out to trigger the value to update. I don’t love this solution, mainly because it uses the clipboard, and I have to remember not to copy and paste anything in other applications while I’m waiting for the macro to run. This macro takes 5 minutes to run through 1133 records in a shared database.
B) I’ve tried
formulafilling “total” with “A+B+C”, but don’t really like this solution because it means the equation is in the design sheet as well as in a macro, and I have to remember to update it in both places when it changes. Takes 4.5 minutes to run.
C) Automatic Calculations Wizard. Doesn’t work for procedure mode formulas, also I don’t think it could be triggered by a macro, so it wouldn’t be ideal even if we could switch modes, which we can’t, because procedure mode is needed for other fields.
I’m seeking other options that don’t use the clipboard. Bonus points if there’s a speed improvement.