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
Advertisements

Tips for debugging when working with workbooks

When working with workbooks, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the workbook you are referencing currently closed?
  • Did you misspell the name of the workbook?
  • Are you trying to reference a new workbook that has not been explicitly saved for the first time?
  • Are you running two instances of Excel?
  • Did you pass an index that is greater than the number of workbooks open?

Tips for debugging when working with worksheets

When working with worksheets, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the name of the Worksheets spelled correctly?
  • Has the name of the sheet changed and not been updated in your code?
  • Has the worksheet been deleted?
  • Has the index you used to reference the sheet too large?
  • If working with multiple workbooks, did you reference the wrong workbook?

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 to return the row of a search value?

<integer variable> = Application.WorksheetFunction.Match(<search value>, Range("<searched range index>"), <0 for exact match or 1 for approximate>)

Helpful tip: also use error handlers to catch 1004 errors (that arise from not finding a match for your search value)

'example
On Error GoTo ErrorHandler
foundRow = Application.WorksheetFunction.Match(“James Robert”, Range(“A1:A100”), 0)
 
ErrorHandler
 MsgBox “Name not found”
Resume Next

'example - find a name on a sheet, then delete the row containing name
On Error Resume Next
foundRow = Application.WorksheetFunction.Match("JamesRobert", Range("B2:B30"),0)
Rows(foundRow).Delete

 

 

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.

‘example
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 is the syntax to use a Go Sub Return statement?

Go Sub <procedure name>
     <your code here>
Exit Sub

<procedure name>:
     <your code here>
Return
‘example
usersName = InputBox(“Please enter your name.”) ‘1. user must type in his/her name
If usersName=”” Then GoSub NoName ‘2. if user doesn’t give a name, computer goes to sub procedure NoName
Exit Sub ‘5. Put exit sub so it will not run NoName unnecessarily

NoName: ‘3. This procedure is run only if user leaves inputbox blank
     MsgBox (“You did not enter any name”)
Return ‘4. returns to line procedure