Preventing Runtime Errors and Custom Error Handling

This Tip & Trick describes a multitude of methods to prevent and handle runtime errors of your VBA code. You will be introduced to the FindColumn and FindRow-methods of table-objects and the Exists-method of Dictionary-objects. Furthermore, the application of variable-info-functions such as IsNumeric is described. This allows you to prevent some runtime errors, others cannot be prevented. The default error handling behavior of VBA then is to cancel the simulation, so that after fixing the root cause, and you need to restart the simulation. This Tip & Trick introduces you to the custom error handling features of VBA (or, more precisely, the WWB-VBA variant that comes with INOSIM) such as the Try-Catch-Finally-Statement and On Error-Instruction.

The demo model (available in the downloads section) was designed to produce runtime errors to showcase how to prevent them. Take your time and “play around” with the model, feel free to delete the “Result”-sheets in the Parameters-Workbook to see what happens. The following code examples are part of an operation parameter control or EndSim procedure, or are functions called from these.

FindColumn and FindRow-methods of table objects

Trying to access a cell of a table object via a non-existent string index will cause a runtime error. Therefore, the methods FindColumn and FindRow are available to check if a certain string index exists. The method will return the column or row number (>0) or -1 in case it does not exist.

In the example below, the name of the operation is used as row index. If it does not exist, the string from the operation’s description should be used instead.

Dim duration As Double, row_index As String
If operation_parameters.FindRow(mop.Name) > 0 Then
    row_index = mop.Name
Else
    row_index = mop.Description
End If
duration = operation_parameters(row_index,"Duration")

Exists-method for dictionary objects

Accessing a non-existent entry of a dictionary object would return a null value, which might result in runtime errors in the following lines. Therefore, the Exists-method is available for dictionary objects in general. Because the list of CustomAttributes of any INOSIM object is a dictionary, the following example uses the Exists-method to check if a certain CustomAttribute has been created for the operation. The sub Initialize_CA_randomly uses a random value to either initialize the CA “Duration Factor” with the random value or not initialize it at all.

'Called sub will decide randomly if the CA "Duration Factor" will be initialized, 
'and if so, assign a random value. If not initialized, CA will not exist.

Initialize_CA_randomly(cop, "Duration Factor")  

Dim duration_factor As Double
If cop.CustomAttributes.Exists("Duration Factor") Then
    duration_factor = cop.CustomAttributes("Duration Factor")
Else
    duration_factor = 1
End If
cop.Duration = duration * duration_factor * 3600

Using Variable-Info Functions

To check if a variable holds a specific data type, there are several variable info functions like IsDate, IsNumeric, and IsDBNull (.NET) or IsNull (COM). (See WinWrapBasic documentation for a full list of available functions, available in the software’s online help or as PDF in our downloads section.)

In this example, users of the model are allowed to enter numbers and the keyword “content” into the operation parameter table below (see screenshot) to specify the transfer amount of the operations Out 1 and Out 2. The code below uses the IsNumeric-function to check if the content of the table cell is a number.

'Check if table entry is numeric for transfer amount (number or content), 
'using the IsNumeric-function

Dim amount_info As Object
amount_info = operation_parameters(row_index,"Amount")
If IsNumeric(amount_info) Then
    cop.Amount = - CDbl(amount_info)
ElseIf amount_info = "content" Or amount_info = "-content" Then
    cop.AmountType = AmountType.amContent
Else
    Console.Error("Specify a number or the keyword >content< in row " & row_index)
End If

Empty table cells are DBNull in WWB.NET (and Null in WWB-COM). Therefore, the IsDBNull-function (or IsNull-function for WWB-COM) can be used to check if a cell is empty. In the following code, the non-null value is written into the unit_name variable, while a warning is issued if the cell is null.

'Check if transfer target in table is not null, using the IsDBNull-function
Dim transfer_target As Unit, unit_name As String
If Not IsDBNull(operation_parameters(row_index,"to/from")) Then
    unit_name = operation_parameters(row_index,"to/from")
Else
    Console.Warning("Enter valid unit name for the transfer target in row "&row_index)
End If

Custom Error Handling

Even with the best intentions and care, it is not always possible to prevent all runtime errors. Sometimes you must rely on external data, sometimes errors emerge from complex scenarios. In many of those cases, it is beneficial to explicitly handle the general error case. The following examples will show you how to do that. The most favorable method, the Try-Catch-Finally-Statement is only available in WWB.NET which was introduced with INOSIM 13. The On Error-Instruction is available in both WWB.NET and WWB-COM, but less powerful.

In general, error handling strategies are only advisable to use, where simple If-Statements are impossible, as the latter are significantly faster in execution speed. (Try it out using our T&T Measuring the Execution Duration of VBA Controls.) In the following sections, three thought through use cases for custom error handling are presented.

Use Case: Checking if an object with a certain name exists

Trying to retrieve an object from a collection by using its string name can result in a runtime error in case of spelling mistakes, etc. Therefore, it is clever to check if the object exists before assigning it.

However, object-classes like units, orders, recipes, etc. currently do not have an Exists-method, unlike dictionaries as shown above. But you can easily create your own function for that purpose, as shown below. All three (alternative!) functions return true if the unit exists, so that the assignment in the following line is safe to be executed without a runtime error.

Afterwards, the IsNothing-function is used to check if an object has been assigned to the transfer_target variable. If so, the unit is used as transfer target, if not, the sink is used.

If Does_Unit_Exist_OnErrorResumeNext(unit_name) Then
    transfer_target = Units(unit_name)
End If
If Does_Unit_Exist_OnErrorGoTo(unit_name) Then
    transfer_target = Units(unit_name)
End If
If Does_Unit_Exist_TryCatchFinally(unit_name) Then
    transfer_target = Units(unit_name)
End If

cop.SourceSinkUnits.Clear
If Not IsNothing(transfer_target) Then
    cop.SourceSinkUnits.Add transfer_target
Else
    cop.SourceSinkUnits.Add Units("Sink")
End If

Try-Catch-Finally-Statement

WWB.NET, as introduced in INOSIM 13, supports the Try-Catch-Finally-Statement, which should be preferred over On Error instructions whenever possible. If an exception occurs in the Try-block, the Catch-block is executed. The optional Finally-block is always executed. It is possible – and recommended – to implement several Catch-blocks to catch different types of exceptions. See the end of this Tip & Trick for an example.

Function Does_Unit_Exist_TryCatchFinally(unit_name As String) As Boolean
    Does_Unit_Exist_TryCatchFinally = True
    Dim u As Unit
    Try
        u = Units(unit_name)
    Catch 
        Does_Unit_Exist_TryCatchFinally = False
    Finally
        '
    End Try
End Function

On Error Instruction

In WWB-COM, you can disable the default error handling with the On Error Resume Next instruction. From now on, all error will be ignored (instead of cancelling the simulation) until the line On Error Goto 0. This means for the example below: If an error occurs while trying to assign the object, the (last) error event is saved in the Err object. By checking the Err.Number property, the function will return false or true. Err.Clear clears the error.

Alternatively, a custom error handler can be created with an On Error GoTo [errhandler] instruction. An example can be found in the model available in the download section. However, the use of GoTo Instructions is generally not advisable. While using GoTo to strictly separate business logic from error handling is worthwhile in smaller functions, it is not feasible in more complex methods.

Function Does_Unit_Exist_OnErrorResumeNext(unit_name As String) As Boolean
    Dim u As Unit
    On Error Resume Next  'sets error handler to continue with next line
        u = Units(unit_name)
        If Err.Number <> 0 Then
            Does_Unit_Exist_OnErrorResumeNext = False
        Else
            Does_Unit_Exist_OnErrorResumeNext = True
        End If
        Err.Clear
    On Error GoTo 0	   'resets error handler to default
End Function

Use Case: Checking for Worksheet-Existence with Try-Catch-Finally

In the EndSim procedure, some results shall be written into a sheet whose name is composed of the experiment name and a string. It would be very inconvenient, if you had forgotten to create that sheet for a simulation run that took a long time to complete and whose results are now lost because of a runtime error.

The following code uses a Try-block to try to assign a sheet with the name sheet_name. If an exception occurs, a sheet with the correct name is created by the Create_Sheet-function (see below) and then assigned. The Finally-block is executed regardless of the occurrence of an exception and calls the sub Write_Results_to_Sheet to write the results.

Dim ResultsSheet1 As Sheet
Dim experiment_name As String, sheet_name As String
experiment_name = ActiveExperiment.Name
sheet_name = ActiveExperiment.Name & " Results 1"

Try 	'exceptions allowed
    ResultsSheet1 = Parameters.Sheets(sheet_name)
Catch 	'executed if exception occured
    Debug.Print Ex.ToString
    Create_Sheet(sheet_name)
    ResultsSheet1 = Parameters.Sheets(sheet_name)
Finally 	'always executed 
    Write_Results_to_Sheet(ResultsSheet1)
End Try

Please note that the Excel-reference must be activated in Tools -> References for the following WWB.NET code to work.

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Sub Create_Sheet(sheet_name)
    Dim objApp As Excel.Application
    Dim objWorkBook As Excel._Workbook
    Dim objSheets As Excel.Sheets
    Dim objSheet As Excel._Worksheet
    objWorkBook = Parameters.ExcelWorkbook
    objSheets = objWorkBook.Worksheets
    objSheet = objSheets.Add
    objSheet.Name = sheet_name
End Sub

Use Case: Controlled “clean-up” in case of runtime errors

INOSIM’s COM-Interface allows you to work with external files like text files or Excel workbooks, see T&T External Excel Workbooks, to read simulation parameters or write simulation results. The connection to external files needs to be closed before the simulation ends. Otherwise, the file connection remains active in the background and prevents you from manually opening the file afterwards.

Let’s assume, you want to write your simulation results into an external Excel workbook. While writing them, before reaching the Close-statement, an error occurs, which causes the simulation to be cancelled. Thus, the file connection remains active and must be closed manually by cancelling the process in the Windows Task Manager. Otherwise, when trying to open the file, you will receive a warning, stating that the document is currently in use. To prevent this, custom error handling can be used for a controlled “clean up”: in case an error occurs while the file is open, it is automatically closed.

If you select “External” for the project’s Custom Attribute “Results Workbook” instead of “Parameters”, the simulation will ask you to select an Excel file for results output.

The following code example also shows how the Try-Catch-Statement can be extended with multiple Catch-statements for different error sources:

If an error occurs while writing*, i.e., while the file is open, an error message is printed to the console and the file is saved and closed.
If no Excel-workbook was selected, an error message is printed.

(* In this example, the error is raised deliberately by calling the Err.Raise method.)

Sub Write_Results_to_External_Workbook
    
    'Write to external excel file (WBB.NET)
    Dim FullFilePath As String
    FullFilePath = GetFilePath("Select File","xlsx",,"Select Excel Workbook",0)

    Dim myWorkbook As Excel._Workbook
    Try
        myWorkbook = OpenExternalWorkbook(FullFilePath)
        Write_Results_to_External_Worksheet(myWorkbook.Worksheets(1))

        myWorkbook.Save    ' Optional
        myWorkbook.Close

    Catch ErrorWriting As System.Exception When Not myWorkbook Is Nothing
Console.Error "An error occured while writing simulation results to the external file."
        myWorkbook.Save    ' Optional
        myWorkbook.Close

    Catch NoFile As System.Exception When myWorkbook Is Nothing
        Console.Error "No Excel-file was selected."

    Finally
        
    End Try

End Sub

Downloads

(for registered INOSIM users)

Example model Custom Error Handling.ixml
PDF printout of this Tip & Trick

More Tips & Tricks

14. July 2018