turn sub into function with arguments

[spencer_time]

Hello all, I have some code that works in a sub, but I need to use it several times over the course of my spreadsheets timeline. It currently only works on the active sheet, which I also need to change to be the sheet/variable that is called for in the argument when calling the function.

This is the sub that works on an active sheet:

Sub count()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    Dim numRow As Long
    Dim numCol As Long
    Dim nr As Long
    Dim nc As Long
    nr = 1 'n for row
    nc = 1 'n for column

Do While ws.Cells(Rows.count, nr).End(xlUp).row <= 1 'keep looping until number of rows counted is larger than 1
nr = nr + 1
Loop
Do While ws.Cells(nc, Columns.count).End(xlToLeft).Column <= 1 'keep looping until number of columns counted is larger than 1
nc = nc + 1
Loop

numRow = ws.Cells(Rows.count, nr).End(xlUp).row
numCol = ws.Cells(nc, Columns.count).End(xlToLeft).Column

MsgBox "Rows = " & numRow
MsgBox "Columns = " & numCol
MsgBox numRow & " rows and " & numCol & " columns"
MsgBox "Sum of number of rows and number of columns = " & (numRow + numCol)

End Sub

This is where I'm at so far in trying to turn this into a function, but it doesn't work:

Public Function cntRowCol(wb As Workbook, ws As Worksheet)

    Public Dim numRow As Long
    Public Dim numCol As Long
    Dim nr As Long
    Dim nc As Long
    nr = 1 'n for row
    nc = 1 'n for column

Do While wb.ws.Cells(Rows.count, nr).End(xlUp).row <= 1
nr = nr + 1
Loop
Do While wb.ws.Cells(nc, Columns.count).End(xlToLeft).Column <= 1
nc = nc + 1
Loop

numRow = wb.ws.Cells(Rows.count, nr).End(xlUp).row
numCol = wb.ws.Cells(nc, Columns.count).End(xlToLeft).Column

End Function

I need the function to be run on the workbook and worksheet called for in the arguments, and the variables numRow and numCol to be globally accessible so I can use them in other sub's as well as them be updated each time this function is ran.

I anticipate this being able to be implemented as something similar to the following (to get results that match the original sub):

Sub callFun()

    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    
    Call cntRowCol(wb, ws)
    
    MsgBox "Rows = " & numRow
    MsgBox "Columns = " & numCol
    MsgBox numRow & " rows and " & numCol & " columns"
    MsgBox "Sum of number of rows and number of columns = " & (numRow + numCol)

End Sub

I tried to explain what I'm trying to do as clear as I could, if further explanation is needed, let me know and I will expand on my needs.

Any suggestions as to how to make this work are appreciated.

EDIT 09:42: TYPO

 

[FormR]

Hi, does this code replicate what you are trying to do?

Public Function cntRow(ws As Worksheet) As Long
    cntRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
End Function


Public Function cntCol(ws As Worksheet) As Long
    cntCol = ws.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
End Function


Sub CallFun()


Dim numRow As Long
Dim numCol As Long
Dim wb As Workbook
Dim ws As Worksheet


Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet


numRow = cntRow(ws)
numCol = cntCol(ws)


MsgBox "Rows = " & numRow
MsgBox "Columns = " & numCol
MsgBox numRow & " rows and " & numCol & " columns"
MsgBox "Sum of number of rows and number of columns = " & (numRow + numCol)


End Sub

 

[spencer_time]

Hi, does this code replicate what you are trying to do?

Yes FormR, it works great.

Thank you for your help. All you guys on this forum have been so helpful as I have been working on my big spreadsheet (and consequently as I learn to use excel with VBA).

EDIT 10:37: I don't really understand your version too well, but it does work and that is the important thing right now. I will get to where I will understand things better in time.