How to pass a value from one sub to another in Excel VBA

Refresh

December 2018

Views

12.5k time

1

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?

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!).

So how do I smuggle out the variable/value from the sub and in to another one? Is the only option Global or Public declarations?

Here's a really rough example of my 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" & NewOrderLineCounter).SpecialCells(xlCellTypeVisible).Select
End sub

(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?)

E_L

3 answers

2

if you really insist on keeping it as it is, just save the value in one of the sheets like this

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
  ws.Cells(i,j).Value = Counter
End sub

where ws is the worksheet you want to keep it and and i and j are the cell row and column value then just take it like this and clear it afterwards (or not)

Sub NumberOfRowsToCopy
  myCounter = ws.Cells(i,j).Value
  ws.Cells(i,j).clear
  ActiveSheet.Range("$A$12:$T$1001").AutoFilter Field:=16, Criteria1:= _
    "New"
  ActiveSheet.Range("B15:N" & NewOrderLineCounter).SpecialCells(xlCellTypeVisible).Select
End sub

but again i feel like there isn't a real reason to not use a function

1

I'm not sure why you feel a function would not be suitable here.

They are the same as a sub, they just have a return value.

Private Sub MainMacro()
    Dim lReturn As Long

    'Get the return from the CommentSub
    lReturn = CommentSub

    'Pass that to the nextsub
    NumberOfRowsToCopy (lReturn)
End Sub

Function CommentSub() As Long 'Declare the return type after the function
  Dim NewOrderLineCounter As Long
  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

  'Here you set the return value of the funtion
  CommentSub = NewOrderLineCounter
End Function

Sub NumberOfRowsToCopy(lCount As Long) 'Declare the variable being passed to the sub.
Dim NewOrderLineCounter As Long
NewOrderLineCounter = lCount
  ActiveSheet.Range("$A$12:$T$1001").AutoFilter Field:=16, Criteria1:= _
    "New"
  ActiveSheet.Range("B15:N" & NewOrderLineCounter).SpecialCells(xlCellTypeVisible).Select
End Sub

Or if you want to go with the public variable route, declare them at the top of the form or module code. Above all functions and subs.

'Declared like this it can be accessed by any sub or function in this module or form.
Private NewOrderLineCounter as Long

'Declared like this it can be accessed by any sub or function in this module or form and from others. Although I think if it is in a form it will not be accessible from modules.  For that you can create a module called globals and declare it there as public.
Public NewOrderLineCounter as Long

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" & NewOrderLineCounter).SpecialCells(xlCellTypeVisible).Select
End sub
1

How to pass and update variables between Subs (and Functions)

Option Explicit

Public Sub MainSub()

    Dim local_1 As Long
    Dim local_2 As Long

    local_1 = 0
    local_2 = 0

    setVal local_1              'Sub setVal() updates local_1
    MsgBox local_1              'result: 1

    putVal local_1              'Sub putVal() doesn't update local_1
    MsgBox local_1              'result: 1

    local_2 = getVal(local_1)   'Function getVal() updates local_1 and local_2
    MsgBox local_1              'result: 2
    MsgBox local_2              'result: 3

End Sub


Public Sub setVal(ByRef val As Long)    'pass ByRef (not a copy)
    val = val + 1
End Sub

Public Sub putVal(ByVal val As Long)    'pass ByVal (a copy)
    val = val + 1
End Sub

Public Function getVal(ByRef val As Long) As Long
    val = val + 1               'updates val
    getVal = val + 1            'doesn't update val (returns a new value)
End Function

.

I would replace the For loop in your CommentSub() Sub with an Autofilter:

Public Sub MainMacro()
    Dim newOrderRows As Long

    Call CommentSub(newOrderRows)
    Call NumberOfRowsToCopy(newOrderRows)
End Sub

Public Sub CommentSub(ByRef newOrderRows As Long)
    Dim vRng As Range

    With ActiveSheet.UsedRange
        .AutoFilter Field:=19, Criteria1:="New"
        Set vRng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Columns("V")

        vRng.SpecialCells(xlCellTypeVisible) = "New Line"
        newOrderRows = vRng.SpecialCells(xlCellTypeVisible).Count
        .AutoFilter
    End With
End Sub

Public Function NumberOfRowsToCopy(ByVal newOrderRows As Long) As Long
    Dim x As Long

    With ActiveSheet
        .Range("A12:T" & .UsedRange.Rows.Count).AutoFilter Field:=16, Criteria1:="New"
        x = .Range("N15:N" & 15 + newOrderRows).SpecialCells(xlCellTypeVisible).Count
    End With

    NumberOfRowsToCopy = x
End Function