Manual vs Automatic Calculations - Safety at Risk - Engineering Help

[cggb]

At my work, we populate an Excel form. The form does a few calculations that are the basis for programming some pumps. If the pumps are programmed wrong, they can burst the piping which is a safety issue.

Is there any way to absolutely (with 100% certainty) to force Excel to always be in automatic calculation mode? Or at least to be in automatic calculation mode when we print the form? We often have multiple sheets open, and some of them are in manual calculation mode. This can lead to the calculation mode inadvertently getting turned off in our form. Because safety is at risk, I need a way to ensure this sheet never gets switched. I know know how to write a VBA module to ensure the sheet opens in automatic mode, but I don't know how to prevent this from happening while the sheet is open.

At present, our best solution to this problem is to verify the calculations by hand.


Thank you in advance.

[Bernie Deitrick]

Try adding this event code to the Thisworkbook object's Codemodule:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Application.Calculation = xlCalculationManual Then
        MsgBox "In manual, but I will now calculate"
        Application.CalculateFull
    End If
    If Application.Calculation = xlCalculationAutomatic Then
        MsgBox "In Auto mode, so all OK"
    End If
End Sub

Once you have finished testing to ensure it works, you can try simplifying to just

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Application.CalculateFull
End Sub

Or use a macro to print

Sub PrintMacro()
Application.CalculateFull
Sheets("Pump Control").PrintOut
End Sub

[cggb]

Bernie, these are helpful. I should have mentioned one other detail. We get the vendor of the pump to populate the form, and then PDF it and send it back to us. How do I know if they have the macros enabled when they PDF it and send it back to me? I like the last macro you mentioned. How do I know they printed the form to PDF using this macro? I could have the code put something in a cell indicating the sheet was printed using the macro with calculations enabled. But if they save that sheet, and use it for populating information for other pumps, then there is no guarantee that they are then printing with the macro.

[Bernie Deitrick]

That is a more complicated situation - it would require that the vendor have macros enabled when they open the workbook, and keep them enabled the whole time. At some point, it just becomes impossible to ensure that they have done everything correctly, but one way would be to produce the sheet that they must print to the PDF by using a macro. And then you need to ensure that the sheet was actually produced by the macro and not just overwritten and then re-printed. I think that one way would be to create a new sheet by copying the output form, then inserting a new row into the print area and - using code - insert a date time stamp and a hashed value that is generated from the date using the macro. Lock the project so that they cannot view it - not great, but better than nothing - and then check that the hash value matches what you would expect given the date time stamp.

Or change how you work - don't accept a PDF, but require the originating workbook, and then send the vendor a PDF that they confirm as correct before proceeding.