Excel - Using ActiveCell.Value Yields Circular Reference

Hey guys, I have recently been assigned a project that uses VBA and I have
been slamming my head against the keyboard for a few hours on this one,
so any help would be more greatly appreciated than you can possibly
imagine. I cannot find anything online, and the answer is probably really
simple. In any case:

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


Does not return the value stored in A6 and creates an annoying spurt of
message boxes telling me that the value is 0. I am seriously about to
break my computer from sheer rage. Help please!




--
BrkenRecord

Hi,

Am Mon, 4 Jun 2012 18:11:21 +0000 schrieb BrkenRecord:


Try:
Sub GetTableValue()
i = Sheets("TableC11").Range("A6")
MsgBox "value is " & i
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

The reason for the error is that a function can not do a Select.
Functions basically can only return values.  They can not change other
cells or go to other cells.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Claus Busch;1602379 Wrote:

Thanks Claus,

That would work except I need the 'i' value to be variable, so i need to
get the ActiveCell command to work for me. Any thoughts?




--
BrkenRecord

Hi,

Am Tue, 5 Jun 2012 11:02:15 +0000 schrieb BrkenRecord:


then try it this way:

Sub GetTableValue()
Sheets("TableC11").Select
i = ActiveCell
MsgBox "value is " & i
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Subject:  Using ActiveCell.Value Yields Circular Reference

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