I'm not sure whether the name of your procedure "Year to Year Sales" matches
what it does.  If the idea is to create a recordset based on a single year,
then perhaps you only need one input parameter - the year.  You also need to
be careful with "between" when using dates as the order date may also
contain a time portion.  I would also avoid putting spaces in the names of
stored procedures as it means you have to use the silly brackets.

I don't know whether I would bother to raise an error in the stored
procedure if you are going to wrap it in a vba function.  You could use the
vba coding to check whether you have a start and end date.  If you are going
to start raising errors, then perhaps you should think about getting your
stored procedure to provide a return value to show if it was successful.
You then have to get your vba coding to get the value of this output
parameter.  I have not done this here, but this gives you the general idea:

Private Sub cmdTest_Click()

   On Error GoTo Err_Handler

   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim prm As ADODB.Parameter
   Dim rst As ADODB.Recordset
   Dim strConn As String
   Dim dteStart As Date
   Dim dteEnd As Date

   dteStart = DateSerial(2006, 1, 1)

   dteEnd = DateSerial(2006, 2, 1)

   strConn = "Provider=sqloledb;" & _
             "Data Source=MyServer;" & _
             "Initial Catalog=MyDatabase;" & _
             "Integrated Security=SSPI"

   Set cnn = New ADODB.Connection

   cnn.Open strConn

   Set cmd = New ADODB.Command

   cmd.ActiveConnection = cnn

   cmd.CommandText = "[Year to Year Sales]"

   cmd.CommandType = adCmdStoredProc

   Set prm = cmd.CreateParameter("@BeginningDate", adDate, adParamInput, ,
dteStart)
   cmd.Parameters.Append prm

   Set prm = cmd.CreateParameter("@EndingDate", adDate, adParamInput, ,
dteEnd)
   cmd.Parameters.Append prm

   Set rst = cmd.Execute

   While Not rst.EOF
       Debug.Print rst.Fields("OrderID")
       rst.MoveNext
   Wend

   MsgBox "Done", vbInformation

Exit_Handler:

   If Not rst Is Nothing Then
       If rst.State <> adStateClosed Then
           rst.Close
       End If
       Set rst = Nothing
   End If

   Set prm = Nothing

   Set cmd = Nothing

   If Not cnn Is Nothing Then
       If cnn.State <> adStateClosed Then
           cnn.Close
       End If
       Set cnn = Nothing
   End If

   Exit Sub

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
   Resume Exit_Handler

End Sub