What is the syntax to exit a procedure?

Exit Sub
#example - exits procedure if something error description happens
Private Sub btnAddNew_Click()
errorStr = errorCheck 'checks for errors ie. missing gaps in textfields

If Not (errorStr = "") Then 'if something wrong with wat user entered
     Exit Sub
End If

End Sub

What is XML Validation?

XML Validation is the process of making sure that XML documents/schemas comply by the rules of the language. This process is similar to compiling of a VBA program. Any good XML editor will display error messages whenever XML language rules are not followed.

Although programs like Notepad can be used to create XML documents, these editors do not have validation features available.

What is the syntax for error handling?

On Error GoTo <Error handler name>
‘normal code here
Exit Sub

<Error handler name>:
‘code here
Resume Next

This entire block of code above is meant to go into a single procedure.

On Error GoTo ErrorHandler 'Begins the error handling routine
z = 10 / 0 'Divide by ZERO Error Raises
Exit Sub 'exit so does not continue onto the errorhandler again

ErrorHandler: ' Error-handling routine
If Err.Number <> 0 Then '0 means no error
    MsgBox "Error #" & Err.Number & " : " & Chr(10) & Err.Description 'displays the error
End If
Resume Next 'goes to whatever code is after where the error line was

unnamed (3).jpg

What are the 3 types of errors?

  1. Syntax – like grammar errors, cannot be interpreted by computer
    (ex) forgetting a bracket
  2. Runtime/Exceptions – syntax is correct, but occurs during execution an after interpretation
    (ex)  calling a procedure that doesn’t exist
  3. Logic – most difficult to trace. Nothing wrong with code, something wrong with coder’s brain
    (ex) When you want to get income, you accidently add revenue and expenses, instead of subtracting expenses from revenue

What are the error types?

  • 3 Return without GoSub. Make sure your GoSub was not deleted/excluded
  • 5 Invalid procedure call or argument. Failure in the part of the call. Could be the arg > range of permitted values, the procedure being called is exclusive to a platform (Windows, Mac)
  • 6 Overflow assigned value cannot be handed by range of variable
  • 7 Out of memory. Not physical system memory, but the fixed memory used by Excel/Windows (ex) the area used for graphics or custom formats). try closing unused apps, docs, source files. Your module/procedure is too large so break down into smaller ones. If you are running Windows in standard mode, try restarting with enhanced mode. Free up disk space. Eliminate unnecessary device drives. Reduce number of public variables
  • 9 Subscript out of range. If named item is not found in a collection of objects. For example, if your code refers to Sheets(“Sheet2”), and Sheet2 does not exist. referenced an array element that does not exist, declared an array but didn’t specify number of elements then/later
  • 10 This array is fixed or temporarily locked.
  • 11 Division by zero.
  • 13 Type mismatch.
  • 14 Out of string space.
  • 16 Expression too complex.
  • 17 Can’t perform requested operation.
  • 18 User interrupt occurred. This error occurs if the user interrupts a macro by pressing the Cancel key.
  • 20 Resume without error. This error probably indicates that you forgot the Exit Sub statement before your error handler code.
  • 28 Out of stack space.
  • 35 Sub or Function not defined.
  • 47 Too many Dynamic Link Library (DLL) application clients.
  • 48 Error in loading DLL. continued
  • 49 Bad DLL calling convention.
  • 51 Internal error.
  • 52 Bad filename or number.
  • 53 File not found.
  • 54 Bad file mode.
  • 55 File already open.
  • 57 Device Input/Output (I/O) error.
  • 58 File already exists.
  • 59 Bad record length.
  • 61 Disk full.
  • 62 Input past end of file.
  • 63 Bad record number.
  • 67 Too many files.
  • 68 Device unavailable.
  • 70 Permission denied.
  • 71 Disk not ready.
  • 74 Can’t rename with different drive.
  • 75 Path/File access error.
  • 76 Path not found.
  • 91 Object variable or With block variable not set. This error occurs if you don’t use Set at the beginning of a statement that creates an object variable. Or, it occurs if you refer to worksheet object (such as ActiveCell) when a chart sheet is active.
  • 92 For loop not initialized.
  • 93 Invalid pattern string.
  • 94 Invalid use of Null.
  • 96 Unable to sink events of object because the object is already firing events to the maximum number of event receivers that it supports.
  • 97 Cannot call friend function on object that is not an instance of defining class.
  • 98 A property or method call can’t include a reference to a private object, either as an argument or as a return value.
  • 321 Invalid file format.
  • 322 Can’t create necessary temporary file.
  • 325 Invalid format in resource file.
  • 380 Invalid property value.
  • 381 Invalid property array index.
  • 382 Set not supported at runtime.
  • 383 Set not supported (read-only property).
  • 385 Need property array index.
  • 387 Set not permitted.
  • 393 Get not supported at runtime.
  • 394 Get not supported (write-only property).
  • 422 Property not found.
  • 423 Property or method not found.
  • 424 Object required. This error occurs if text preceding a dot is not recognized as an object.
  • 429 ActiveX component can’t create object (might be a registration problem with a library that you’ve referenced).
  • 430 Class doesn’t support Automation or doesn’t support expected interface.
  • 432 Filename or class name not found during Automation operation.
  • 438 Object doesn’t support this property or method.
  • 440 Automation error.
  • 442 Connection to type library or object library for remote process has been lost.
  • 443 Automation object doesn’t have a default value.
  • 445 Object doesn’t support this action.
  • 446 Object doesn’t support named arguments.
  • 447 Object doesn’t support current locale setting.
  • 448 Named argument not found.
  • 449 Argument not optional.
  • 450 Wrong number of arguments or invalid property assignment.
  • 451 Property Let procedure not defined, and Property Get procedure did not return an object.
  • 452 Invalid ordinal.
  • 453 Specified DLL function not found.
  • 454 Code resource not found.
  • 455 Code resource lock error.
  • 457 Key is already associated with an element of this collection.
  • 458 Variable uses an Automation type not supported in Visual Basic.
  • 459 Object or class doesn’t support the set of events.
  • 460 Invalid Clipboard format.
  • 461 Method or data member not found.
  • 462 Remote server machine doesn’t exist or is unavailable.
  • 463 Class not registered on local machine. continued
  • 481 Invalid picture.
  • 482 Printer error.
  • 735 Can’t save file to TEMP.
  • 744 Search text not found.
  • 746 Replacements too long.
  • 1004 Application-defined or object-defined error. This is a very common catch-all error message. This error occurs when an error doesn’t correspond to an error defined by VBA. In other words, the error is defined by Excel (or some other object) and is propagated back to VBA.