I want to use VBA to check to see if a formulas has any references to blank
cells.

So if my formulas is

=A1+SUM(B2:B30)/Average(C1:C30)+Opcost

I want to know if either A1 is blank, any of the array items in the SUM or
Average are blank, if named cell Opcost is blank etc

Is there a way to evaluate the formula propertly of the cell object to test
for blanks? Am I wandering into Regular Expression territory with this one?
Or can I do it with VBA?

Thanks

assume the cell is Cell F10. as long as the formula only references cells
on the same sheet

Dim rng as Range, rng1 as Range
set rng = Range("F10").DirectPrecedents
on Error Resume Next
set rng1 = rng.specialCells(xlBlanks)
On Error goto 0
if not rng1 is nothing then
msgBox rng1.Address & " are blank"
End if

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
...
I want to use VBA to check to see if a formulas has any references to
blank
cells.

So if my formulas is

=A1+SUM(B2:B30)/Average(C1:C30)+Opcost

I want to know if either A1 is blank, any of the array items in the SUM or
Average are blank, if named cell Opcost is blank etc

Is there a way to evaluate the formula propertly of the cell object to
test
for blanks? Am I wandering into Regular Expression territory with this
one?
Or can I do it with VBA?

Thanks

You can loop through all the cells and check for blank.
And at the end you can do the calculation.

the following is crude be should do the trick...

dim cel as Range, rng as range, x, y, z

x = 0
y = 0
z = 0
set rng = Range("B2:B30")
for each cel in rng
If cel = 0 than
msgbox cel.address & " = 0"
else
x = x + cel < create sum
end if
next

or you can check for
cel = "" <<<< is blank
len(cel) = 0 <<<<< no characters in cell

do the same for column C & use

if cel ...... then

else
y = y + 1 <<<<< to get count of cells in column c
z = z + cel <<<<< to get sum of cells

[average = z/y]

this is crude but it will do the trick....



--
steveB

Remove "AYN" from email to respond
"ExcelMonkey" wrote in message
...
I want to use VBA to check to see if a formulas has any references to blank
cells.

So if my formulas is

=A1+SUM(B2:B30)/Average(C1:C30)+Opcost

I want to know if either A1 is blank, any of the array items in the SUM or
Average are blank, if named cell Opcost is blank etc

Is there a way to evaluate the formula propertly of the cell object to
test
for blanks? Am I wandering into Regular Expression territory with this
one?
Or can I do it with VBA?

Thanks

ExcelMonkey wrote:
I want to use VBA to check to see if a formulas has any references to blank
cells.

So if my formulas is

=A1+SUM(B2:B30)/Average(C1:C30)+Opcost

I want to know if either A1 is blank, any of the array items in the SUM or
Average are blank, if named cell Opcost is blank etc

Is there a way to evaluate the formula propertly of the cell object to test
for blanks? Am I wandering into Regular Expression territory with this one?
Or can I do it with VBA?

Thanks

Here is a recursive approach that returns true if you pass it a blank
cell or a cell which refers to a blank or a cell which refers to a cell
which refers to a blank ...

Function RefersToBlanks(R As Range) As Boolean
Dim precedents As Range
Dim cl As Range

If Not R.HasFormula Then 'basis of recursion
If IsEmpty(R.Value) Then
RefersToBlanks = True
Else
RefersToBlanks = False
End If
Else 'recursive case
Set precedents = R.DirectPrecedents
For Each cl In precedents.Cells
If RefersToBlanks(cl) Then
RefersToBlanks = True
Exit Function
End If
Next cl
End If

End Function

Hope that helps

-John Coleman

a slightly shorter version:

Function RefersToBlanks(R As Range) As Boolean
Dim precedents As Range
Dim cl As Range

If Not R.HasFormula Then 'basis of recursion
If IsEmpty(R.Value) Then RefersToBlanks = True
Else 'recursive case
Set precedents = R.DirectPrecedents
For Each cl In precedents.Cells
If RefersToBlanks(cl) Then
RefersToBlanks = True
Exit Function
End If
Next cl
End If

End Function

In my original code I somewhat inconsistently used the fact that
boolean variables are implicitly initialized to false in the recursive
case but not the basis case. Some programmers may not like default
values, but if VBA provides them why not use them?

-John Coleman

Note that this function will not return correct results if used in a
worksheet cell such as

=RefersToBlanks(B10)

Also, it seems like the major change was the recognition that your function
didn't need to recurse when precedents was used instead of directprecedents.

--
Regards,
Tom Ogilvy


"scattered" wrote in message
oups.com...


a slightly shorter version:

Function RefersToBlanks(R As Range) As Boolean
Dim precedents As Range
Dim cl As Range

If Not R.HasFormula Then 'basis of recursion
If IsEmpty(R.Value) Then RefersToBlanks = True
Else 'recursive case
Set precedents = R.DirectPrecedents
For Each cl In precedents.Cells
If RefersToBlanks(cl) Then
RefersToBlanks = True
Exit Function
End If
Next cl
End If

End Function

In my original code I somewhat inconsistently used the fact that
boolean variables are implicitly initialized to false in the recursive
case but not the basis case. Some programmers may not like default
values, but if VBA provides them why not use them?

-John Coleman

Tom Ogilvy wrote:
Note that this function will not return correct results if used in a
worksheet cell such as

=RefersToBlanks(B10)
(snip)
Regards,
Tom Ogilvy



Thanks for pointing this out.

Why doesn't the function work in a worksheet cell? I know that you
can't put functions with side effects (like message boxes) in a cell,
but I don't see any side effects in my function. Is the problem with
recursion as such?

Another problem with my function is that it doesn't work with
precedents in another sheet. Is there any work around? (The approach
using Precedents instead of DirectPrecedents has the same problem)

Have a good day

-John Coleman

Behavior for some functions is different when used as a function in a cell -
Special cells for one. Find in xl2000 and earlier (doesn't work). This is
one of them. I can't say why.

As you have stated, precedents and directprecedents and dependents and
directdependents do not work off sheet. The workaround is to use the Excel4
macro approach of follow arrows I believe. Stephen Bullen uses that
technique in his utility to find circular errors.

http://www.oaltd.co.uk/Excel/Default.htm

--
Regards,
Tom Ogilvy

"scattered" wrote in message
oups.com...


Tom Ogilvy wrote:
Note that this function will not return correct results if used in a
worksheet cell such as

=RefersToBlanks(B10)
(snip)
Regards,
Tom Ogilvy



Thanks for pointing this out.

Why doesn't the function work in a worksheet cell? I know that you
can't put functions with side effects (like message boxes) in a cell,
but I don't see any side effects in my function. Is the problem with
recursion as such?

Another problem with my function is that it doesn't work with
precedents in another sheet. Is there any work around? (The approach
using Precedents instead of DirectPrecedents has the same problem)

Have a good day

-John Coleman