"BrkenRecord" wrote:
Subject: Using ActiveCell.Value Yields Circular Reference
Function HelpPlease()
Call GetTableValue(i)
MsgBox "value is" & i
End Function

Sub GetTableValue(i)
Sheets("TableC11").Select
Range("A6").Select
i = ActiveCell.Value
End Sub

You neglect to state the context. I presume you are call calling HelpPlease
from an Excel formula, for example =HelpPlease().

See the example VBA code below.

If you enter =myfunc1() into Sheet1!A1 for example, you will get a circular
reference error the first time, and you will always see the zero return
value.

The primary problem is the Select method. You cannot use Select in the
context of a function that is called from an Excel formula.

Note that I say "in the context" and "called from an Excel formula". It
does not matter that the Select usage is actually in a sub(routine). What
matters is: the first-level VBA context is a function.

In contrast, note that =myfunc2() works -- usually, specifically in
Sheet1!A1.

However, if you put =myfunc2() into Sheet3!C3, you will get a ciruclar
reference error the first time, and you will always see the zero return
value.

That demonstrates a secondary problem: you cannot reference the same cell
from which the function context is called directly or indirectly.

Finally, try executing the "doit" macro. Note that it works fine, despite
the use of Select in the function context.

That demonstrates that the problem is not the use of Select in a function
context so much as the context in which it is executed; that is, whether or
not the function context is called directly or indirectly from an Excel
formula at the time.

PS: As you play with the example VBA procedures below, you might discover
some other interesting anomalies of Excel.

-----

Option Explicit

Sub doit()
Dim x
Sheets("sheet1").Select ' something not Sheet3, for demo purposes
x = myfunc1()
MsgBox "doit" & vbNewLine & x
End Sub

Function myfunc1()
Dim x
Call mysub1(x)
MsgBox "myfunc1" & vbNewLine & x
myfunc1 = x
End Function

Function myfunc2()
Dim x
Call mysub2(x)
MsgBox "myfunc2" & vbNewLine & x
myfunc2 = x
End Function

Sub mysub1(x)
Sheets("sheet3").Select
Range("c3").Select
x = ActiveCell.Address & vbNewLine & ActiveCell
MsgBox "mysub1" & vbNewLine & x
End Sub

' note-1: use of "r" below is for programming convenience.
' we could use Sheets("sheet3").Range("c3") directly
' a With statement instead
'
' note-2: the reference r is the same as r.Value since
' Value is the default property for a Range object

Sub mysub2(x)
Dim r As Range
Set r = Sheets("sheet3").Range("c3")
x = r.Address & vbNewLine & r
MsgBox "mysub2" & vbNewLine & x
End Sub