Precedents for formulas

[Dave Peterson]

Using .specialcells doesn't change anything in my mind, either. But it isn't
allowed in UDFs called from formulas in worksheet cells.

 

[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

 

[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