Precedents for formulas
 

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

Precedents for formulas
 

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

Cliff Edwards

Precedents for formulas
 

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" 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

Precedents for formulas
 

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" wrote in message
...
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" 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

Precedents for formulas
 

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

Tom

"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.

Bob

Bob

"TomPl" wrote in message
...
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" 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

Precedents for formulas
 

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

Precedents for formulas
 

That should not matter. The function returns a variant.

"smartin" wrote in message
...
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

Precedents for formulas
 

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


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

Tom

"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.

Bob

Bob

"TomPl" wrote in message
...
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" 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

Precedents for formulas
 

Bob wrote:
That should not matter. The function returns a variant.

"smartin" wrote in message
...
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?

Precedents for formulas
 

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

Precedents for formulas
 

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

Precedents for formulas
 

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.

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

--

Dave Peterson

Precedents for formulas
 

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

"TomPl" wrote:

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

Tom

"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.

Bob

Bob

"TomPl" wrote in message
...
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" 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

Precedents for formulas
 

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

Precedents for formulas
 

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

"Ron Rosenfeld" wrote:


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

Precedents for formulas
 

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

"lcaretto" wrote in message
...
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

"TomPl" wrote:

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

Tom

"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.

Bob

Bob

"TomPl" wrote in message
...
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" 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

Precedents for formulas
 

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

"smartin" wrote in message
...
Bob wrote:
That should not matter. The function returns a variant.

"smartin" wrote in message
...
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?

Precedents for formulas
 

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

"Phillip" wrote in message
...
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

Precedents for formulas
 

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

"Dave Peterson" wrote in message
...
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.

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

--

Dave Peterson

Precedents for formulas
 

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

"Ron Rosenfeld" wrote in message
...
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

Precedents for formulas
 

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

Bob wrote:

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

"Dave Peterson" wrote in message
...
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.

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

--

Dave Peterson

--

Dave Peterson

Precedents for formulas
 

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

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

Bob wrote:

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

"Dave Peterson" wrote in message
...
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.

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

--

Dave Peterson

--

Dave Peterson

Precedents for formulas
 

On Sat, 30 Aug 2008 09:47:01 -0700, lcaretto
wrote:

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

Precedents for formulas
 

On Sat, 30 Aug 2008 10:36:30 -0700, "Bob" wrote:

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

Precedents for formulas
 

It could happen.

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

Bob wrote:

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

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

Bob wrote:

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

"Dave Peterson" wrote in message
...
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.

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson