Circular References and Visual Basic Formulas: Trouble Opening Workbooks

Hello,

I have a workbook with a complex formula written in VBA that is part of
a circular reference.  When the workbook is open, everything works
fine.  But when I close the workbook and open it again, I get "#Value"
in the cells with the VBA formula and "#Name" in other cells part of
the circular reference.

I assume it is because there is nothing to start off the circular
reference (sort of a "primer") once the workbook is opened.

I have gotten around this by turning off auto calculation when I open
the workbook and then turning it back on as soon as the workbook is
open.  But this is a pain for me to do everytime and even harder for me
to explain to others when I send my workbooks to them.

I know that I did not have to do this with older versions of Excel. 
Does anybody know how I can fix this problem?

Thanks,

Scott


---
Message posted from http://www.ExcelForum.com/

0
1/22/2004 2:37:40 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
349 Views

Similar Articles

[PageSpeed] 37

Reply:

Similar Artilces:

Messages are minimized when opened #2
I'm a new Outlook user and When I open an email, it shows up minimized automatically. I would lilke it not to be minimized since I'm ready to read it and that is an extra step that is irritating. Any Ideas? ...

Need Formula #3
I need help with a formula for Cell E3 that will give me the total days between cell C3 and D3 - Here's what I am trying to do. Column C3 - Enter Deployment Start Date Column D3 - Enter Deployment Return Date Column E3 - Total Days Deployed As of now I am using =SUM(D3-C3) but it does some strange things. If only C3 or D3 are filled in it gives a bogus number until both cells are filled in. Also it always displays the number zero unless a value is entered - I would rather the cell remain blank if cells C3 and D3 are blank. Thanks Try this: E3: =IF(COUNT(C3:D3)=2,D3-C3,"&q...

Another question on count formula
In a large spreasheet, I want to be able to count the number of rows where column O is a nonblank (has some type of data entered - may be alpha or may be a date) AND column P = "Y". Can someone please point me in the right direction in terms of which formula to use? Thanks, David =sumproduct((isblank(o:o)=false)*(p:p="Y") "David Lipetz" <dlipetz@hotmail.com> wrote in message news:eaVoNfnCHHA.4832@TK2MSFTNGP06.phx.gbl... > In a large spreasheet, I want to be able to count the number of rows where > column O is a nonblank (has some type of da...

Removing reference to CRM
I have a small application that does not expose crm functionality but uses the framework. I would like to change the title bar in IE to not display - Microsoft CRM 3.0 in the title bar. Does anyone know if this is possible. I want to hide the fact that I am using CRM in the initial stages of a presentation. You can change the title in the loader.aspx file, which is loacated in c:\inetpub\wwwroot by default. Find the following line: <title><loc:Text ResourceId="Web.default.aspx_16" runat="server"/></title> and replace it with <title>Your...

Can't open word file with password: "can't find encryption code"
I have a word file with a password and encrypted used to open in word 2003 with the compatibility pack as the file was created in another pc in word 2007. Now, after installing office 2007, can't open that file: it says can't find the encryption code or source. Thank you. ...

outlook won't open after adding exchange server account
Hi there After I added an Exchange Server e-mail account, Outlook hangs on opening and after 15 minutes or more I have to end the process OUTLOOK.EXE from within Task Manager. When opening Outlook again, I am prompted to open in safe mode, and the same thing happens. Finally I am prompted to 'Detect and Repair' ... and after an apparently successful detect-and-repair, Outlook still hangs on opening. I haven't done anything unusual except add the Exchange Server account, so I don't have more detailed information. Please help! Best regards Loane P.S. I'm using O...

Payables transaction description disappears
We use a lot of miscellaneous vendor IDs - I have one set up as Miscellaneous Office Expense, one as Miscellaneous Client Expense, etc - so that we don't have to enter a completely new vendor for a one-time transaction. The only way to track the actual vendor name on those transactions is to put it in the Description line, since we change the vendor name and address on the card each time we use it. I've recently noticed that if I do a Trx by Vendor Inquiry, then try to drill down on an invoice, the description has disappeared and been replaced with the vendor name (which we...

I am getting "unable to read" when trying to open an excel file
When trying to open an existing Excel file. I am getting "unable to read message" Worf, That sounds like a media error. If you're trying to open it directly from a diskette, CD ROM, etc., try copying it to the hard drive (Desktop, My Documents, wherever). Open from there. -- Earl Kiosterud www.smokeylake.com "worf65" <worf65@discussions.microsoft.com> wrote in message news:63506006-D794-4C20-98AE-0C49B8705DB9@microsoft.com... > When trying to open an existing Excel file. I am getting "unable to read > message" Earl It is happening ev...

customize outlook today doesn't open
Good Day.. one of my clients have outlook 2002. when they click on customize outlook today button nothing happens. They are 50 users and none of them could get into this option. any help would be greatly appreciated Thanks John ...

How can I open .pub 1995 files?
I have old .pub files -1995 but have 1998 and 2000 software and am trying to find a way to open the files. I don't use Norton and am a mobile worker so am go crazy about not being able to access my work. Publisher 98 and 2000 will open 95 publications. Are you sure they are 95 files? You can send me one of the files and I will try to open it for you. mary-sauer at columbus.rr.com -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jvb1962" <jvb1962@discussions.microsoft.com> wrote in message news:6B50B451-F93D...

Windows Server 2003 Enterprise visualization licensing
Hi folks, Apologies if this isn't the right group to be posting this in. I've just started working for a new company as the sole IT bod. It appears my predecessor left the IT systems in a bit of a mess and one of my tasks is to sort it out. Now we have a couple of old servers which I'd like to virtualize on to something a bit newer, not only to save power but also to save a bit of space in the rather crampt server room. Now I noticed that one of our servers is running Windows Server 2003 Enterprise Edition. It appears that the copy of Windows Server is also ...

Formula for Average
I have a table with columns Mon-Fri and Rows for Week 1-5. In the cells I will be entering general numbers. My last row contains the formula to average the columns for Mon-Fri for weeks 1-5. But my problem is, For my November data, we do have a week 5, but only for Sun-Wed. What happens with my data on row 5 for Thur-Sat, where I dont have any data, I dont want my formula to average the blank cells. I welcome anyone who would be willing to let me send them an attachment as I dont think I have explained myself well. The AVERAGE() function by default doesn't count blank cells when ca...

Referring to controls
Thank you How can I refer to (subform) from (subform) on the main form? How can I refer to (subform) from (masterform)? How can I refer to control on (master form) from (subform)? "a" <A@a.com> wrote in message news:OxYjnMZSIHA.5016@TK2MSFTNGP06.phx.gbl... > Thank you > > How can I refer to (subform) from (subform) on the main form? Me.Parent!OtherSubformName.Form > > How can I refer to (subform) from (masterform)? Me!SubFormName.Form > > How can I refer to control on (master form) from (subform)? Me.Parent!ControlName On Dec 28, 12:49 pm, &qu...

Not able to close X workbook
Any ideas why the Close X would go grey and not work? nor does file close either???? The only way to exit is to file exit....the task bar won't even work. I've never seen anyone complain about File|close not working, but the closing via the top right X might be this: http://support.microsoft.com/default.aspx?scid=kb;en-us;260199 XL2000: Close Button Is Dimmed After Groupwise Is Installed cat wrote: > > Any ideas why the Close X would go grey and not work? nor > does file close either???? The only way to exit is to file > exit....the task bar won't even work. -- ...

Opportunity Status Code
I am trying to insert another status code for Pending. There are immediate open request and those that are on hold for significant periods of time due to contract expiration dates, etc.. We do not want to see the Pending opportunities in the Open list of opportunities and would like to track seperate Status Reasons for Pending. Is this customization forbidden by the MS Gods? On Aug 18, 6:11=A0am, Forrest_Adam <ForrestA...@discussions.microsoft.com> wrote: > I am trying to insert another status code for Pending. =A0There are immed= iate > open request and those that are ...

Circular Logging #5
I have a Exchange 2000 server with circular logging turned ON. If I do a nightly FULL backup then circular logging being ON should not really matter, right? We do not do incremental or differentail backups. thanks Well, consider the following. You backup at midnight. Your server fails at 2pm and you lose your database drive. With circular logging on, you can only recover your system back to the state it was in at midnight, i.e. you lose emails from midnight to 2pm. Best practice is to ensure circular logging is off. -- Neil Hobson Exchange MVP For Exchange news, links, and tips...

formula #REF!
Hi all, What should i do to prevent #REF! in my formula? I would need to delete certain rows in my database according to som criteria, but when i do that, this appears. My formulas: Range("C1").Select ActiveCell.FormulaR1C1 = "=0.3*R[2]C" Range("C2").Select ActiveCell.FormulaR1C1 = "=0.705*R[1]C" The error displays only when i delete Row 3. If the other rows ar deleted, it works fine. I read about this somewhere, but cant seem to find the thread. Thanks in advance! Ken -- Message posted from http://www.ExcelForum.com Hi Keng When you delete row 3...

Title Reference
I am aware how to direct the title of an individual set of data but i want to know how to link the title in an Excel chart to a cell. Any ideas? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23758 View this thread: http://www.excelforum.com/showthread.php?threadid=541475 Try this, http://www.andypope.info/tips/tip001.htm Cheers Andy chalky wrote: > I am aware how to direct the title of an individual set of data but i > want to know how to link the title in an...

Open Minimized
Is there some way to open a file minimized - just open it, do some processing, and close it, without it showing on the screen? Hi you could use application.screenupdating=false -- Regards Frank Kabel Frankfurt, Germany "Bill" <nunnallywilliamr@uams.edu> schrieb im Newsbeitrag news:5F65B845-E7C3-4F67-A7AB-C652DAFE431F@microsoft.com... > Is there some way to open a file minimized - just open it, do some processing, and close it, without it showing on the screen? Maybe you could save your .xls file as hidden. with that workbook the active workbook: Window|Hide Close exce...

Circular text
How do I create circular text? ...

Enable Visual Styles after application has started
Hi All, I want to dynamically change the Application.EnableVisualStyles flag. I have an application that uses a startup form (.Net Framework 2.0) and if I set the EnableVisualStyles in the Application.Startup event it doesn't work. The only way it works is if I set this flag at design time in the Application properties. Any suggestions on how I can change this flag dynamically without using Sub Main()? Thanks JB Am 01.06.2010 23:55, schrieb JB: > Hi All, > > I want to dynamically change the Application.EnableVisualStyles flag. > I have an application that u...

Cannot open earlier version of access database
I'm trying to open Northwind database with MS Access 2003. The problem is that a dialog box appears prompting me to go to Database > Tools > Convert Database, however, the dialog box re-appears over a couple of times and disallows the user from doing anything within MS Access. If you are getting the Convert dialog you will need to convert the database to the version that you are working in and save it and then open it next time instead of the original file. During the convert process it will not change the original file. It will make a NEW copy for you to use. I wonder w...

cursor reference
I want to know if this is possible? If I have text in column A can I scroll down the text and in let's say in cell B1 it shows the text of where my cursor is? So if my cursor is on cell A5 B1 shows the text in A5 if I scroll down and stop on A15 cell B1 shows the text in A15. Tried searching but couldn't find anything. TIA Very difficult if you mean a mouse hover (like viewing a comment). Easy if you mean clicking on a cell or using the arrow keys to navigate up & down. Put the following event macro in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target...

average formula #4
Hi all, How to get the real average, when you have cells containing formula returning zero? I have tried "AVERAGE" but that don't work when I have formulas in th cells that I want to get average. Example: Without formula in A1:A5 A1 A2 A3 A4 A5 10 10 10= 10 average (right) With formula in A1:A5 A1 A2 A3 A4 A5 10 10 10= 6 average (wrong) I want that also to be 10 as average value. What to be done? Any idea's? //Thoma -- Message posted from http://www.ExcelForum.com AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5) i.e. f...

Excel how to tell if workbook is already open BEFORE you try to open it.
I've been looking all over the net trying to find C# code to determine if a workbook is open before you try to open it so you can alert the user to close it before you update it. There must be a way. Please help. hd "hdjim" <hdjim69@yahoo.com> wrote in message news:121fe4a0-138e-497f-b6b7-32b4b3f9ef7a@o14g2000yqb.googlegroups.com... > I've been looking all over the net trying to find C# code to determine > if a workbook is open before you try to open it so you can alert the > user to close it before you update it. > > There must be a w...