Vba error checking

Question

QUESTION: Thanks in advance for helping me out with this. I have been usingfor years but am very novice at VBA. Here is what I¡¯m doing I have a workbook that allows the user to place a ¡°Start Date¡± and ¡°End Date¡± on a tab called ¡°Home¡±. Once entered, the user selects a button that sets of a macro that inserts the Start Date and End Date into an SQL Statement and connects to my SQL server, runs the SQL statement, Returns the data to a ¡°Data¡± tab then Updates all the Pivot Table¡± I got all of this working fine. The issue that I¡¯m having is with Error Handling. What I want to do is perform 3 checks prior to the DB connection being made. Here are the checks: 1. Is there a Start Date provided (Home:C7) 2. Is there a End Date provided (Home:C8) 3. If 1 and 2 pass then check if the End Date is greater than the Start Date I have been doing a lot of reading on the error handling, but I just can¡¯t seem to grasp how to do something like this

ANSWER: Shawn,

this isn't error handling. It is error prevention.

 Dim r1 as Range, r2 as Range, bError as Boolean
 bError = False
 set r1 = worksheets("Home").Range("C7").value) 
 set r2 = worksheets("Home").Range("C8").value)

 if r1.value = "" or r2.value = "" then bError = True 
 if not bError then 
   if r1 >= r2 then bError = True
 end if

 if bError then exit sub

 ' now your code to do all the things you describe. 

is that what you meant?

---------- FOLLOW-UP ----------

QUESTION: Thanks for the quick response. I think I understand this, but when I run it I¡¯m getting a Run-Time error 424 ¨C Object Required error on the ¡°set r1 = worksheets("Home").Range("C7").value)¡± line. Also how would I have it display a custom message if bError=True then end sub when clicking OK on the message.

Answer

Shawn,

I had about 6 or seven questions stack up while I drove home from work. Guess I got distracted. I wrote the code directly, then decided to use some range reference variables instead and didn't get things totally cleaned up when I merged the two thoughts. Sorry about that.

Sub aa() Dim r1 As Range, r2 As Range, bError As Boolean

bError = False
Set r1 = Worksheets("Home").Range("C7")
Set r2 = Worksheets("Home").Range("C8")

If r1.Value = "" Or r2.Value = "" Then bError = True

If Not bError Then
  If r1 >= r2 Then bError = True
End If

If bError Then
  MsgBox "Either missing values or C7 isn't the earlier date"
  Exit Sub
End If

' now your code to do all the things you describe.

End Sub

If you wanted a more specific message

Sub aa() Dim r1 As Range, r2 As Range, bError As Boolean Dim msg As String bError = False Set r1 = Worksheets("Home").Range("C7") Set r2 = Worksheets("Home").Range("C8")

If r1.Value = "" Or r2.Value = "" Then

  bError = True
  If r1.Value = "" Then msg = vbNewLine & " -Missing Start Date"
  If r2.Value = "" Then msg = msg & vbNewLine & " -Missing End Date"

End If If Not bError Then

 If r1 >= r2 Then
   bError = True
   msg = vbNewLine & " -Start Date is Later than End Date"
 End If

End If

If bError Then

 MsgBox "Error Encountered: " & msg
 Exit Sub

End If

End Sub