VBA; what it means, when there are arguments in the brackets after the subroutine title?

Question

I've written about 10 easy macros in the last 18 months, and I would like to understand more than I do in VBA programming.

In none of the subroutines I've written were there arguments in the brackets after the subroutine title.

I have found this code (found here) on stackoverflow. It's used to find the last row that contains data in the Excel sheet. But it's got some arguments/parameters (please explain the difference) in the brackets after the subroutine name, which I've never seen before.

What do these arguments stand for? I'm unable to figure this out.

Sub GetLastRow(strSheet, strColumn)
     Dim MyRange As Range
     Dim lngLastRow As Long

     Set MyRange = Worksheets(strSheet).Range(strColum & "1")

     lngLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
End Sub

Another thing I don't understand is why the "MyRange" variable needed to be created. Was this necessary or is there a way to simplify this code, get rid of "MyRange" variable and use Worksheets("MBank_Statsy").UsedRange property??

Below you can see the worksheet that I would like to apply this code to.

MyRange was not needed it just makes it ugly. theres no reason to have a string for the column number. also it has to be `Sub Test(Var1 as Integer)`and not just the var name. — Plagon, May 25 '17 at 10:28
Which argument you have a question about? There are 2 parameters and 5 arguments used in your code snippet... which is the one you have trouble understanding? — trincot, May 25 '17 at 10:29
For arguments [see this](https://msdn.microsoft.com/en-us/library/aa263527(v=vs.60).aspx). As you say MyRange is not required, you could skip it. The author probably did it that way as its cheap to use a variable and makes the code more readable. If the code made use of the range more than once you should always use a variable as there is a small cost when accessing sheets & ranges by name/address as a lookup is involved. — Alex K., May 25 '17 at 10:30
@trincot I don't understand 'Sub GetLastRow(strSheet, strColumn)' why there are parameters in the sub brackets. I was writing an excel function once, and after it's name I've put some parameters in the brackets, but I don't understand why to put parameters into subroutine brackets. — michal roesler, May 25 '17 at 10:32
The Sub (which, to make it of any real use should really be a Function) is basically a routine that finds the LastRow for a given Sheet and Column. The author has chosen to provide these as strings (rather than the objects themselves). If the Sheet and Column weren't provided, the routine would not know which Sheet and Column you wanted the result for. — CLR, May 25 '17 at 10:37
Parameters are passed to a Sub in exactly the same way, and for exactly the same reason as when passing them to a Function : to provide context on which to perform the routine. The only real difference is that a Function is expected to return a value, a Sub is just expected to 'do' things.. — CLR, May 25 '17 at 10:43

score 2 · Accepted Answer · answered May 25 '17 at 10:50

I think the comments upon your question have probably given you all the info you need on why things are done the way they are, but if you're interested, the entire subroutine could be reduced to:

Function GetLastRow(strSheet, strColumn)
     GetLastRow = Cells(Rows.Count, Worksheets(strSheet).Range(strColumn & "1").Column).End(xlUp).Row
End Function

Note: this is now a function. To find out the last used cell in Sheet1, column 'C' for instance, you would do something like this:

lastrowused = GetLastRow("Sheet1","C")

I suspect that the original routine started out with more going on (perhaps it found the last cell and did something to it) and has been stripped down to just retrieving the last row but no thought was given to reducing unnecessary instructions.

You can also simplify things to `GetLastRow = Cells(Rows.Count, strColumn).End(xlUp).Row` because `Worksheets(strSheet).Range(strColumn & "1").Column` is just going to return the column that is already referred to by `strColumn`. — YowE3K, May 25 '17 at 19:52

VBA; what it means, when there are arguments in the brackets after the subroutine title?

1 Answers1