创建包含工作表数据的公共变量

Question

简化Excel代码:

Public MyFile As String
Public varSheetA As Variant
Public SelRangeA As Range
Public wsCopy As Excel.Worksheet
Sub SelectFile_Click()
MyFile = Application.GetOpenFilename()  'Aquire filepath from user
If (MyFile <> "False") Then
Range("B1").Value = "File Found"
End If
End Sub
Sub LoadFile_Click()
Dim WbOne As Workbook
Dim strRangeToCheck As String
strRangeToCheck = "A1:T2000"
Set WbOne = Workbooks.Open(MyFile) 'Open that file
Set wsCopy = WbOne.Worksheets(1)   'Try to copy
Set varSheetA = wsCopy.Range(strRangeToCheck) 'Try to copy
Set SelRangeA = wsCopy.Range(strRangeToCheck) 'Try to copy
WbOne.Close 'This is where we lose the references & values
End Sub
Sub DisplayFile_Click()
Range("A4").Value = varSheetA(1, 1)
End Sub

这个程序的最终结果是有从工作表(1)的值在一个范围或变量数组，这样我可以编辑和显示它们的需要，并最终将值复制回原始文件。然而，当我运行这段代码时，当LoadFile_Click退出时(更具体地说，当WbOne关闭时)，我初始化的所有Public变量都是空的。

以前我的代码看起来像varSheetA = WbOne.Worksheet(1).Range(strRangeToCheck)，虽然我目前正在测试不同的方法，因为这种方式似乎不起作用。

有人发现我正在做的事情有什么根本问题吗?谢谢!

Answer 1

Option Explicit
Public MyFile As String
Public varSheetA As Variant

Sub SelectFile_Click()
    MyFile = Application.GetOpenFilename()  'Aquire filepath from user
    If (MyFile <> "False") Then
        Range("B1").Value = "File Found"
    End If
End Sub
Sub LoadFile_Click()
    Const strRangeToCheck As String = "A1:T2000"
    With Workbooks.Open(MyFile)
        varSheetA = .Worksheets(1).Range(strRangeToCheck).Value
        .Close False
    End With
End Sub
Sub DisplayFile_Click()
    Range("A4").Value = varSheetA(1, 1)
End Sub