UDFs and Circular References in XL2k

[Mike Mertes]

I've got a workbook that I use to keep track of purchases I make online. In
column A is a description of the purchase, and in the same row to the right
in column K will appear "W", "+", or "-". (These are arbitrary characters
I use to rate the purchase.) Because they are strings and don't carry any
inherent value, I wrote a UDF to total all the +'s in the K column. I call
the UDF from Range("K74"), the row of totals. So, Range("K74").Formula =
"=PTotal()". My code looks like this:

Function PTotal() as integer

Range("K2").select 'K2 is the first desc. under the title row

Do

If ActiveCell.Value = "+" Then PTotal = PTotal + 1

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Offset(0,-10).Value = ""

End Function

Very simple, very effective. If I step through the code in the VBE, it works
just fine. It successfully stops when it doesn't find anymore descriptions
in column A (hence the Offset(0, -10).) However, calling the function from
K74 as I do causes unusual erroneous behaivor in Excel! At first it will act
as if it's caught in a loop of code, requiring me to Ctrl+Break. When I end,
it immediately does the same thing again, requiring me to break. Eventually,
after a couple of breaks, Excel will complain and tell me that there are
circular references and that Excel can't tell me where they are. I get the
impression that everytime the function is done executing, rather than
returning a value, it decides to execute itself again. That would
beperfectly
understandable except that if that were the case the following function
would have the same effect, and it does not:

Function PTotal() as integer
PTotal = 20
End Function


What is causing this unusual behaivor, and what alternatives do I have?

Thanks for your help once again, MVPs!
-Mike

Also, I was also wondering if I can post HTML to the newsgroup, such that I
can include small screenshots as jpegs to illustrate my questions, and post
my text in colors with a monospaced font for readability? That would likely
make it easier for anyone interested in answering my questions... but
naturally, I assume it would be looked down upon.

 

[Frank Kabel]

Hi
you should not use this function in the same range. Also I would not
use a UDF for this task. the function
=COUNTIF(K1:K100,"+")
would do the same is is much faster


P.S.: please DON'T post attachments or use HTML in newsgroups. Many
people will filter these kind of messages. Also you will create large
messages with attached files. Some people will 'hate' your for that as
they have to pay for volume or have a time based Internet access!

 

[Charles Williams]

Hi Mike,

Any action in a UDF that tries to modify the environment (like select) is
ignored.

try something like this (but COUNTIF will be faster)

Function PTotal(theRange as range) as long
dim oCell as range

on error resume next
for each oCell in theRange
If oCell.Value = "+" Then PTotal = PTotal + 1
next oCell
End Function

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm