CODEWITHSUNDEEP
excelexcel-formulavba
Tsaukpaetra
Tsaukpaetra

Reputation: 588

Excel SpecialCells does not work when used inside spreadsheet

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.

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)

The important code is as follows:

 For Each CheckCell In Check.SpecialCells(xlCellTypeConstants)

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.

Complete function:

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

Upvotes: 0

Views: 964

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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

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.

Upvotes: 4

Related Questions