在电子表格中使用Excel SpecialCells时不起作用 [英] Excel SpecialCells does not work when used inside spreadsheet

问题描述

我编写了一个VBA函数，以尝试使用数组公式绕过条件检查单元格范围并返回一些列偏移量.基本上，它是一个Sumif，而不是返回总和，而是返回将被求和的一系列单元格.

I wrote a VBA function in an attempt to get around using Array Formulas to check a range of cells against a condition and return some column offset. It's basically a Sumif that, instead of returning a sum, returns a range of cells that would have been summed up.

我遇到的问题是，在工作表中调用该代码与在另一个函数中调用时，代码的运行方式不同，特别是.SpecialCells在需要时不会限制范围.例如，在代码中，我仅对具有公式或常量的单元格进行比较，这很好地限制了从另一个宏或即时窗口调用时执行的计算，但是如果我将其作为公式输入到工作表中，完全没有限制(如果我指定整个列作为比较，即使该列中的大多数单元格为空白，它仍然会遍历所有1048576单元格)

The problem I'm encountering is that the code runs differently when being called inside a worksheet versus another function, specifically the .SpecialCells does not limit the range when needed. For example, in the code I perform the comparison on only cells that have formulas or constants, and this works fine to limit the calculations performed when calling from another macro or the immediate window, but if I enter it as a formula in a sheet, it does not limit it at all (if I specified the entire column as a comparison, even if most of the cells in the column are blank it still goes through all 1048576 cells)

重要代码如下:

 For Each CheckCell In Check.SpecialCells(xlCellTypeConstants)

对于Check as Range("A:A")，理想情况下可以运行132个其中具有常量的单元格，而运行整个列.

For Check as Range("A:A"), This ideally would run for, say 132 cells that have a constant in it, but instead runs the entire column.

任何想法如何使它更合理地工作?其余的代码可以很好地满足我的需求，我只是不想让它花几秒钟来计算使用此公式的每个单元格的整个列.

Any ideas how to get this to work more sensibly? The rest of the code works very well for what I need, I just don't want to have it spending several seconds calculating the entire column for every cell this formula is used in.

完整功能:

Function RangeIf(returnColumn As Range, Check As Range, Condition As String) As Range
    'Exit Function
    Dim Operator As Integer, HasOperator As Boolean, TheColumn As String, CheckCell As Range, Passed As Boolean, ReturnRange As Range
    HasOperator = True
    Operator = 0
    TheColumn = Mid(returnColumn.Cells(1, 1).Address, 2)
    TheColumn = "$" & Mid(TheColumn, 1, InStr(1, TheColumn, "$"))
    While HasOperator
        Select Case Mid(Condition, 1, 1)
            Case "<"
                Operator = Operator Or 1
                Condition = Mid(Condition, 2)
            Case ">"
                Operator = Operator Or 2
                Condition = Mid(Condition, 2)
            Case "="
                Operator = Operator Or 4
                Condition = Mid(Condition, 2)
            Case Else
                HasOperator = False
        End Select
    Wend
    For Each CheckCell In Intersect(Check, Check.Parent.UsedRange).Cells
        Passed = False
        'UpdateStatusBar "Processing Cell: " & CheckCell.Address
            Select Case Operator
                Case 0, 4    'No op or Equals
                    If CheckCell.Value = Condition Then Passed = True
                Case 1    ' Less than
                    If CheckCell.Value < Condition Then Passed = True
                Case 2    ' Greater than
                    If CheckCell.Value > Condition Then Passed = True
                Case 3    ' Not
                    If CheckCell.Value <> Condition Then Passed = True
                Case 5    ' Less or Equal
                    If CheckCell.Value <= Condition Then Passed = True
                Case 6    ' Greater or Equal
                    If CheckCell.Value >= Condition Then Passed = True
            End Select
            If Passed Then
                If Not ReturnRange Is Nothing Then
                    Set ReturnRange = Union(ReturnRange, Range(TheColumn & CheckCell.Row))
                Else
                    Set ReturnRange = Range(TheColumn & CheckCell.Row)
                End If
            End If
    Next CheckCell

    Set RangeIf = ReturnRange
End Function

推荐答案

SpecialCells在UDF中不起作用.这是Excel的局限性.这是在UDF中不起作用的内容的列表.

SpecialCells does not work in UDFs. It's a limitation of Excel. Here's a list of things that don't work in UDFs.

http://www.decisionmodels.com/calcsecretsj.htm

您必须逐个遍历单元格.开始我的限制检查，只检查UsedRange.

You have to loop through the cells individually. Start my limiting Check to only the UsedRange.

For Each CheckCell in Intersect(Check, Check.Parent.UsedRange).Cells

这可能会使它保持在一百万以下.您可以进一步减少它，但这将取决于您的具体情况.

That will keep it under a million, probably. You can reduce it further, but it will be specific to your situation.

这篇关于在电子表格中使用Excel SpecialCells时不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！