title: How do I pass a range obj variable to a sub in Excel VBA (2016)
body: <p><strong>Given the following code:</strong>
  I can not seem to successfully pass a <strong>Range</strong> Object Variable from one sub-function to another. I spent an entire day researching, and experimenting before I swallowed pride and came here. </p>
  
  <p>Please read the comments below, and reply with any ideas you have regarding why the LAST two lines will not behave. </p>
  
  <pre><code>Public Sub doSomethingToRows(ROI As Range)
  *'do Something with the cell values within the supplied range*
  
  End Sub
  '
  Public Sub testDoAltRows()
  
      Dim RegionOfInterest As Range       'is this an object or not?
  
      '*The following yields: Class doesn't support Automation (Error 430)*
      '*Set RegionOfInterest = New Worksheet 'this just gives an error*
  
      Set RegionOfInterest = Worksheets("Sheet1").Range("A1")
      RegionOfInterest.Value = 1234.56        '*okay, updates cell A1*
  
      Set RegionOfInterest = Worksheets("Sheet1").Range("B5:D15")
      RegionOfInterest.Columns(2).Value = "~~~~~~"    '*okay*
  
      'doSomethingToRows (RegionOfInterest)   'why do I get "OBJECT IS REQUIRED" error?
      doSomethingToRows (Worksheets("Sheet1").Range("B5:C15")) 'but this executes okay
  End Sub
  </code></pre>