title: Sub With Arguments Produces Error (Compile error: Constant expression required)
body: <p>I wrote this script which loops through a column and <strong>if</strong> it finds a value in it and a value in another column, changes the value of the other column. </p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub RepChange()
  
      Const Company As String = "Kellog's"
      Const Representer As String = "Elite"
      Const NewRepresenter As String = "Core"
  
      Const cSheet As Variant = "Sheet1"
      Const cColRep As Variant = "H"
      Const cColComp As Variant = "I"
      Const cFR As Long = 2
  
      Dim LR As Long
      Dim i As Long
  
      With ThisWorkbook.Worksheets(cSheet)
          LR = .Columns(cColComp).Cells(.Rows.Count).End(xlUp).Row
          For i = cFR To LR
              If StrComp(.Cells(i, cColComp), Company, vbTextCompare) = 0 And _
                      StrComp(.Cells(i, cColRep), Representer, vbTextCompare) _
                      = 0 Then .Cells(i, cColRep) = NewRepresenter
          Next
      End With
  
      MsgBox "Operation finished successfully.", vbInformation, "Success"
  
  End Sub
  </code></pre>
  
  <p>It works fine. Now I wanted to use the first three constants as arguments of the Sub:</p>
  
  <pre><code>Sub RepChange2(Company As String, Representer As String, _
          NewRepresenter As String)
  
      Const cSheet As Variant = "Sheet1"
      Const cColRep As Variant = "H"
      Const cColComp As Variant = "I"
      Const cFR As Long = 2
  
      Dim LR As Long
      Dim i As Long
  
      With ThisWorkbook.Worksheets(cSheet)
          LR = .Columns(cColComp).Cells(.Rows.Count).End(xlUp).Row
          For i = cFR To LR
              If StrComp(.Cells(i, cColComp), Company, vbTextCompare) = 0 And _
                      StrComp(.Cells(i, cColRep), Representer, vbTextCompare) _
                      = 0 Then .Cells(i, cColRep) = NewRepresenter
          Next
      End With
  
  End Sub
  </code></pre>
  
  <p>so I could use the following:</p>
  
  <pre><code>Sub Kellogs()
      RepChange2 "Kellog's", "Elite", "Core"
  End Sub
  </code></pre>
  
  <p>but it produces the following error:</p>
  
  <blockquote>
    <p>Compile error: Constant expression required</p>
  </blockquote>
  
  <p>in the line</p>
  
  <pre><code>With ThisWorkbook.Worksheets(cSheet)
  </code></pre>
  
  <p>where <code>cSheet</code> is grayed out. If I replace it with "Sheet1", another error occurs in the next line:</p>
  
  <pre><code>LR = .Columns(cColComp).Cells(.Rows.Count).End(xlUp).Row
  </code></pre>
  
  <p>where <code>cColComp</code> is grayed out etc. Changing the <code>Variants</code> to <code>Strings</code> doesn't help.</p>
  
  <p>Would appreciate any help.</p>