Passing values between three subroutines

[AndyTampa]

I'm trying to pass a value between subs that can be used by other subs. I was able to pass a value to one sub, but since I may get that value to use in a few subs, I created a subroutine to get that value. But it won't get the value and pass it to the main macro.

In this macro, I want to run the subroutine LastRow to get the last row of data and pass that value to MAIN. Then I want MAIN to pass the value to TWO. I want to be able to use both LastRow and TWO in other macros too. When I put the entire LastRow code into MAIN, the variable is passed to TWO without a problem. How can I get LastRow to pass the variable to MAIN first?

Sub MAIN()

   Dim LRow As Long

   LastRow (LRow)

   TWO (LRow)

'a whole bunch of stuff

End Sub
-------------
Sub LastRow(LRow)

  LRow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

End Sub
-----------
Sub TWO(LRow)

'more stuff

End Sub

 

[offthelip]

the easy way is to define lrow at the module level so that it is retained between calls like this:

   Dim LRow As Long   ' put this right at the top of the module

Sub MAIN()


Call LastRow
MsgBox LRow & " Main"
Call TWO

'a whole bunch of stuff

End Sub
Sub LastRow()

  LRow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
 MsgBox LRow & " Lastrow "
End Sub

Sub TWO()
MsgBox LRow & "two"
'more stuff

End Sub

 

[dmt32]

Hi,
I suspect that your LastRow sub should be a Function that returns the value to the main sub - you can then pass the returned value to other subs as required

Sub MAIN()

   Dim LRow As Long

   LRow = LastRow
  
   MsgBox LRow

   TWO LRow

'a whole bunch of stuff

End Sub
'-------------
Function LastRow() As Long

  LastRow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

End Function
'-----------
Sub TWO(ByVal LRow As Long)

MsgBox LRow

'more stuff

End Sub

Dave

 

[AndyTampa]

offthelip, VB will not allow me to place the DIM before the SUB.

Dave, that worked. Thanks.

 

[offthelip]

VB will not allow me to place the DIM before the SUB.

Strange, it works OK for me and I am using office 2010 as well

 

[dmt32]

offthelip, VB will not allow me to place the DIM before the SUB.

Dave, that worked. Thanks.

offthelip solution should work ok but you need to place the variable at very TOP of the module as indicated

Glad we were able to help & appreciate feedback

Dave

 

[AndyTampa]

OOOOHHHH! The module and not the subroutine? I have a Public Function above that subroutine. I'd have to place the DIM statement above that, in a different macro?

 

[offthelip]

Yes the very first line in the module, you will find vba automatically puts a dividing line underneath it

 

[AndyTampa]

So if the very top of my module looks like this:

Public Function UName()
    UName = Environ$("USERNAME")
End Function
--------------------------------------
Sub ONE()

  Dim LRow As Long, RNum As Long, ILBDTLErr As Long, InvEnt As Long, BLKErr As Long, ADJErr As Long, BPErr As Long, VALNum As Long, ErrQty As Long
  
'------------------
'| FIND LAST ROW  |
'------------------
   LRow = LastRow

And the two subroutines below ONE() are:

Function LastRow() As Long

  LastRow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

End Function
-------------------------------
Sub TWO(ByVal LRow As Long)

Does moving the DIM line to the top of the module function for every subroutine in the module?
Do I still have to pass LRow as a variable to Sub TWO or can I simply call LastRow without adding the LRow = LastRow line to my Sub ONE?

 

[dmt32]

My solution changed your LastRow Sub to a Function - A Function RETURNS a value to the calling procedure. In the example given, there is no need to have a variable at top of module as you can pass the returned value to other procedures from the calling procedure as shown in my post.

The solution posted by @offthelip retains your codes as a Sub but the value of the variable is held at Module level (outside any procedure) Variables declared with Dim statement at the module level are available to all procedures within that module.

Either solution should work for you

Dave