VBA数组创建失败、去重异常及下标越界问题求助

Hey there! Let's work through your two VBA issues step by step—since you're new to arrays, I'll make sure to explain everything clearly so you understand what's going wrong and how to fix it.

First: Fixing the "Subscript out of range" Error #

That error almost always pops up with arrays when either:

• You declared a fixed-size array that's too small for the data you're trying to store, or
• You're using a dynamic array but forgot to resize it before adding new elements.

Since you're iterating through data and adding elements as you go, dynamic arrays are the way to go here. Unlike fixed-size arrays, you can resize them on the fly with ReDim Preserve (the Preserve part makes sure you don't lose the data you've already added). Also, remember that VBA arrays start at index 1 by default (unless you set Option Base 0 at the top of your module)—so if you try to access index 0 without that setting, you'll get the same error.

Second: Fixing the Duplicate Removal Issue (You were right about spaces!) #

Hidden leading or trailing spaces are one of the most common reasons deduplication fails. Two values that look identical (like "Jan" and " Jan ") are treated as different strings by VBA. The fix here is simple: use the Trim() function to strip whitespace from both ends of every value before you compare or store it.

If you also need to handle case sensitivity (like "Jan" vs "JAN" being considered duplicates), you can wrap the values in LCase() or UCase() to make the comparison case-insensitive too.

Corrected Code Example #

Here's a revised version of your code (I filled in common parts since you shared a snippet) that fixes both issues:

Option Explicit ' Always add this! It catches typos and undeclared variables

Sub FixArrayAndDeduplication()
    Dim MoNam() As String ' Dynamic array (no fixed size)
    Dim k As Integer, lastRow As Integer, arrPosition As Integer
    Dim currentVal As String
    Dim isDuplicate As Boolean
    
    ' Get the last row with data (adjust column "A" to match your data)
    lastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    
    arrPosition = 1 ' Start at index 1 (VBA's default array start)
    
    ' Loop through your data (assuming row 1 is a header)
    For k = 2 To lastRow
        currentVal = Trim(Sheet1.Cells(k, "A").Value) ' Strip whitespace first!
        
        ' Skip empty cells to avoid clutter
        If currentVal <> "" Then
            isDuplicate = False
            
            ' Check if the value is already in the array
            For i = 1 To arrPosition - 1
                ' Compare trimmed values to avoid space-related duplicates
                If Trim(MoNam(i)) = currentVal Then
                    isDuplicate = True
                    Exit For ' No need to check further once a duplicate is found
                End If
            Next i
            
            ' If not a duplicate, add it to the array and resize
            If Not isDuplicate Then
                ReDim Preserve MoNam(1 To arrPosition) ' Resize array to fit new element
                MoNam(arrPosition) = currentVal
                arrPosition = arrPosition + 1 ' Move to next empty spot in array
            End If
        End If
    Next k
    
    ' Example: Print the deduped array to column B
    For k = 1 To UBound(MoNam)
        Sheet1.Cells(k, "B").Value = MoNam(k)
    Next k
End Sub

Quick Breakdown of Key Fixes: #

1. Option Explicit: This is a must-have—it forces you to declare all variables, which prevents silly bugs from typos (like misspelling MoNam as MoNam1).
2. Dynamic Array: Dim MoNam() As String lets us resize the array as we add elements, so we never hit that "subscript out of range" error.
3. ReDim Preserve: Resizes the array while keeping all existing data—without this, you'd lose everything you added before when resizing.
4. Trim(): Strips leading/trailing spaces from every value before comparing or storing it, fixing the duplicate issue you noticed.
5. Duplicate Check Loop: We loop through the existing array elements to make sure we only add values that aren't already there.

Extra Tips: #

• If you need case-insensitive deduplication, change the comparison line to:

  If LCase(Trim(MoNam(i))) = LCase(currentVal) Then
  

• If your data has unwanted spaces in the middle (like "Jan uary"), you can use Replace(currentVal, " ", "") to remove all spaces—but only do this if that makes sense for your use case.

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