Application.Intersect fails when Target is in a validation list

Viewed 188

I have the function below which I use widely throughout the event procedures in several of my projects. Every once in a while it fails with a subscript error on the indicated line.

Function Insect(Target As Range, ParamArray Rng() As Variant) As Boolean

    Dim Fun     As Variant          ' range intersection / return boolean
    Dim Area    As Range            ' Range.Area
    Dim i       As Integer          ' index
    
    For i = 0 To UBound(Rng)
        For Each Area In Rng(i).Areas
            Set Fun = Application.Intersect(Target, Area)  ' failure occurs here
            Fun = Not Fun Is Nothing
            If Fun Then Exit For
        Next Area
        If Fun Then Exit For
    Next i
    Insect = Fun
End Function

As far as I recall, all failures have occurred when the function was called from an event procedure that responded to a change in a cell with data validation, meaning (in the context of the above procedure) with a simple range of a single cell and therefore a single area, nothing as exotic as the function is capable of handling. In each case tests showed that both ranges were available (checked addresses and values) and, for all appearances, the function should have worked. In each case it did work when the action was repeated. It's difficult to test to a conclusion because I can't generate the error at will.

I now believe it is caused by imprecise mouse action, that somehow the click doesn't come exactly on one item in the dropdown list or perhaps slips to an adjacent cell in the time after a selection is made but before it is shown in the cell. This theory would entail that the change event occurs before the validation is completed, meaning before the change has been "approved". This theory would then conclude that Application.Intersect would also fail if it were not bound into an unnecessarily complex function. Hence my questions:-

  1. Does anyone have experience with Application.Intersect misfiring in connection with data validation dropdowns?
  2. Can anyone tell me the sequence of events here - between validation and change - with the view of correcting the error before it occurs. Is there a way to check if the clicking was accurate?
  3. Would someone encourage (or discourage) me to try and capture the error after it happened, such as simply re-submitting the same ranges to the same function another time.

The question to answer in the last case would be whether to do this within the function or in the event procedure. Since both ranges are available in the function I'm tempted to just trap the error and run the same data again. But since I don't know what causes the error I might create an endless loop. Perhaps a cooling period with DoEvents would help?

Better understanding of the sequence of actions and events might lead me to a way to repair the damaged data that cause the malfunction. This might be easier to do in the event procedure. For example, if one of the ranges is lacking information about its parent that wouldn't be available in the function but easily supplied in the event procedure - if I would know in which way the range object might be corrupted.

Any ideas? All suggestions welcome.

Based on @FaneDuru's suggestion I amended the function as follows.

Dim Fun     As Boolean          ' return boolean
Dim Sect    As Range            ' intersection range
Dim Area    As Range            ' Range.Area
Dim i       As Integer          ' index

For i = 0 To UBound(Rng)
    For Each Area In Rng(i).Areas
        Set Sect = Application.Intersect(Target, Area)
        Fun = Not Sect Is Nothing
        If Fun Then Exit For
    Next Area
    If Fun Then Exit For
Next i
Insect = Fun

Unfortunately, that didn't seem to make a difference. The next crash occurred again on the same line (Set Sect = Application.Intersect(Target, Area) but I thought that the error message was a different one, "Object doesn't support this property or method". Target.Address, Rng(i).Address, and Area.Address, printed to the Immediate window, were all as expected. The only irregularity I found was that Target appeared blank (forgot to check if it contained an error). Try as I might, I was unable to replicate the error in the same location with the same or different data. My tests included entering "" in the trigger cell and no offence was taken. I still think it's significant that the trigger cell has data validation (the list is extracted from a different worksheet in the same workbook on the fly and might therefore arrive with a slight delay - not noticable to me).

The only other application running at the time was MS Edge (and VBE). Have amended the procedure to specify Set Sect = Excel.Application.Intersect(Target, Area) and will continue using the function until it perhaps crashes again.

1 Answers

The issue was resolved by buying a new mouse. It turned out that the flicker I had observed wasn't caused by imprecise clicking but by imprecise interpretation of the click. The entire problem disappeared when the new mouse took up its duty. That occurred more than 3 months ago and the same code has been working faultlessly ever since.

Related