Why am I getting error unwrapping Optional if it has a value?

Vba error checking

• 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