在 Excel VBA 中处理范围内的列

debugcn 发表于 Dev

詹姆斯·柯兰

我有一个比较两列并返回 true 或 false 的函数。我有另一个将范围作为输入的函数，并且应该对该范围内的列进行所有成对比较。

但是，我似乎在从范围中提取（和存储）列时画了一个空白。当我要求第 i 列时，该函数退出。这是我的代码：

Function CompareAllColumns(r As Range, o As Range)
Dim numCols As Integer
Dim i As Integer
Dim j As Integer
Dim col1 As Range
Dim col2 As Range
Dim Matches As Integer

Matches = 0
numCols = r.Columns.Count
Dim ac1 As String
Dim ac2 As String
Dim a As String

a = r.Address

For i = 1 To numCols - 1
    col1 = r.Columns(i).Select
    ac1 = col1.Address

    For j = i + 1 To numCols
        col2 = r.Columns(j).Select

        If (Compare(col1, col2)) Then
            o.Value = "Columns " & i & " and " & j & " are the same"
            o = o.Offset(1).Select
            Matches = Matches + 1
        End If
    Next
Next

CompareAllColumns = Matches
End Function

它在线上退出col1 = r.Columns(1).Select-Select实验性地存在，但对正确执行没有影响。

YoE3K

你必须Set对象，你不能对Let它们使用默认值。

此外，由于这似乎是一个 UDF（基于您的评论“它在线上退出col1 = r.Columns(1).Select”，而不是您说它在那条线上崩溃了），您需要注意您的代码将不被允许Excel 单元格的更改，而不是通过从函数返回值。

Function CompareAllColumns(r As Range, o As Range)
    Dim numCols As Integer
    Dim i As Integer
    Dim j As Integer
    Dim col1 As Range
    Dim col2 As Range
    Dim Matches As Integer

    Matches = 0
    numCols = r.Columns.Count
    Dim ac1 As String
    Dim ac2 As String
    Dim a As String

    a = r.Address

    For i = 1 To numCols - 1
        'use Set for an object
        Set col1 = r.Columns(i)
        ac1 = col1.Address

        For j = i + 1 To numCols
            'use Set for an object
            Set col2 = r.Columns(j)

            If Compare(col1, col2) Then
                'You can't set values within a UDF
                'o.Value = "Columns " & i & " and " & j & " are the same"
                'You can't "Select" things within a UDF
                'o = o.Offset(1).Select
                Matches = Matches + 1
            End If
        Next
    Next

    CompareAllColumns = Matches
End Function

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。