Precedents for formulas

[Bob]

I guess, you are right, even though I am not familiar with .specialcells and
have never used it. But, after reading the help on it, I see your point. I
hope the excel people would try to make the program more user friendly by
removing all these random exceptions. At least now, thanks to Ron Rosenfeld
below, I have a solution to go by. Not the most efficient to parse, but it
works.

Bob

 

[Ron Rosenfeld]

Ron -- I learned some new VBA from your post. Thanks.

You're welcome.

In trying it out I discovered that it would not return cell references in
function calls like sqrt(A1). This is easily remedied by adding a left
parenthesis to the definition of sPat giving Const sPat As String =
"[-+/*=^&,(]".

That's one area I hadn't checked yet.

For consistency, probably best to remove both parentheses:

Const sPat As String = "[-+/*=^&,()]"

It is also possible to generate "external" address references
[Book]Sheet!cell_ref by setting the external parameter of the address property
to true:

GetRefs = GetRefs & Range(aRefs(i)).Address(external:=True) & ", "

That would also allow returning arguments that refer to other worksheets (or
workbooks).
--ron

 

[mikerickson]

One can work-around the limitations of spreadsheed UDF's by using the Calculate event.
The Calculate event fires after the UDF runs, so this kind of code:

(pseud-code)
Public collFtnCells as Collection
Public collReturnValues as Collection
Public flag as Boolean

Function myFunction()
    If flag Then
        myFunction = collReturnValues(myCallingCellAddress)
    Else
        collFtnCells.Add myCallingCell
    End If
End Function

Sub Worksheet_Calculate()
    Dim oneCell as Range
    For Each oneCell in collFtnCells
        collReturnValues(oneCellAddress) = something
    Next oneCell
    flag =True
    Application.EnableEvents = False
    Calculate
    Application.EnableEvnets = True
    flag = False
End Sub

executes in this order
UDF(first pass) - send cell addresses to Calculate event
Calculate event- take cell addresses and put returnValues in a collection
UDF(second pass) - display returnValues in the cell

In this case, this code in a normal module

Public collFtnCells As New Collection
Public collReturnValues As New Collection
Public readFromCollection As Boolean

Function PrecedentCells(inputRange As Range) As String
    Application.Volatile
    On Error Resume Next
    If readFromCollection Then
        PrecedentCells = collReturnValues(Application.Caller.Address(, , , True))
    Else
        collFtnCells.Add Item:=Application.Caller.Address(, , , True), key:=Application.Caller.Address(, , , True)
    End If
    On Error GoTo 0
End Function

in partnership with this code in ThisWorkbook's CodeModule

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim i As Long, temp As String
    If 0 < collFtnCells.Count Then
        Set collReturnValues = Nothing
        Application.EnableEvents = False
        For i = 1 To collFtnCells.Count
                temp = collFtnCells(i)
                On Error Resume Next
                collReturnValues.Add Item:=Range(temp).Precedents.Address, key:=temp
                On Error GoTo 0
        Next i
        readFromCollection = True
        Calculate
        readFromCollection = False
    End If
    Set collFtnCells = Nothing
    Application.EnableEvents = True
End Sub

=PrecedentCells(A1) will return the address of A1's precedent cells.
(Note: someCell.Precedents returns only those precedents that are on the same sheet as oneCell, off sheet precedents need to be probed with the .NavigateArrows method.)

The technique of using a UDF to pass information to the Calculate event can also be used to make UDF that will color cells or emulate ConditionFormatting for number formats or other "impossible" things.

 

[Ron Rosenfeld]

Thanks Ron. It works great so far with all the stuff that I have tested.
The only problem is that it does not return named cells as their name, but
their address. However, this is relatively easy to fix (I think). Anyway,
thanks a million. At last, someone with a useful answer. Have a great
weekend.

Bob

Glad to help. And you should make one change in one line, which came up after
lcarretto tested it on a formula with a function:

Const sPat As String = "[-+/*=^&,()]"

So far as the Name of the Named cell is concerned, I thought you specified
initially that either the name or the address would be suitable:

...and x (or D5)

In case that format was exactly how you wanted it to be, and I misunderstood
the "or", then substitute this for the defining of GetRefs:

For i = 0 To UBound(aRefs)
On Error Resume Next
GetRefs = GetRefs & aRefs(i) & " " & _
IIf(Range(aRefs(i)).Address(True, True) = aRefs(i) Or _
Range(aRefs(i)).Address(True, False) = aRefs(i) Or _
Range(aRefs(i)).Address(False, True) = aRefs(i) Or _
Range(aRefs(i)).Address(False, False) = aRefs(i) _
, "", "(or " & Range(aRefs(i)).Address & ")") _
& ", "
On Error GoTo 0
Next i


Again, this will work so long as the formula refers only to the same worksheet.

If you are going to refer to other worksheets/workbooks, I'll need more
information to make appropriate modifications.

Here is it all together:

============================================
Option Explicit
Function GetRefs(rg As Range) As Variant
Dim sStr As String
Dim aRefs
Dim i As Long
Dim re As Object
Const sPat As String = "[-+/*=^&,()]"

If rg.Count <> 1 Then
GetRefs = CVErr(xlErrRef)
Exit Function
ElseIf rg.HasFormula = False Then
GetRefs = CVErr(xlErrValue)
Exit Function
End If

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
sStr = re.Replace(rg.Formula, Chr(1))

aRefs = Split(sStr, Chr(1))

For i = 0 To UBound(aRefs)
On Error Resume Next
GetRefs = GetRefs & aRefs(i) & " " & _
IIf(Range(aRefs(i)).Address(True, True) = aRefs(i) Or _
Range(aRefs(i)).Address(True, False) = aRefs(i) Or _
Range(aRefs(i)).Address(False, True) = aRefs(i) Or _
Range(aRefs(i)).Address(False, False) = aRefs(i) _
, "", "(or " & Range(aRefs(i)).Address & ")") _
& ", "
On Error GoTo 0
Next i

'remove last comma <space>
re.Pattern = ", $"
GetRefs = re.Replace(GetRefs, "")
End Function
===================================
--ron

 

[Dave Peterson]

It could happen.

rng.find wasn't allowed in a UDF called from formulas in worksheet cells until
xl2002 (IIRC).

I guess, you are right, even though I am not familiar with .specialcells and
have never used it. But, after reading the help on it, I see your point. I
hope the excel people would try to make the program more user friendly by
removing all these random exceptions. At least now, thanks to Ron Rosenfeld
below, I have a solution to go by. Not the most efficient to parse, but it
works.

Bob