VBA Pass value between subs

Question

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.

Just FYI, I have answered this question before, see this link: https://stackoverflow.com/questions/17998773/set-a-variable-in-one-sub-and-use-in-another/17999208#17999208 — Mardin Yadegar, Oct 02 '17 at 04:41
If Scott's comment doesn't resolve your issue, please update the question with more details about what you mean by "it hangs". (Using `x1Up` instead of `xlUp` would usually make the code crash with an "Application-defined or object-defined error", or just return an incorrect value, not hang.) — YowE3K, Oct 02 '17 at 04:50
tip: type all commands in lower case like `xlup`, VBA editor will change it to `xlUp`. if you do not see the change then you have mis-typed it .... same with variables .... use upper and lower case when defining `dim lRows As long` but when you use the variable use all lower case `....value = lcols`, the editor will chnge it to `.....Value = lCols` .... if it does not change then you made an error in typing — jsotola, Oct 02 '17 at 05:01

Mrig · Accepted Answer · 2017-10-02T04:52:00.610

2

Few points:

It's better to declare row and column variables as long. See this.
When you've declared variables lCols, lRows as Integer then return type of functions should also be Integer
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

edited Oct 02 '17 at 04:52

answered Oct 02 '17 at 04:40

Mrig

11,612
2
13
27

I should refresh more frequently..@c3nixon His code breaks because of missing `Option Explicit`and Sheet1 object. Is there any chance countCols can get Null or am I to careful? – BitAccesser Oct 02 '17 at 04:59
1

@BitAccesser - If there's no row or column with data then `Cells(Rows.Count, 1).End(xlUp).Row` and `Cells(1, Columns.Count).End(xlToLeft).Column` will return `1`. – Mrig Oct 02 '17 at 05:06
@BitAccesser If a function returns `Long`, is not possible to get `Null` from it. – LS_ᴅᴇᴠ Oct 02 '17 at 08:12
@LS_ᴅᴇᴠ What will cause an error if you try to assing a Null-Value to a Long. If `Cells(1, Columns.Count).End(xlToLeft).Column` can get Null you have to avoid assinging Null, by replaceing Null with a Long (or `On Error Resume Next` if you want to get shot ;-)): Iam not familiar with Excel functions, that's why I asked. btw is there no Nz() like function for Excel? – BitAccesser Oct 02 '17 at 08:30

VBA Pass value between subs

1 Answers1