Variable being used in different module VBA

Question

I'm very new to VBA and I need all the help I can get. Module 1 counts the numbers of cells with integers in the first row starting at C1 (A1 and B1 are titles)in the 'LLP Disc Sheet'. The number of cells for this specific worksheet is 9. However, 9 is not always the number. Sometimes the number is 1, 2, 3, 4, etc. It just depends if the user fills in those cells. I'm trying to store that number 9 to use in Module 2.

Module 2 produces copies of an entire sheet called 'MasterCalculator', which I plan on renaming each sheet produced to the Cell values that were counted in Module 1. The number of copies produced must match the calculation in Module 1 (Which is currently 9).

I can't seem to figure out how to reference the variable 'lc' in the t3() module in the test() module. The number of copies of the MasterCalculator Sheet is inaccurate.

MODULE 1

Public lc As Integer
Sub t3()
Dim lc As Long, sh As Worksheet
Set sh = ActiveSheet
With sh

lc = Rows(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count - 1
End With
ThisWorkbook.Save
End Sub

MODULE 2

Sub test()
Dim sh As Worksheet
Dim last_is_visible As Boolean

With ActiveWorkbook
last_is_visible = .Sheets(.Sheets.Count).Visible
.Sheets(Sheets.Count).Visible = True
.Sheets("MasterCalculator").Copy After:=.Sheets(Sheets.Count)
Set sh = .Sheets(Sheets.Count)
If Not last_is_visible Then .Sheets(Sheets.Count - t3.lc).Visible =     False
sh.Move After:=.Sheets("LLP Disc Sheet")
End With
End Sub

You need global / public variable . You can declare it in one module can store/retrieve a value from any module .. [See Microsoft link](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables) ... [and this also](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/public-statement) .. [and stackoverflow](https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba) — Naresh, Mar 31 '20 at 07:53
Does this answer your question? [How do I declare a global variable in VBA?](https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba) — FunThomas, Mar 31 '20 at 07:57
with `Public lc As Integer Sub t3() Dim lc As Long` you are defining lc twice? No need. if you need it to be long, then `Public lc As Long` is enough for all modules. — Naresh, Mar 31 '20 at 08:03
@Naresh Bhople "No need"? - Not quite. Declaring a variable of the same name as a global one in a procedure in fact creates as second variable which prevents the use of the global one in that procedure. The global variable will retain the value it was assigned elsewhere in the project while its doppelgänger loses its value at the end of the procedure. I know, sometimes it's very hard to think up "meaningful names" but the solution nobert seems to apply here is commendable only for its ability to confuse novices. More staid programmers will think of a different name for each scope. — Variatus, Mar 31 '20 at 08:13
@Variatus oh yes.. I forgot about the scope, never encountered it. Thanks :) .. — Naresh, Mar 31 '20 at 08:16

score 2 · Answer 1 · answered Mar 31 '20 at 08:55

First off, your requirement isn't suitable to be filled by a global variable. It's clearly a task for a function.

Second, your line lc = Rows(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count will throw an error if there are no matching SpecialCells. Therefore it would require an error handler so that it can return -1 instead of crashing.

Third, rather than make your idea of counting SpecialCells work, please consider the alternative which is to find the end of the first row by looking from the right (instead of counting from the left). The above reasoning leads me to this function.

Function ColumnsCount(Optional Ws As Worksheet) As Long

    If Ws Is Nothing Then Set Ws = ActiveSheet
    With Ws
        ColumnsCount = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
    End With
End Function

Implementation of this function into your code leads to these two lines of code in your Test procedure.

Set Sh = .Sheets(Sheets.Count)
If Not last_is_visible Then .Sheets(Sh.Index - ColumnsCount(Sh)).Visible = False

The function ColumnCount will return the count from the worksheet given to it as parameter. In the above code that is the worksheet Sh. In the code in your question it seems to be the ActiveSheet (Perhaps Sh is the active sheet. Just make sure you pass the sheet on which you want to count and the function will return the correct number.

As two matters of principle: First, avoid using ActiveSheet as much as possible. Assign your sheets to variables meaningfully named and refer to them by the names you gave yourself. This is because ActiveSheet can be affected by user action outside the scope of your code and 9 times out of 10 it isn't a meaningful name.

Second, avoid what rubberduck calls "snake_names". LastIsVisible is a meaningful name, last_is_visible is a pain in the eye. I would use LastVis because it's shorter. I also recommend to use upper and lower case letters in names and this is my reason.

As you declare names, in the Dim statements, use caps and smalls.
As you write your code, use lower case only.
VBA will correct the capitalisation of what you type to match the declaration.
So, when VBA doesn't change the names you typed you know that there is a typo. - Instant alert for no effort at all. And your code becomes easier to read into the bargain.

Isn't your answer the same as mine + some suggestions in CodeReviews' style? — HTH, Mar 31 '20 at 11:22

HTH · Answer 2 · 2020-03-31T08:16:06.243

1

To me it's preferable to set down a Function:

Function Lc(Optional sh As Worksheet) As Long
    If sh Is Nothing Then Set sh = ActiveSheet
    With sh
        Lc = .Rows(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count - 1
    End With
End Function

and call it whenever you need, for example:

Sub test()
    Dim sh As Worksheet
    Dim last_is_visible As Boolean

    With ActiveWorkbook
        last_is_visible = .Sheets(.Sheets.Count).Visible
        .Sheets(Sheets.Count).Visible = True
        .Sheets("MasterCalculator").Copy After:=.Sheets(Sheets.Count)
        Set sh = .Sheets(Sheets.Count)
        If Not last_is_visible Then .Sheets(Sheets.Count - Lc).Visible = False '<--- Lc will get the "current" Lc value
        sh.Move After:=.Sheets("LLP Disc Sheet")
    End With
End Sub

A Public variable is as handy as can be dangerous in that you have to carefully :

"follow" it throughout all your code and ensure it isn't being unwillingly set
check it doesn't persist through sessions

edited Mar 31 '20 at 08:16

answered Mar 31 '20 at 08:10

HTH

2,031
1
4
10

1

I think .. If you set Lc as function you have to give attribute each time you use it. Hence, it will not be a global variable. – Naresh Mar 31 '20 at 08:14
@NareshBhople, the point is NOT to have global variable for what I explained. And I don't get what you mean by _"If you set Lc as function you have to give attribute each time you use it."_. Finally I myself unwillingly upvoted your comment ... – HTH Mar 31 '20 at 08:19
:). See, I said "I think" am not sure too. But whenever we access a function, we need to give its parameters? In your answer it is worksheet. So every procedure that uses this function will need that parameter and hence, it will be defined ifresh every time. – Naresh Mar 31 '20 at 08:22
1

@NareshBhople 1) I proposed a function wth an _Optional_ parameter, i.e. you can omit it (and it would assume `ActiveSheet`), 2) it will definitely be defined fresh all the times it's being called. – HTH Mar 31 '20 at 08:26
@nobert, does it fit your needs? – HTH Mar 31 '20 at 08:29

Variable being used in different module VBA

2 Answers2