Let’s tackle your issues one by one—these are super common pitfalls when working with VBA event procedures and subroutine arguments, so you’re not alone!

1. Fixing "Wrong Number of arguments or invalid property assignment" #

This error almost always boils down to one of two things:

• You’re calling a subroutine with more/fewer arguments than it’s defined to accept
• You’re trying to assign a value to a property incorrectly (e.g., using the wrong syntax or mismatched data type)

For example, if you had a sub like this:

Sub HandleItem(ByVal itemValue As String)
    ' Logic here
End Sub

And called it like HandleItem (no arguments) or HandleItem "Apple", 5 (extra argument), you’d trigger this error. Double-check your subroutine definitions and every place you call them to make sure argument counts and types line up.

2. Fixing "Procedure declaration does not match description of event or procedure having the same name" #

Ah, this is a classic VBA event procedure gotcha! When you work with control events (like a ListBox’s Click), you have to stick to the exact signature VBA expects for that specific event.

Standard ListBox click events must be:

• Private (not Public)
• Have no arguments (unless you’re using a custom ActiveX control with a modified Click event, which is rare)

So your code should look like this, not Public:

Private Sub ListBox1_Click()
    ' Your logic here
End Sub

VBA ties event procedures to controls by their name and signature—changing Private to Public breaks that connection, hence the compile error.

3. Implementing Variable Passing in a Loop #

Now for your core goal: passing variables from a loop to another subroutine. Here’s a complete, working example tailored to your ListBox scenario:

First, the correct ListBox Click event (stays Private):

Private Sub ListBox1_Click()
    Dim loopIndex As Integer
    Dim selectedValue As Variant ' Use Variant to handle any data type in the ListBox
    
    ' Loop through all items in the ListBox
    For loopIndex = 0 To ListBox1.ListCount - 1
        ' Check if the item is selected
        If ListBox1.Selected(loopIndex) Then
            selectedValue = ListBox1.List(loopIndex)
            
            ' Call your custom subroutine, passing both the value and index
            ProcessSelectedItem selectedValue, loopIndex
        End If
    Next loopIndex
End Sub

Then, define the subroutine that receives the parameters (can be Private or Public, depending on where you need to call it):

' Custom sub to handle each selected item
Private Sub ProcessSelectedItem(ByVal itemVal As Variant, ByVal itemIndex As Integer)
    ' Example logic: Print to the Immediate Window
    Debug.Print "Processing item at index " & itemIndex & ": " & itemVal
    
    ' Or write to a worksheet
    ' Sheet1.Cells(itemIndex + 2, 1).Value = itemVal ' +2 to skip header row
End Sub

Key Notes: #

• Make sure the number of arguments you pass to ProcessSelectedItem matches exactly what the sub is defined to accept.
• Use matching data types (or Variant for flexibility) to avoid hidden type-mismatch errors.
• Event procedures like ListBox1_Click should always stay Private unless you have a very specific reason to expose them (and even then, you’d need to adjust how VBA hooks up the event).

内容的提问来源于stack exchange，提问作者Tyler