Excel VBA function unintentionally calls another function in module

Question

This is probably a quick fix, but I am new to VBA and I'm somewhat confused. I have a module:

Function classify (r As Range)
Debug.Print("Text")
End Function

Function foo(r As Range)
Debug.Print(r.Count)
End Function

There is working code before and after the module.

If, in a cell in excel, I write =foo(A1) and then press enter, I get the following output:

 1 
Text

Why? How did foo call classify? Moreover, if I just call '=classify(A1)', I get

Text
Text

Has classify somehow been set to always get called? Any help is greatly appreciated

do you have a formula in A1 which is perhaps making other cells volatile? — InContext
– InContext, Commented Dec 12, 2012 at 22:52
Do you clear the output before you press enter? Maybe there are old values in the output pane that still show. — John Alexiou
– John Alexiou, Commented Dec 13, 2012 at 1:04

Rick · Accepted Answer · 2012-12-12 23:35:52Z

5

The only way that I could reproduce your issue was with the following scenario:

Cell A1:
=foo(A1)

Cell A2:
=classify(A1)

When updating cell A1 I get

1
Text

I think this is due to the fact that Cell A2 has a reference to A1 so by updating A1 I also call the formula in A2 as it is referencing A1.

Assuming your formula that you are entering is in A1 I would trace dependents to see where the other formula is.

answered Dec 12, 2012 at 23:35

Rick

1,0738 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1