VBA 声明的变量不允许作为对象模块的公共成员

Question

已解决

"全局"索引变量和数组必须位于模块中，并确保 ThisWorkbook 和不同的工作表不包含任何这些变量。

---

你好这是我第一次在这里提问。我只在 VBA 中编程了大约 3 周，并且之前没有编程经验。

问题：

我的问题是是否可以将索引变量和数组声明为 Public 并且可以由多个子例程使用，如果是这样，如何？

问题：

我一直在尝试将索引变量和数组声明为 Public。当我运行第一个子"公共子数组ToFinnish（）"时没有问题，我在这个子中重新分配数组。当我从"公共子数组到芬兰语（）"，"私有子"中调用另一个子时，出现以下错误消息并我收到一条错误消息，指出：

"编译错误：

常量、固定长度字符串、数组、用户定义类型和 Declare 语句不允许作为对象模块的公共模因"

我的数组"arrData（） 作为变体"也会发生同样的情况

 Option Explicit
Public wb As Workbook
Public Cell As Range
Public i1 As Long
Public A(1 To 4) As String    '<---- Indexed Variable
Public arrRow As Long
Public arrData() As Variant   '<---- Array
Public Sub ArrayToFinnish()
    Dim i2 As Long
    Dim j1 As Long, j2 As Long
    Dim Cell As String
    Dim lRow As Long
    Dim lCol As Long
    Dim rng As Range
    Dim aCell As Range
    A(1) = "String1"
    A(2) = "String1"
    A(3) = "String1"
    A(4) = "String1"

错误消息如下所示：

"编译错误"窗口

感谢您提供的任何帮助！

//亨利

根据要求，我将提供所有代码。调用 KN 时错误标记为 <-----------错误

Option Explicit
Public wb As Workbook
Public Cell As Range
Public i1 As Long
Public A(1 To 4) As String
Public arrRow As Long
Public arrData() As Variant
'Public ListGroup()
Public Sub ArrayToFinnish()
    'Dim A(1 To 4) As String
    'Dim i1 As Long
    Dim i2 As Long
    Dim j1 As Long, j2 As Long
    'Dim arrData() As Variant
    Dim Cell As String
    Dim lRow As Long
    Dim lCol As Long
    Dim rng As Range
    Dim aCell As Range
    A(1) = "Ship Via Description"
    A(2) = "Speditor"
    A(3) = "Planned Ship Date/Time"
    A(4) = "Weight"
    'A(4) = "Customer Order"
    'A(5) = "Customer Number"
    Sheet1.Activate

    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))
    ReDim arrData(1 To lRow, 1 To UBound(A, 1))
    'ListGroup = arrData(1 To lRow, 1 To Ubound(A,1))

    For i1 = 2 To lRow
        For j1 = 1 To UBound(A, 1)
            Set aCell = rng.Find(A(j1))
            Cell = Sheet1.Cells(i1, aCell.Column).Value
            Select Case Cell
                 Case Cell = "EXPRESS"
                 Case Cell = "TRUCK"
                 Case Cell = "CZ/DACHSER/Axis Communications LLC"
                 Case Cell = "DE/ASH Logistik/Abris"
                 Case Cell = "DE/EXP Cargo/RRC Cent. Asia"
                 Case Cell = "HU/Trans-Gate/IQ Trading"
                 Case Cell = "USA/Atlanta/Splitpoint"
                 Case "AIRFREIGHT"
                    arrRow = arrRow + 1
                    KN           <-----------**Error when calling KN**
                 Case Cell = "China/Shanghai/Splitpoint"
                 Case Cell = "Singapore/KN/CDP"
                 Case Cell = "US/Geodis/Miami"
                 Case Cell = "BR/Sao Paulo/Splitpoint"
                 Case Cell = "Japan / Multitek / Warehouse"
            End Select
        Next j1
    Next i1
End Sub
Private Sub KN()
    'DELETE should be global
    Dim wb As Workbook
    Set wb = ThisWorkbook
    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    'DELETE
    Dim ws As Worksheet
    Dim j3 As Long
    Dim KCellD As Range, KCellW As Range
    Dim D As Date

    Set ws = wb.ActiveSheet

    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))
    Set KCellD = rng.Find(A(3))
    Set KCellW = rng.Find(A(4))
    With ws

        D = Sheet1.Cells(i1, KCell.Column)
        Select Case D
            Case DateAdd("d", 1, Date)
                If .Cells(i1, KCellW.Column).Value >= 50 Then
                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value
                    Next j2
                End If

            Case DateAdd("d", 2, Date)
                If .Cells(i1, KCellW.Column).Value >= 1000 Then
                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value
                    Next j2
                End If
            Case Else
        End Select

End Sub

Answer 1

如果您在同一个模块中使用两个子例程，那么只需在所有模块上方的Option Explicit部分中声明它们就足够了。

我对代码进行了一些修改，并将一些变量声明移到了Option Explicit部分。

请查看下面的代码，我能够进入 KN 子例程。但是，检查KCell.Column值时出错，因为您没有设置 KCell 范围（请参阅下面的代码，我标记了错误现在的位置）

Option Explicit
Dim wb                                  As Workbook
Dim Cell, rng                           As Range
Dim A(1 To 4)                           As String
Dim arrData()                           As Variant
Dim arrRow, lRow, lCol                  As Long
Dim i1, i2, j1, j2                      As Long
'Public ListGroup()

Public Sub ArrayToFinnish()
    Dim Cell As String
    Dim aCell As Range
    A(1) = "Ship Via Description"
    A(2) = "Speditor"
    A(3) = "Planned Ship Date/Time"
    A(4) = "Weight"
    'A(4) = "Customer Order"
    'A(5) = "Customer Number"
    Sheet1.Activate
    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))
    ReDim arrData(1 To lRow, 1 To UBound(A, 1))
    'ListGroup = arrData(1 To lRow, 1 To Ubound(A,1))
    For i1 = 2 To lRow
        For j1 = 1 To UBound(A, 1)
            Set aCell = rng.Find(A(j1))
            Cell = Sheet1.Cells(i1, aCell.Column).Value
            Select Case Cell
                 Case Cell = "EXPRESS"
                 Case Cell = "TRUCK"
                 Case Cell = "CZ/DACHSER/Axis Communications LLC"
                 Case Cell = "DE/ASH Logistik/Abris"
                 Case Cell = "DE/EXP Cargo/RRC Cent. Asia"
                 Case Cell = "HU/Trans-Gate/IQ Trading"
                 Case Cell = "USA/Atlanta/Splitpoint"
                 Case "AIRFREIGHT"
                    arrRow = arrRow + 1
                    KN
                 Case Cell = "China/Shanghai/Splitpoint"
                 Case Cell = "Singapore/KN/CDP"
                 Case Cell = "US/Geodis/Miami"
                 Case Cell = "BR/Sao Paulo/Splitpoint"
                 Case Cell = "Japan / Multitek / Warehouse"
            End Select
        Next j1
    Next i1
End Sub
Private Sub KN()
    Dim ws                              As Worksheet
    Dim KCell, KCellD, KCellW           As Range
    'Dim j3                              As Long
    Dim D                               As Date
    Set wb = ThisWorkbook
    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

    Set ws = wb.ActiveSheet
    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))
    Set KCellD = rng.Find(A(3))
    Set KCellW = rng.Find(A(4))
    With ws
        ' ****** Getting an error here , you are not setting KCell Range ******
        D = .Cells(i1, KCell.Column)
        Select Case D
            Case DateAdd("d", 1, Date)
                If .Cells(i1, KCellW.Column).Value >= 50 Then
                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value
                    Next j2
                End If
            Case DateAdd("d", 2, Date)
                If .Cells(i1, KCellW.Column).Value >= 1000 Then
                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value
                    Next j2
                End If
            Case Else ' not sure why need, you are not using it
        End Select
    End With
End Sub