Passing Pamertrised Query from Excel to SQL Server using Password Protected VBA

Passing Dynamic Query Values from Excel to SQL Server describes step by step solution of this use case.  The disadvantage of this approach is that the connection string is accessible by the end user.  The way around of this issue is to create password protected VBA as follows:

Sub btnRefresh_Click()
On Error GoTo Error_Handler
    Dim SellStartDate As String _
    , SellEndDate As String _
    , strSQL As Variant _
    , sSQLQry As String _
    , Conn As New ADODB.Connection _
    , mrs As New ADODB.Recordset _
    , DBPath As String _
    , sconnect As String

        Call ClearWKSData(ThisWorkbook.Worksheets("Your Sheet Long Name"), 2, 1)
        sconnect = "WSID=*;DRIVER=SQL Server;APP=Microsoft Office 2013;SERVER=Your Server Name

;UID=Your SQL Account Name;PWD=Your Password;DATABASE=Your Database Name;"
        SellStartDate = Sheets("Change Parameters").Range("C3").Value
        SellEndDate = Sheets("Change Parameters").Range("C4").Value
        strSQL = strSQL + " SELECT * FROM dbo.YourUserDefinedFunction('" & SellStartDate & "','" & SellEndDate & "')"
        Conn.Open sconnect
        mrs.Open strSQL, Conn
        Sheets("Purchase Order Spend").Range("A2").CopyFromRecordset mrs
        mrs.Close
        Conn.Close
        ThisWorkbook.Worksheets("Your Sheet Long Name").Activate
Exit_Procedure:
        Exit Sub

Error_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, Err.Source
        Resume Exit_Procedure
End Sub

Sub ClearWKSData(wksCur As Worksheet, iFirstRow As Integer, iFirstCol As Integer)

On Error GoTo Error_Handler
    Dim iUsedCols As Variant, iUsedRows As Variant
        iUsedRows = wksCur.UsedRange.Row + wksCur.UsedRange.Rows.Count - 1
        iUsedCols = wksCur.UsedRange.Column + wksCur.UsedRange.Columns.Count - 1
        If iUsedRows > iFirstRow And iUsedCols > iFirstCol Then
            wksCur.Range(wksCur.Cells(iFirstRow, iFirstCol), wksCur.Cells(iUsedRows, iUsedCols)).Clear
        End If
Exit_Procedure:
        Exit Sub

Error_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, Err.Source
        Resume Exit_Procedure
End Sub

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Change Parameters").Activate
End Sub

The .xlsm sample file is available for download. To lock the VBA please press Alt+F11 

-> VBA Project Properties -> Protection Tab -> Lock project for viewing -> Enter your password.

If you find this script useful please donate generously.