0

I type in the start date and the end date and execute a code of export from Outlook.

Sub ExportFromOutlook()
Dim dteStart As Date
Dim dteEnd As Date

dteStart = InputBox("What is the start date?", "Export Outlook Calendar")
dteEnd = InputBox("What is the end date?", "Export Outlook Calendar")
Call GetCalData(dteStart, dteEnd)
End Sub

I want when I press the "Cancel button" on any of the Inputboxes to exit the sub, not to get an error in the VBA code to debug it.

Community
  • 1
  • 1
Flaw98
  • 84
  • 1
  • 10

1 Answers1

6

Unqualified InputBox is VBA.InputBox. It returns a String, not a date, and you use StrPtr to determine if Cancel was pressed:

Dim dteStart As Date
Dim dteEnd As Date
Dim t as string

t = InputBox("What is the start date?", "Export Outlook Calendar")
If StrPtr(t) = 0
  Exit Sub
Else
  dteStart = CDate(t)
End If

t = InputBox("What is the end date?", "Export Outlook Calendar")
If StrPtr(t) = 0
  Exit Sub
Else
  dteEnd = CDate(t)
End If

If you switch to Excel's Application.InputBox, which returns Variant, it may become a little bit more straightforward:

Dim dteStart As Date
Dim dteEnd As Date
Dim t as Variant

t = Application.InputBox("What is the start date?", "Export Outlook Calendar", Type:=2)
If t = False
  Exit Sub
Else
  dteStart = CDate(t)
End If

t = Application.InputBox("What is the end date?", "Export Outlook Calendar", Type:=2)
If t = False
  Exit Sub
Else
  dteEnd = CDate(t)
End If
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 1
    Maybe there should be also an error handler for the case that the user entered something invalid. – FunThomas May 20 '19 at 08:52
  • @FunThomas There should be, but there wasn't one originally either. – GSerg May 20 '19 at 08:53
  • 1
    Just wanted to prevent the follow up quesion ;-) – FunThomas May 20 '19 at 08:54
  • [FYI: `StrPtr` may act slightly decieving and is not always the optimal way of handling `InputBox`](https://stackoverflow.com/questions/42015700/what-are-the-benefits-and-risks-of-using-the-strptr-function-in-vba) – Samuel Hulla May 20 '19 at 08:57
  • Thank you @GSerg, this is working, about the follow up question Thomas said, I would like to approach that too at least a bit, if you are up to it. TIA – Flaw98 May 20 '19 at 08:58
  • @Flaw98 [Here](https://stackoverflow.com/a/1046222/8769365) is a great answer on error handling in VBA. – Nacorid May 20 '19 at 08:59
  • @Flaw98 `IsDate` tells you if the string is convertable to date.Use that to direct your logic into asking for a better input. – GSerg May 20 '19 at 09:01
  • As my previous comment suggested, it's better to check for `= vbNullString` or `Len() = 0`. As of now `StrPtr()` only works thanks to the bug in production of `InputBox` by Microsoft – Samuel Hulla May 20 '19 at 09:05
  • @Rawrplus I find the answer you've pointed to wrong and misleading. I have [left a comment](https://stackoverflow.com/questions/42015700/what-are-the-benefits-and-risks-of-using-the-strptr-function-in-vba#comment99055825_42016288). Checking for `= vbNullString` or `Len() = 0` will give you `True` when the user leaves the input field empty and presses OK. The only correct way to detect a cancel with `VBA.InputBox` (as opposed to `Application.InputBox`) is `StrPtr`. – GSerg May 20 '19 at 09:12
  • So, from what I've read around there are different opinions, in my case I have tested what @Gserg suggested me (the second approach - `Application.InputBox`) and it is working just as perfect. If I were to add the IsDate I should add it to the variable T, am I right? – Flaw98 May 20 '19 at 09:35
  • `If StrPtr(t) = 0 And (IsDate(t) = False) Then Exit Sub` – Flaw98 May 20 '19 at 09:50
  • 1
    @Flaw98 `IsDate` will always be false when `StrPtr` is zero. – GSerg May 20 '19 at 10:02