CODEWITHSUNDEEP
vbaexcelfunctionuser-defined-functions
c3nixon
c3nixon

Reputation: 9

VBA Pass value between subs

I am trying to learn how to pass values back and forth between subs. I have created several macro subs that ultimately use the same lines of code over and over again between them. To start I want to be able to look up/count the # of columns and rows. This is what I have so far but it is not working.

I would also like to expand it beyond just cols to rows.

Public Sub main()
Dim lCols As Integer
Dim lRows As Integer

   lCols = countCols(Sheet1)

   Sheet1.Range("M2").Value = lCols

End Sub

Public Function countCols(sheetValue As Worksheet) As Variant
       countCols = sheetValue.Cells(Rows.Count, 1).End(x1Up).Row
   Exit Function
End Function

Right now it hangs within the function... Appears it is not passing the "Sheet1" into sheetValue.

Upvotes: 0

Views: 1804

Answers (1)

Mrig
Mrig

Reputation: 11702

Few points:

  1. It's better to declare row and column variables as long. See this.
  2. When you've declared variables lCols, lRows as Integer then return type of functions should also be Integer
  3. No need to use Exit Function in your case. Exit Function is used to exit a function and execute the statement following function call. However in your case the function itself ends there hence exit is not required.

Try following

Public Sub main()
    Dim lCols As Long, lRows As Long  'declared as long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")  'change Sheet2 to your data sheet

    lCols = countCols(ws)
    lRows = countRows(ws)

    ws.Range("M2").Value = lCols
    ws.Range("N2").Value = lRows

End Sub

Public Function countCols(sheetValue As Worksheet) As Long  'should return long
       countCols = sheetValue.Cells(1, sheetValue.Columns.Count).End(xlToLeft).Column
End Function

Public Function countRows(sheetValue As Worksheet) As Long  'should return long
       countRows = sheetValue.Cells(sheetValue.Rows.Count, 1).End(xlUp).Row
End Function

Upvotes: 2

Related Questions