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
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