Excel 2010 VBA - 全局变量设置不正确

Excel 2010 VBA - global variables aren't set properly

提问by EplusL

UPDATE: I know I can just rename the sheets in VBA Editor, but my first instinct was to use code. I looked it up, saw the solution in several places, and now that it doesn't work I still want to know why. The answer might mean I'll learn something critical for later programming in VBA.

更新：我知道我可以在 VBA 编辑器中重命名工作表，但我的第一直觉是使用代码。我查了一下，在几个地方看到了解决方案，现在它不起作用我仍然想知道为什么。答案可能意味着我将学习一些对以后在 VBA 中编程至关重要的东西。

---

---

I thought I found the answer here: https://stackoverflow.com/a/1179730/1370938, but it turns out that doesn't work for me, I don't know why. I'm new to VBA so I might be missing something, but I found this solution in multiple sites and I'm wondering if I missed a simple step that was not explicited.

我以为我在这里找到了答案：https: //stackoverflow.com/a/1179730/1370938，但事实证明这对我不起作用，我不知道为什么。我是 VBA 新手，所以我可能会遗漏一些东西，但是我在多个站点中找到了这个解决方案，我想知道我是否错过了一个没有明确说明的简单步骤。

I wanted to set global worksheet variables that I could call for all my functions and forms data manipulation and make the code look lighter. (all those Worksheets("name").really fatten up the code when you jump from sheet to sheet).

我想设置全局工作表变量，我可以为我的所有函数和表单数据操作调用这些变量，并使代码看起来更简洁。（Worksheets("name").当您从一张纸跳到另一张纸时，所有这些都会使代码变胖）。

Following the answer in the link, I wrote this in one of my three modules:

按照链接中的答案，我在我的三个模块之一中写了这个：

'Global variables
Public shT As Worksheet
Public shA As Worksheet
Public shC As Worksheet
Public shM As Worksheet
Public shP As Worksheet

'Setting global variables
Sub Workbook_Open()

   Set shT = ActiveWorkbook.Worksheets("Tasks")
   Set shA = ActiveWorkbook.Worksheets("Activity")
   Set shC = ActiveWorkbook.Worksheets("Closed")
   Set shM = ActiveWorkbook.Worksheets("Modifications")
   Set shP = ActiveWorkbook.Worksheets("Persistent")

End Sub

But When running this code in another module, the variable is set with "Empty" when trying to access ranges etc and I get runtime error '424' Object Required which points to the line .Rows(3).EntireRow.Insert.

但是当在另一个模块中运行此代码时，在尝试访问范围等时变量设置为“Empty”，并且我收到运行时错误 '424' Object Required ，它指向该行.Rows(3).EntireRow.Insert。

Here is the code

这是代码

Sub addModLogEntry(sTask As String, sOwner As String, dDate As Date, sType As String)
    Dim iRow As Integer
    With shM
        .Rows(3).EntireRow.Insert

        .Range("A" & 3).value = sTask
        .Range("B" & 3).value = sOwner
        .Range("C" & 3).value = dDate
        .Range("D" & 3).value = sType

        If Day(dDate) Mod 2 = 0 Then
             shM.Range("A" & 3 & ":D" & 3).Interior.Color = RGB(230, 230, 230)
        End If

    End With

End Sub

When in debug mode if I watch the value of shMit returns Empty. I tried putting a breakpoint on the first line of Workbook_Open()but the breakpoint isn't saved when I close the document to test opening it.

在调试模式下，如果我观察shM它的值返回Empty. 我尝试在第一行放置一个断点，Workbook_Open()但是当我关闭文档以测试打开它时，断点没有被保存。

I tried puttin this code in the code for thisWorkbook and here the breakpoint works, workbook_open() is executed, but if I execute anything after it the variables are still no good.

我尝试将这段代码放入 thisWorkbook 的代码中，这里断点有效，workbook_open() 被执行，但如果我在它之后执行任何操作，变量仍然不好。

回答by Gary's Student

Dimthe variables in a Standard Module, not in the ThisWorkbookModule above the Open event code.

将标准模块中的变量变暗，而不是在ThisWorkbookOpen 事件代码上方的模块中。

EDIT#1:

编辑#1：

In the ThisWorkbookModule:

在ThisWorkbook模块中：

Private Sub Workbook_Open()
    Set shT = ActiveWorkbook.Worksheets("Tasks")
   Set shA = ActiveWorkbook.Worksheets("Activity")
   Set shC = ActiveWorkbook.Worksheets("Closed")
   Set shM = ActiveWorkbook.Worksheets("Modifications")
   Set shP = ActiveWorkbook.Worksheets("Persistent")
End Sub

and in a standard module:

并在标准模块中：

Public shT As Worksheet
Public shA As Worksheet
Public shC As Worksheet
Public shM As Worksheet
Public shP As Worksheet

Sub durAL()
    MsgBox shT.Range("A1").Value
End Sub

running durAL will demonstrate the globals are available for common use.

运行durAL 将证明全局变量可供通用使用。