vba - 在一个工作簿中创建适用于另一个工作簿的子例程

Question

我在一个工作簿上创建了一个按钮，可以打开另一个无宏工作簿。 transposeDataMatrices是将在打开的工作簿中的工作表上运行的子程序:

Private Sub CommandButton21_Click()
Set BEEBook = ThisWorkbook

FileSelect = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", Title:="Please select the report to import")
If FileSelect = "False" Then Exit Sub
Set ReportBook = Workbooks.Open(FileSelect)

transposeDataMatrices

End Sub

在 transposeDataMatrices 内是以下严重截断的代码( ReportBook 是 trasposeDataMatrices 中正在处理的工作簿的全局变量； cArray 是全局数组):

Public Sub transposeDataMatrices()

ReportBook.Activate

rowCounter = finWkst.UsedRange.Rows.Count
For ii = 1 To wsCount

    Worksheets(ii).Activate
    pullModelData (ii) ' just pulls some data, off ii worksheet in ReportBook

    ReDim indexIDArray(0 To 504) As Integer
    Dim j as Integer: j = 0
    For Each catName In cArray
        Dim totRange As Range
        Set catTitle = Worksheets(ii).UsedRange.Find(catName)

        If Not catTitle Is Nothing Then
        catTitle.Offset(2, 0).Offset(0, 1).Select
        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlDown)).Select
        Set totRange = Selection
        Else
        Set totRange = Cells("A1")
        EndIf
        indexIDArray(j) = j
    Next

    equipModelVersion rowCounter, indexIDArray
    rowCounter = finWkst.UsedRange.Rows.Count

Next ii

End Sub



Sub equipModelVersion(rowCounterDummy As Integer, indexArrayDummy() As String)

ReportBook.Activate
finWkst.Activate

iCol = 1:

    Set indexRange = Range(Cells(rowCounterDummy + 1, iCol), Cells(rowCounterDummy + 1 + UBound(valueArrayDummy, 1), iCol))

    Dim j As Integer: j = 0
    For I = rowCounterDummy + 1 To rowCounterDummy + 1 + UBound(valueArrayDummy, 1)
        Cells(I, iCol) = indexArrayDummy(j)
        j = j + 1
        Next

End Sub


Sub initializeWorkspace()
ReportBook.Activate
finWkst.Activate

Range("A1").Value = "IndexID"
Range("B1").Value = "ModelID"
Range("C1").Value = "UserVersion"
Range("D1").Value = "Equipment"
Range("E1").Value = "Date"

For ii = LBound(cArray) To UBound(cArray)
    Cells(1, ii + 5).Value = cArray(ii)
    Next

End Sub

我有两个问题:

首先 , 当 equipModelVersion运行时，它将值存储在按钮所在的工作表上的工作簿单元格中，而不是在为存储值而创建的工作表中，而不是打开的工作簿中。

我该如何纠正？

我尝试使用 with 语句和在 Stack Overflow 上找到的其他一些快速操作来激活该特定工作表，但没有任何效果。

其次 ，当我调试 transposeDataMatrices并单独使用，以下行有效:

   Set totRange = Range(Range(Selection, Selection.End(xlToRight).Offset(0, -1)), Selection.End(xlDown))

在我用按钮转移到工作簿的宏中，它不再起作用，所以我不得不解决它:

        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlDown)).Select
        Set totRange = Selection

只是看起来很糟糕。

当代码完全相同但扩展时，为什么 VBA 对此如此愚蠢？

Answer 1

VBA 并不聪明，它只会做你告诉它做的事情，而不是你认为你告诉它做的事情。

我的猜测是ReportBook.Worksheets(ii)正在失去焦点。

使用对象时始终声明父对象是一种很好的做法。简单的方法是将父对象声明为变量。

在这种情况下:

Dim ws as worksheet
set ws = ReportBook.Worksheets(ii)

然后有问题的两行使用 with声明

with ws
    Set totRange =.Range(Selection, Selection.End(xlDown))
end with

您应该避免使用 .select命令。见 here有关这方面的重要信息。使用它只会弄脏代码并使查找错误变得更加困难。