vba 我如何为下一个子程序保留这个变量的值？

How can I keep the value of this variable for my next subroutine?

提问by TheTreeMan

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!

我是一名初级程序员（没有经验），正在学习 Visual Basic 以完成我现在正在做的工作。我已经阅读了一天左右，终于决定开始制作所需的程序！

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.

为什么 Counter 的值不结转到第二个子程序？现在我有一个解决方法来计算 B 列中填写的单元格数量，这给了我数字。但是，这使得我无法将 B 列中的任何空间用于我想要使用的工作表的其余部分。

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.

任何人都可以帮忙吗？我认为“公共”会使其成为工作簿级别的变量？每当我让第二个 sub 显示 Counter 的值时，它都会显示为零。

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

对不起，如果我的代码凌乱/效率低下。我还在学习。:)

回答by TheZ

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

在您的代码中，您声明了Public Counter As Long两次。可能发生的情况是您的每个Sub块都获得了不同的Counter变量。如果删除第二个，它们应该共享相同的变量。

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

此外，您应该只需要为Option Explicit每个模块列出一次。哪个，现在我看到你指定这些是单独的模块，你做得很好。

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 Explicitwhich means that the variables must be defined manually and they will never be defined automatically.

在您的子程序中，您引用了该变量，因此程序开始向上查找。假设您当然已经设置Option Explicit，这意味着必须手动定义变量，并且永远不会自动定义它们。

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 )
)

回答by Jon Crowell

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.

我删除了您在生产中可能需要的一堆无关代码，但只是妨碍了查看问题。我测试了以下内容，并且 Counter 保持其在第一个模块中的第一个 sub 中设置的值到第二个模块中的 sub 。

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:

模块 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 messageboxif you run TableCreation1and then FindCFLGuessin sequence.

如果您创建一个包含 2 个模块的新工作簿并将代码粘贴到每个模块中，则 Counter 变量的值将显示在messagebox如果您运行TableCreation1然后FindCFLGuess按顺序显示。

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.

如果由于某种原因出现运行时错误，或者中断代码的执行，显然 Counter 将失去其值。如果这两种情况都没有发生，您应该在单步执行代码时添加一个监视来跟踪变量的值。

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.

要添加监视，请右键单击变量，选择添加监视...，然后从模块下拉列表中选择（所有模块）。您还可以在表达式的值更改时让监视中断。