在VBA Sub过程中调用自定义函数出现编译错误，求解决方案

Let's walk through fixing your code step by step—first addressing that compilation error, then cleaning up the row copy logic:

1. Root Cause of the Compilation Error #

Your custom function NINOFunction isn't properly assigning its return value. You're using NINO = ... instead of the function's actual name. VBA throws a compilation error here because it doesn't recognize NINO as the return variable for this function.

Fix the function like this:

Function NINOFunction(sInp As String) As Boolean
    Const s1 As String = "[AaBbCcEeGgHhJjKkLlMmNnOoPpRrSsTtWwXxYyZz]"
    Const s2 As String = "[AaBbCcEeGgHhJjKkLlMmNnPpRrSsTtWwXxYyZz]"
    Const s3 As String = "######"
    Const s4 As String = "[AaBbCcDd]"
    ' Assign the result to the FUNCTION NAME, not a random variable
    NINOFunction = sInp Like s1 & s2 & s3 & s4
End Function

2. Fixing the Row Copy Logic #

Your current copy code has two critical issues:

• Range(NINumber) is redundant (NINumber is already a Range object)
• Cells(2, x) uses a boolean value x as a column index, which will only ever target column 1 (True=1) or 2 (False=2)—that's not what you want for appending rows to the Validation sheet.

Here's the corrected CheckNINO sub, with a counter to track where to paste invalid rows:

Sub CheckNINO()
    Dim NINumber As Range, NINumbers As Range
    Dim Usdrws As Long, x As Boolean
    Dim destRow As Long ' Counter for the Validation sheet's next empty row
    
    ' Turn off screen updating to speed up the macro and avoid flicker
    Application.ScreenUpdating = False
    
    Usdrws = Worksheets("EeeDetails").Range("C" & Rows.Count).End(xlUp).Row
    Set NINumbers = Worksheets("EeeDetails").Range("Q2:Q" & Usdrws)
    destRow = 2 ' Start pasting at row 2 of the Validation sheet
    
    For Each NINumber In NINumbers
        ' Skip empty cells to avoid false negatives (optional but recommended)
        If NINumber.Value <> "" Then
            x = NINOFunction(NINumber.Value)
            If x = False Then
                ' Copy the entire row of the invalid NINO to the Validation sheet
                NINumber.EntireRow.Copy Destination:=Worksheets("Validation").Cells(destRow, 1)
                destRow = destRow + 1 ' Move to the next empty row for the next invalid entry
            End If
        End If
    Next
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
End Sub

Key Improvements Explained: #

• Proper Return Value Assignment: Fixed the function to assign its result to NINOFunction (the function's name), eliminating the compilation error.
• Row Counter: Used destRow to track the next empty row on the Validation sheet, so each invalid row appends below the last one instead of overwriting row 2.
• Redundant Range Fix: Replaced Range(NINumber) with NINumber.EntireRow since NINumber is already a Range object.
• Empty Cell Check: Added a check to skip empty cells in column Q, so you don't copy rows with no NINO value.
• Performance Optimization: Disabled screen updating during the macro to make it run faster and avoid distracting screen flicker.

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