Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using ActiveCell.Value Yields Circular Reference
Hey guys, I've recently been assigned a project that uses VBA and I've 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 can't 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'm seriously about to break my computer from sheer rage. Help please! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ActiveCell.Value Yields Circular Reference
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ActiveCell.Value Yields Circular Reference
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 On Jun 4, 2:11*pm, BrkenRecord wrote: Hey guys, I've recently been assigned a project that uses VBA and I've 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 can't 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'm seriously about to break my computer from sheer rage. Help please! -- BrkenRecord |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ActiveCell.Value Yields Circular Reference
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ActiveCell.Value Yields Circular Reference
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
circular reference | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Programming | |||
Circular Reference? | Excel Programming | |||
HELP: circular reference | Excel Programming | |||
Circular Reference | Excel Worksheet Functions |