CODEWITHSUNDEEP
vbaexcel
michal roesler
michal roesler

Reputation: 499

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

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.

enter image description here

Upvotes: 0

Views: 978

Answers (1)

CLR
CLR

Reputation: 12279

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.

Upvotes: 2

Related Questions