Using ActiveCell.Value Yields Circular Reference

[BrkenRecord]

Hey guys, I've recently been assigned a project that uses VBA and I'v
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 possibl
imagine. I can't find anything online, and the answer is probably reall
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 o
message boxes telling me that the value is 0. I'm seriously about t
break my computer from sheer rage. Help please

 

[Claus Busch]

Hi,

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

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

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


Regards
Claus Busch

 

[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

Thanks Claus,

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

 

[Bob Flanagan]

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]

Hi,

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

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?

then try it this way:

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


Regards
Claus Busch

 

[joeu2004]

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