How can I keep the value of this variable for my n

Question

I'm a beginning programmer (no experience) learning Visual Basic for a job I'm doing right now. I've been reading for a day or so and have finally decided to start making the required program!

However, I'm running into some problems.

Right now I have two subroutines. The first subroutine lets the user input how many data pairs they have so that I can create a table for them to fill in. This is so their data is in the right place for me to reference it later.

There is then a button they press after they finish entering the data to start a different subroutine which will do some calculations to the numbers they entered. My issue is that I need the variable that says how many data pairs they have to carry over to the second routine.

Before I continue, here is my code so far! (You'll have to scroll down in the window) I should also note that the second subroutine is in a separate module.

Option Explicit

Public Counter As Long


Sub TableCreation1()

    ActiveSheet.Shapes.Range(Array("Button 5")).Select

    Selection.Delete

    Counter = InputBox("How many pairs of data do you have? ")

    Range("A1") = "Time (days)"

    Range("B1") = "CFL (measured)"

    Range("A1:B1").Font.Bold = True

    Columns("A:B").EntireColumn.EntireColumn.AutoFit

    Range("A1").Select

    ActiveCell.Range("A1:B" & Counter + 1).Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

       .LineStyle = xlContinuous

       .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .Weight = xlThin

    End With

    Dim btn As Button

    Dim rng As Range

    With Worksheets("Sheet1")

        Set rng = .Range("A" & Counter + 2)

        Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)

    With btn

        .Caption = "Click this button to begin calculations"

        .AutoSize = True

    End With

    End With

End Sub



Option Explicit

Dim IntermediateVariable As Long

Public Counter As Long

Sub FindCFLGuess()

IntermediateVariable = Worksheets("Sheet1").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count

Counter = IntermediateVariable - 1

End Sub

Why isn't the value for Counter carrying over to the second subroutine? Right now I have a workaround that counts the amount of cells filled out in column B, which gives me the number. However, that makes it so that I wouldn't be able to use any of the space in column B for the rest of the sheet, which I want to use.

Can anyone help? I thought the "Public" would make it a Workbook-level variable? Whenever I make the second sub display the value of Counter, it comes up as zero.

Sorry if my code is messy/inefficient. I'm still learning. :)

Answer 1

I removed a bunch of your extraneous code that you may need in production but is just getting in the way of seeing the problem. I tested the following, and Counter maintains its value that is set in the first sub in the first module to the sub in the second module.

Module 1:

Option Explicit

    Public Counter As Long

    Sub TableCreation1()
        Dim btn As Button
        Dim rng As Range

        Counter = InputBox("How many pairs of data do you have? ")
        Range("A1") = "Time (days)"
        Range("B1") = "CFL (measured)"
        Range("A1:B1").Font.Bold = True
        Columns("A:B").EntireColumn.EntireColumn.AutoFit
        Range("A1").Select
        ActiveCell.Range("A1:B" & Counter + 1).Select

        With Worksheets("Sheet1")
            Set rng = .Range("A" & Counter + 2)
            Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
            With btn
                .Caption = "Click this button to begin calculations"
                .AutoSize = True
            End With
        End With
    End Sub

Module 2:

Option Explicit

Sub FindCFLGuess()
    MsgBox ("Counter = " & Counter)
End Sub

If you create a new workbook with 2 modules and paste the code into each, your Counter variable's value will be displayed in the messagebox if you run TableCreation1 and then FindCFLGuess in sequence.

If you have a runtime error for some reason, or break the execution of your code, obviously Counter will lose its value. If neither of those things is happening, you should add a watch to track the variable's value while you step through your code.

To add the watch, right-click the variable, select Add Watch... and then select (All Modules) from the module drop-down. You can also have the watch break when the value of the expression changes.

Answer 2

In your code you are declaring Public Counter As Long twice. What's probably going on is that each of your Sub blocks are getting a different Counter variable. If you remove the second one they should both share the same variable.

Also, you should only need to list Option Explicit once per module. Which, now that I see you specify these are separate modules, you are doing fine.

EDIT: Trying to elucidate more.

Think of it as a layering and each "scope" is what the layer can access. Each layer has access to itself and all parents. Here's a simplified visualization:

( program 
    ( module
        ( sub )
    )
)

In your subroutine you reference the variable, so the program starts looking upwards. Assuming of course you've set Option Explicit which means that the variables must be defined manually and they will never be defined automatically.

What you want to have is something like the following. Where the variable is global in scope so that it can be accessed from other modules running concurrently.

( program
    [global variable]
    ( module1
        ( sub )
    )
    ( module2 )
)