arrays - Excel VBA - 使用在另一个模块中创建的数组

Question

我正在尝试创建一个工作簿，允许用户导入某种主数据，导入一些要与主数据进行比较的数据，并最终对这些数据进行分析。主数据和导入数据都可以是一个工作簿（即多个工作表）。

我的想法是将工作表分组为一个主工作表数组和一个导入的工作表数组，以便我可以在分析代码中引用它们。目前，我已经设法在两个单独的模块中创建了这两个数组（2 个单击按钮），但是我无法在第三个模块中使用这些数组。

有没有办法做到这一点？还是我一开始就使用这种方法的想法是错误的？

以下是我的代码。

Sub ImportMaster_Click()

Dim sImportFile As String, sFile As String, sSheetName As String
Dim sThisBk As Workbook
Dim wbBk As Workbook
Dim wsSht As Worksheet
Dim vfilename As Variant
Dim Mshtarray()
Dim MshtName As String
Dim lSheetNumber As Long
Dim lshtcount As Long
Dim iMshtcount As Integer
Dim x As Integer
Dim y As Integer

'import data that you want to compare with master data

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename(Title:="Open File")
If sImportFile = "False" Then
    MsgBox "No File Selected"
    Exit Sub
Else
    vfilename = Split(sImportFile, "\")
    sFile = vfilename(UBound(vfilename))
    Application.Workbooks.Open Filename:=sImportFile


    Set wbBk = Workbooks(sFile)

    With wbBk
        lSheetNumber = wbBk.Worksheets.Count
        If lSheetNumber > 1 Then

           x = 0
            For iMshtcount = 1 To lSheetNumber
                x = x + 1
                ReDim Preserve Mshtarray(0 To iMshtcount)
                Mshtarray(x) = wbBk.Sheets(x).Name

            Next

            If IsArray(Mshtarray) = True Then

                For y = 1 To x
                    If Mshtarray(y) <> "Import page" Then
                            lshtcount = sThisBk.Worksheets.Count
                            wbBk.Sheets(Mshtarray(y)).Copy after:=sThisBk.Sheets(lshtcount)


                    End If
                Next
            Else
                MsgBox "Array error"
            End If
            sThisBk.Sheets("Import page").Select

        ElseIf lSheetNumber = 1 Then

            MshtName = ActiveSheet.Name

            If SheetExists(MshtName) Then
                Set wsSht = .Sheets(MshtName)
                wsSht.Copy after:=sThisBk.Sheets("Import page")
            Else
                MsgBox "There is no Sheet with name :  in:" & vbCr & .Name
            End If
            sThisBk.Sheets("Import page").Select

        Else

            MsgBox "Error, no worksheet opened"


        End If


        wbBk.Close SaveChanges:=False



    End With
End If



Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Public Function SheetExists(ByVal sWSName As String, Optional wb As Workbook) As Boolean

Dim sht As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
Set sht = wb.Sheets(sWSName)
On Error GoTo 0
SheetExists = Not sht Is Nothing

End Function

导入数据的模块与上面的类似。问题是我要比较数据的第三个模块。

Sub Reporting_Click()

Dim wbBk As Workbook
Dim wsSht As Worksheet
Dim firstRow As Long
Dim lastRow As Long
Dim lastvisRow As Long
Dim readN3 As Integer
Dim maxN3 As Integer
Dim shtcount As Integer
Dim fltrng As Range
Dim a As Long


Set wbBk = ActiveWorkbook
Set wsSht = ThisWorkbook.Sheets
'vMshtname = ThisWorkbook.Sheets(Module1.Mshtarray(y)) <----- I have error for calling such array here
'vImshtname = ThisWorkbook.Sheets(Module3.Imshtarray(j))

Application.DisplayAlerts = False

For Each wsSht In wkbk.Worksheets
 shtcount = ThisWorkbook.Worksheets.Count
 Set wsSht = ThisWorkbook.Worksheets <---- problem for setting worksheets as well since I can see wsSht is still nothing after running this line 


with wsSht
    a = 2
    For a = 2 To a = shtcount <---- ******

    If ThisWorkbook.Sheets(a).Name <> "Import page" Then

        wsSht.AutoFilterMode = False
        lastRow = wsSht.UsedRange.Rows.Count
        wsSht.Range("D6").AutoFilter Field:=4, Criteria1:=">=0", Operator:=xlAnd, Criteria2:="<>="


        Set fltrng = wsSht.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
        firstRow = fltrng.Range("E1").End(xlUp).Row
        lastvisRow = fltrng.Range("E1").End(xlDown).Row
        readN3 = Application.WorksheetFunction.Max(Range("E" & firstRow, "E" & lastvisRow))
        maxN3 = 0
        If maxN3 < readN3 Then
            maxN3 = readN3
        End If

    Else

        MsgBox "No data available for analysis"
    End If

Next

End With

即使我只是想尝试for循环中的代码，VBA也只能运行到带有******的那一行，然后直接跳到end sub。有人可以帮我吗？

标签： arraysexcelvba

Answer 1

控制变量缺失（误用）

我已经纠正了错误，但我不知道您要对数组做什么。试着解释一下。

Option Explicit

Sub Reporting_Click()

Application.DisplayAlerts = False

Dim wbBk As Workbook
Dim wsSht As Worksheet
Dim firstRow As Long
Dim lastRow As Long
Dim lastvisRow As Long
Dim readN3 As Integer
Dim maxN3 As Integer
Dim shtcount As Integer
Dim fltRng As Range
Dim a As Long

'vMshtname = ThisWorkbook.Sheets(Module1.Mshtarray(y)) <----- I have error for calling such array here
'vImshtname = ThisWorkbook.Sheets(Module3.Imshtarray(j))

Set wbBk = ActiveWorkbook

' shtcount = wbBk.Worksheets.Count
' For a = 2 To shtcount
'     With wbBk.Worksheets(a)
'
'     End With
' Next

' Or
For Each wsSht In wbBk.Worksheets
    With wsSht
        If .Name <> "Import page" Then
            .AutoFilterMode = False
            lastRow = .UsedRange.Rows.Count
            .Range("D6").AutoFilter Field:=4, Criteria1:=">=0", _
                    Operator:=xlAnd, Criteria2:="<>="
            Set fltRng = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
            With fltRng.Range("E1")
                firstRow = .End(xlUp).Row
                lastvisRow = .End(xlDown).Row
            End With
            readN3 = Application.WorksheetFunction _
                    .Max(Range("E" & firstRow, "E" & lastvisRow))
            maxN3 = 0
            If maxN3 < readN3 Then
                 maxN3 = readN3
            End If
          Else
            MsgBox "No data available for analysis"
        End If
    End With
Next

结束子