Precedents for formulas

[Bob]

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

 

[ward376]

Sub way()
MsgBox Sheet1.Range("a1").Formula
End Sub

Cliff Edwards

 

[TomPl]

You didn't indicate what you wanted to do with the cell address. This code
will print them to the immediates window:

Sub Precedents()
Debug.Print ActiveSheet.Range("A1").Precedents.Address
End Sub

Tom

 

[Bob]

Hi Tom:

When I run your code as a Sub, it works fine

Sub Precedents1()
s = ActiveSheet.Range("A1").Precedents.Count
MsgBox s
End Sub
and I get 3, which is correct. However, when I run it as a function so that
I can output the result onto my spreadsheet, it does not work.

Public Function ssbb()
Call Precedents(p)
ssbb = p
End Function
Sub Precedents(p)
s = ActiveSheet.Range("A1").Precedents.Count
p = s
End Sub
Now, if on the spreadsheet, you enter the formula =ssbb() and hit return,
you get 1. And I just can't understand why. I have to get the information
in a function and not a sub.

Bob

Bob

 

[TomPl]

I don't know why this is not working in a function.
I hope someone else can answer.

Tom

 

[smartin]

Hi Tom:

When I run your code as a Sub, it works fine

Sub Precedents1()
s = ActiveSheet.Range("A1").Precedents.Count
MsgBox s
End Sub
and I get 3, which is correct. However, when I run it as a function so that
I can output the result onto my spreadsheet, it does not work.

Public Function ssbb()
Call Precedents(p)
ssbb = p
End Function
Sub Precedents(p)
s = ActiveSheet.Range("A1").Precedents.Count
p = s
End Sub
Now, if on the spreadsheet, you enter the formula =ssbb() and hit return,
you get 1. And I just can't understand why. I have to get the information
in a function and not a sub.

[snipped]

In the second code example, Sub Precedents is not returning anything to
the calling function. Remember, variables (such as "p") are local to the
procedure in which they are declared (unless by chance you declared them
as global, but I doubt you did that, and you don't need to anyway).

Try

Public Function ssbb() as Long
ssbb = ActiveSheet.Range("A1").Precedents.Count
End Function

 

[Bob]

That should not matter. The function returns a variant.

Hi Tom:

When I run your code as a Sub, it works fine

Sub Precedents1()
s = ActiveSheet.Range("A1").Precedents.Count
MsgBox s
End Sub
and I get 3, which is correct. However, when I run it as a function so
that I can output the result onto my spreadsheet, it does not work.

Public Function ssbb()
Call Precedents(p)
ssbb = p
End Function
Sub Precedents(p)
s = ActiveSheet.Range("A1").Precedents.Count
p = s
End Sub
Now, if on the spreadsheet, you enter the formula =ssbb() and hit return,
you get 1. And I just can't understand why. I have to get the
information in a function and not a sub.

[snipped]

In the second code example, Sub Precedents is not returning anything to
the calling function. Remember, variables (such as "p") are local to the
procedure in which they are declared (unless by chance you declared them
as global, but I doubt you did that, and you don't need to anyway).

Try

Public Function ssbb() as Long
ssbb = ActiveSheet.Range("A1").Precedents.Count
End Function

 

[Bob]

Thanks Tom. I guess, I have to ask one of the MVP's.

 

[smartin]

That should not matter. The function returns a variant.

Hi Tom:

When I run your code as a Sub, it works fine

Sub Precedents1()
s = ActiveSheet.Range("A1").Precedents.Count
MsgBox s
End Sub
and I get 3, which is correct. However, when I run it as a function so
that I can output the result onto my spreadsheet, it does not work.

Public Function ssbb()
Call Precedents(p)
ssbb = p
End Function
Sub Precedents(p)
s = ActiveSheet.Range("A1").Precedents.Count
p = s
End Sub
Now, if on the spreadsheet, you enter the formula =ssbb() and hit return,
you get 1. And I just can't understand why. I have to get the
information in a function and not a sub.

[snipped]

In the second code example, Sub Precedents is not returning anything to
the calling function. Remember, variables (such as "p") are local to the
procedure in which they are declared (unless by chance you declared them
as global, but I doubt you did that, and you don't need to anyway).

Try

Public Function ssbb() as Long
ssbb = ActiveSheet.Range("A1").Precedents.Count
End Function

Huh?

The function you put together will return a variant (since no data type
was specified), but it will return /no value/ because "p" is never
assigned a value in the function.

You might think "p" is assigned a value by way of Sub Precedents, and in
fact "p" does have a value in that Sub, but this is irrelevant because
"p" is a local variable, and it is /erased/ when Sub Precedents quits.

Please reread my previous post. Especially "variables (such as "p") are
local to the procedure in which they are declared". Emphasis on /local/.

If that doesn't click for you, try
http://en.wikipedia.org/wiki/Local_variable

Did you actually try my Function?

 

[Phillip]

Phillip London UK

Try This

Sub MyPrecedents()
Dim rng As Range
Dim s As Integer
s = ActiveSheet.Range("A1").Precedents.Count
Set rng = ActiveSheet.Range("A1").Precedents
For x = 1 To s
MsgBox rng.Areas(x).Address
Next
End Sub

 

[TomPl]

Where is an MVP when you need one?

I have tried and tried but whether I ask for the precedents count in the
function or in a called procedure from the function the count provides not
the count of precedents, but the count of cells in the range. I am guessing
that because precedents is a property and not an object, the property does
not update within a function. I'm confused. Why doesn't someone that knows
what they are talking about pay attention.

Tom

 

[Dave Peterson]

There are somethings that can't be done in a UDF if the origination is a
worksheet cell/formula. (.specialcells or changing a value in a different cell
or changing the format of any cell).

It sure looks like this is one of them.

I used this as my function:

Option Explicit
Public Function ssbb(rng As Range) As Long
Dim p As Long
Dim myPrec As Range

Set rng = rng.Cells(1)
'Set myPrec = rng.Precedents
p = rng.Precedents.Count
ssbb = p
End Function

'This worked fine
Sub aaa()
MsgBox ssbb(Activesheet.Range("a1"))
End Sub

If I tried to call the function from a formula in a cell, then I got 1--not
matter what the cell I pointed to contained--or even if it was empty.

Why does excel behave this way? I have no idea. But it does.

 

[lcaretto]

A function called from a worksheet cannot change any Excel object; it can
only return a value to the cell where it is located. You cannot get around
this by calling a sub from a function on a worksheet either. Although this
is not allowed, there is no error message telling you this. The function
simply ignores commands to change Excel objects.

HTH

Larry

 

[Ron Rosenfeld]

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

 

[lcaretto]

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

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 =
"[-+/*=^&,(]".

:

 

[Bob]

We are not changing anything, we are simply getting information. Besides,
if that is the case, on the spreadsheet itself, using VBA, how can I get the
number of precedence?

Bob

 

[Bob]

Yes, I did try your code, and it does NOT work. If in cell A1, you type the
formula =B1+C2+D5, and then go to cell B3 (for example), and type =ssbb()
the program returns 1 and not 3, as it should. So, something is wrong.
Please give it a try in excel 2003.

Bob

That should not matter. The function returns a variant.

Bob wrote:
Hi Tom:

When I run your code as a Sub, it works fine

Sub Precedents1()
s = ActiveSheet.Range("A1").Precedents.Count
MsgBox s
End Sub
and I get 3, which is correct. However, when I run it as a function so
that I can output the result onto my spreadsheet, it does not work.

Public Function ssbb()
Call Precedents(p)
ssbb = p
End Function
Sub Precedents(p)
s = ActiveSheet.Range("A1").Precedents.Count
p = s
End Sub
Now, if on the spreadsheet, you enter the formula =ssbb() and hit
return, you get 1. And I just can't understand why. I have to get the
information in a function and not a sub.

[snipped]

In the second code example, Sub Precedents is not returning anything to
the calling function. Remember, variables (such as "p") are local to the
procedure in which they are declared (unless by chance you declared them
as global, but I doubt you did that, and you don't need to anyway).

Try

Public Function ssbb() as Long
ssbb = ActiveSheet.Range("A1").Precedents.Count
End Function

Huh?

The function you put together will return a variant (since no data type
was specified), but it will return /no value/ because "p" is never
assigned a value in the function.

You might think "p" is assigned a value by way of Sub Precedents, and in
fact "p" does have a value in that Sub, but this is irrelevant because "p"
is a local variable, and it is /erased/ when Sub Precedents quits.

Please reread my previous post. Especially "variables (such as "p") are
local to the procedure in which they are declared". Emphasis on /local/.

If that doesn't click for you, try
http://en.wikipedia.org/wiki/Local_variable

Did you actually try my Function?

 

[Bob]

Hi Phillip:

As I mentioned earlier, the code works fine in a sub, but not in a function.
I have to use a function.

Bob

 

[Bob]

I know Dave, and you raised the same question that I initially had.
Besides, I don't think we are changing anything. We are merely getting some
information out.

Bob

 

[Bob]

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

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