title: How to pass a value from one sub to another in Excel VBA
body: <p>In my macro I have a subroutine which (using a for loop) goes through the rows of a table and writes a comment in column V depending on what is in column S.  During this loop it also counts the number of times it says "New" in column S.  Now I want to pass this value up in to the main macro and down in to another subroutine.  How do I do this or is my approach here wrong?</p>
  
  <p>If I understand correctly, its usually not possible to return values from subs in excel VBA, but you can from a function.  However I don't think a function would be suitable here (I might be misunderstanding how functions work in VBA and/or not realising their full potential!).</p>
  
  <p>So how do I smuggle out the variable/value from the sub and in to another one?  Is the only option <code>Global</code> or <code>Public</code> declarations?</p>
  
  <p>Here's a really rough example of my code:</p>
  
  <pre><code>Sub MainMacro ()
    Call CommentSub
    Call NumberOfRowsToCopy
  End sub
  
  Sub CommentSub
    Dim Counter As Integer
    For Counter = 1 to 500
      If Cells(Counter, "S") = "New" Then
        NewOrderLineCounter = NewOrderLineCounter + 1
        Cells(Counter, "V").Select
        ActiveCell.FormulaR1C1 = "New Line"
      End If
    Next Counter
  End sub
  
  Sub NumberOfRowsToCopy
    ActiveSheet.Range("$A$12:$T$1001").AutoFilter Field:=16, Criteria1:= _
      "New"
    ActiveSheet.Range("B15:N" &amp; NewOrderLineCounter).SpecialCells(xlCellTypeVisible).Select
  End sub
  </code></pre>
  
  <p>(Btw, I'm aware that there maybe "better" ways to get the number of rows that need to be copied here (thereby eliminating the need to pass values between subs) but I think I've tried all of them and none worked.  I think its the format of the excel sheet but that's another problem and you gotta work with what you're given, right?)</p>