Selecting Precedents On Other Sheet Via Macro

[FARAZ QURESHI]

The following macro selects precedents only if on the current sheet.

Sub Macro1()
ActiveCell.Precedents.Select
End Sub

If the cell contains reference to some other sheet in the same book the
error 1004 stating "No Cells Found" pops up.

Any suggestion?

 

[Gary''s Student]

One way would be to bring the formula in as a string and parse it to find the
precedents. Since programming a compiler for Excel is non-trivial, I would
consider searching the Internet for existing code.

 

[FARAZ QURESHI]

Good suggestion Gary!

However, can you help me in designing a UDF to return the number of
precedent cells/ranges present in a formula?

Thanx again.

 

[Gary''s Student]

We can make simple UDFs for simple formulas and expand the code to include
more cases. For example, if the formula is extremely simple:

=A1+B1
=A1-B1+C2
=(D45+C45)/AA12

then all we need to do is to count the Operators:

Function ArgCounter(r As Range) As Integer
Dim sFormula As String
Dim seps()
seps = Array("+", "-", "*", "/")
sFormula = r.Formula
l = Len(sFormula)
ArgCounter = 0
For i = 1 To l
characterr = Mid(sFormula, i, 1)
For j = 0 To 3
If characterr = seps(j) Then
ArgCounter = ArgCounter + 1
End If
Next
Next
End Function

This DOES work for simple case. But for:

=SUM(A1:B9)

It does not work. So we add : to the list of Operators

Then we discover that:

=A1+742

returns a two. We need to decide if "742" is an argument and adjust the
code accordingly.

Then we discover that:

=++++A1+A3

is also a perfectly valid formula and we must adjust the code that case,


etc......