将变量传递给for循环中的另一个子

Question

在下面显示的我的代码中有以下错误，

编译错误

错误的参数数量或无效属性分配

Public Sub ListBox1_Click()
    Dim ShNameRow As Integer
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            Set T1 = Range("A" & i + 13).Resize(26 - i, 1)
            Set T2 = Range("E" & i + 13).Resize(26 - i, 2)
            Set T3 = Range("K" & i + 13).Resize(26 - i, 1)
            Set y = Application.Union(T1, T2, T3)
            y.Select
            Selection.Copy

            ShNameRow = 13 + i
            'Error with passing this variable
            CopyData_Click (ShNameRow)
        End If
    Next i
End Sub
Private Sub CopyData_Click()
    'Testing variable
    MsgBox (ShNameRow)
End Sub

-

更新

我尝试了将变量从一个子传递到另一个子的示例，因为我觉得更适用于我的代码。

public sublistbox1_click((

Dim ShNameRow As Integer
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        Set T1 = Range("A" & i + 13).Resize(26 - i, 1)
        Set T2 = Range("E" & i + 13).Resize(26 - i, 2)
        Set T3 = Range("K" & i + 13).Resize(26 - i, 1)
        Set y = Application.Union(T1, T2, T3)
        y.Select
        Selection.Copy

        ShNameRow = 13 + i
        'Error with passing this variable
        CopyData_Click (ShNameRow)
    End If
Next i
End Sub
Private Sub CopyData_Click(ListboxShNameRow As Integer)
MsgBox (ListboxShNameRow)
End Sub

带来了另一个编译错误，以下面的块引用显示

Private Sub CopyData_Click(ListboxShNameRow As Integer)

过程声明不匹配具有相同名称的事件或过程的描述

Answer 1

编辑在OP问题编辑和澄清之后

所以CopyData_Click是一个按钮事件处理程序...

然后，您必须找到在ListBox1_Click()内部设置变量并在CopyData_Click()

中使用它的方法

1(使用A 模块范围变量

将 ShNameRow声明为模块范围变量，即在同一模块内的任何子/函数内"看到"的变量(在您的情况下是您的用户表单代码模块

这样做只将Dim ShNameRow As Integer放在模块的顶部

然后只需致电CopleData_Click通过没有参数(其 signature 需要(

    Option Explicit
    Dim ShNameRow As Integer
    Public Sub ListBox1_Click()
        Dim i As Integer
        Dim T1 As Range, T2 As Range, T3 As Range, y As Range
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = True Then
                Set T1 = Range("A" & i + 13).Resize(26 - i, 1)
                Set T2 = Range("E" & i + 13).Resize(26 - i, 2)
                Set T3 = Range("K" & i + 13).Resize(26 - i, 1)
                Set y = Application.Union(T1, T2, T3)
                y.Select
                Selection.Copy
                ShNameRow = 13 + i
                CopyData_Click ' call 'CopyData' button 'Click' event handler
            End If
        Next i
    End Sub

    Private Sub CopyData_Click()
        MsgBox ShNameRow ' 'ShNameRow ' variable can be used inside this sub because it's a module scoped variable. i.e. it's "seen" throughout every sub or function inside the module on top of which it's declared 
        ... rest of your code (I guess you are "pasting" the already copied range)
    End Sub

2(使用 Tag UserForm对象的属性存储想要的信息

    Option Explicit

    Public Sub ListBox1_Click()
        Dim i As Integer
        Dim T1 As Range, T2 As Range, T3 As Range, y As Range
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = True Then
                Set T1 = Range("A" & i + 13).Resize(26 - i, 1)
                Set T2 = Range("E" & i + 13).Resize(26 - i, 2)
                Set T3 = Range("K" & i + 13).Resize(26 - i, 1)
                Set y = Application.Union(T1, T2, T3)
                y.Select
                Selection.Copy
                Me.Tag = 13 + i
                'Error with passing this variable
                CopyData_Click
            End If
        Next i
    End Sub
    Private Sub CopyData_Click()
        Dim ShNameRow As Integer ' declare 'ShNameRow' as a local variable, i.e. it's "seen" only inside the sub/function it's declared in
        ShNameRow = Me.Tag ''Me' is the keyword referring to the User Form object whose module code you're inside
        MsgBox ShNameRow
        ... rest of your code (I guess you are "pasting" the already copied range) 
    End Sub

Answer 2

可以通过几种方法将数据从一个子手中获取到另一个子，但是这是您要混合的两个：

全局变量

Public myGlobalVariable As String
Sub myMainSub()
    myGlobalVariable = InputBox("Enter a word:")
    myOtherSub
End Sub

Sub myOtherSub()
    MsgBox "Any sub can see 'Public' variables : " & myGlobalVariable
End Sub

将变量作为参数

传递

Sub myMainSub2()
    Dim myVariableWithinSub As String
     myVariableWithinSub = InputBox("Enter a word:")
     myOtherSub2 (myVariableWithinSub)
End Sub

Sub myOtherSub2(variableIgotFromOtherSub As String)
    MsgBox "Variables can be passed as 'Parameters' like this." & _
        vbLf & "See? : " & variableIgotFromOtherSub
End Sub

这个问题可能会关闭，因为这是您应该搜索并弄清楚的非常基本的事情，但是我知道当您不知道要搜索什么时很难开始。

在模块和实验中(分别(尝试我的示例，以便您可以看到差异。请 Google术语"全局变量Excel VBA"one_answers"通过参数Excel VBA"，以了解有关两者的更多信息。

我也强烈建议您将Option Explicit作为每个模块的第一行。这似乎是在造成错误，但通过强迫您正确宣布＆amp;处理所有变量，对象，属性等

您示例中有关代码的几件事：

• 我修复了您的缩进。缩进不会改变代码运行的方式，但它使您(和其他人(阅读和故障排除变得容易得多。

• 我也将您的Dim语句从您的循环内部移动到开始。您不希望不想一遍又一遍地奔跑的循环中(例如声明变量(。...从技术上讲，我不应该在您的问题中更改您的代码(除了诸如缩进之类的化妆品之外(，但这是习惯的力量(您可以犯的一个简单的错误！(

祝你好运！