Help (!) with Range.Precedents

[Indystick]

Hi all,

I have a sub that works correctly using Range.Precedents, but when I try and pass a range to it from a function, returns the incorrect results. Here is a simplified version of what I am trying to do:

Imagine in cell A3 I have the formula =A2 + A2 and I have the following sub in ThisWorkbook:

Sub MySub()    
    Dim myRange As Range
    Dim rngPrecedents As Range
    Dim rngPrecedent As Range
    
    Set myRange = Worksheets("Sheet1").Range("A3")
    Set rngPrecedents = myRange.Precedents
    
    For Each rngPrecedent In rngPrecedents
        Debug.Print rngPrecedent.Address
    Next
        
    
End Sub

The sub will print $A$1 AND $A$2 in the immediate window when it is run. So far so good.

Now imagine, I create function that receives a cell reference and passes it as a range to the sub I created above (with a minor modification):

Function FORMULATEST(testCell As Range) As Boolean    Dim cell As Range
    Set cell = testCell
    Call ThisWorkbook.MySub(cell)
    FORMULATEST = True
End Function

and I have modified my sub to receive the range captured by the function:

Public Sub MySub(cellAddress As Range)    Dim myRange As Range
    Dim rngPrecedents As Range
    Dim rngPrecedent As Range
    
    Set myRange = cellAddress
    Set rngPrecedents = myRange.Precedents
    
    For Each rngPrecedent In rngPrecedents
        Debug.Print rngPrecedent.Address
    Next
        
    
End Sub

Now the Debug.Print prints the range passed to the sub, not the cell references of the formula of that range. In other words, if I put =FORMULATEST(A3) in a cell, the sub prints $A$3, not $A$1 and $A$2 as it should.

I, for the life of me, cannot figure out what the problem is. It seems like it should work.

 

[Marcelo Branco]

The Precedents property doesn't work in UDFs, that is when a function is called by a formula in a cell.

See the topic UDF action being ignored in
http://www.decisionmodels.com/calcsecretsj.htm

M.

 

[Indystick]

Thank you Marcelo, that was good information. Can you think of a workaround?

 

[Marcelo Branco]

You should use a Sub...

M.

 

[Indystick]

Right, which is why I used the precedents in MySub.

My problem is, I need to tell the Sub what cell to get the precedents, and then I am going to evaluate them and return a Boolean to the worksheet; which I don’t think I can do from a Sub.

 

[Marcelo Branco]

Maybe you can select the cell and in the sub use something like this

Dim bResult as Boolean

With Selection
   'code
   'code
   Range("A1") = bResult 'for example
End With

M.