On Fri, 29 Aug 2008 11:43:08 -0700, "Bob" wrote:

Hi Everyone:

I posted this in the wrong place, and now, I am posting it here, in the
correct newsgroup. In excel VBA, is there a way to get the cell references
that a formula contains. For example, if in cell A1, I have the following
formula:

=2*B1+C3-x

where x is a defined name (referencing cell D5).

So, for cell A1, I want the program to return to me B1, C3, and x (or D5).

I would appreciate a small code for this. Thanks for all your help.

Bob



As you have noted, you cannot get cell references in a function using the
Precedents property.

Here is one method of parsing out the cell references from a formula. I have
not tested it extensively, and it will only work on references to the same
worksheet (as written).

Depending on what you want to do with the results, other methods might also be
applicable, and/or additions to the sPat string.

Let me know how it works for your data.

===============================
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 & Range(aRefs(i)).Address & ", "
Next i

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