I have a workbook where there is a formula that searches for "Grand Total", as would appear at the end of a range when you've applied a subtotal, and then it takes a number from one column and subtracts numbers from the same row in several other columns, with the hope being that in the end the result is a sum of 0. Basically we are ensuring that we have picked up all the pieces of a total. Previously the formula was an unruly combination of 9 VLOOKUP formulas. I wrote a User Defined Function to make the formula a little easier to understand and much shorter. The UDF works great normally.
When I automated the clearing out of the sheet's data, replacing it with new data and adding a subtotal, I noticed when stepping through the code that it was constantly trying to recalculate my UDF, which made the code run slow. I then added code to switch the calculation method to manual during the code run and then switch it back to automatic at the end. Now I find that the code still tries to recalculate the UDF after the subtotal is added, and although the formula appears to return the correct amount, I get an object defined error when the UDF is being calculated.
Alternatives:
I could remove the formulas at the beginning of the code and then add them back near the end.
I could add an apostrophe to the formulas to deactivate them and then remove it later.
I could figure out how to keep the function from trying to run until I'm finished making changes or figure out why it is breaking altogether.
I would post the UDF, but I really don't think that is the issue. It seems that adding the subtotal forces a recalculation to occur, but I'm not sure why it is failing because the formula returns the correct result, the only error is in VBA. The macro code has so much going on, I'm not sure how to easily pull out only those pieces that would be beneficial to review.
Does anyone know of any tricks to quickly disable a few formulas until a macro is finished running?
When I automated the clearing out of the sheet's data, replacing it with new data and adding a subtotal, I noticed when stepping through the code that it was constantly trying to recalculate my UDF, which made the code run slow. I then added code to switch the calculation method to manual during the code run and then switch it back to automatic at the end. Now I find that the code still tries to recalculate the UDF after the subtotal is added, and although the formula appears to return the correct amount, I get an object defined error when the UDF is being calculated.
Alternatives:
I could remove the formulas at the beginning of the code and then add them back near the end.
I could add an apostrophe to the formulas to deactivate them and then remove it later.
I could figure out how to keep the function from trying to run until I'm finished making changes or figure out why it is breaking altogether.
I would post the UDF, but I really don't think that is the issue. It seems that adding the subtotal forces a recalculation to occur, but I'm not sure why it is failing because the formula returns the correct result, the only error is in VBA. The macro code has so much going on, I'm not sure how to easily pull out only those pieces that would be beneficial to review.
Does anyone know of any tricks to quickly disable a few formulas until a macro is finished running?
