Memory Usage ...
Dec 6, 2006
I'm using a Dell P4, 1.6Ghz, 512Mhz of memory (Win2K & Excel 2003), and it's bogging on spreadsheets that aren't that big and complicated (compared to thing's I did years ago, in the days of 386's and Win98)...
It hits the 100% CPU usage and stays there for minutes (especially when saving) on files that are only around 2M in size, yet it's not using much of the memory...
Spreadsheets large or small seem to have little effect on how much memory is being used... is Win2K allocating a fixed % of total memory for Excel to use?... can this be changed, so Excel gets to use more of the available memory?
I've been cleaning up and redesigning my spreadsheets to make them more efficient, but there seems to be a disconnect between my hardware specs and Excel's performance with files that aren't that big ~:-/
Disabling my antivirus software (Nortons) prior to saving, seems to speed it up a little, but this is not something I should be having to do...
View 9 Replies
ADVERTISEMENT
Feb 3, 2010
I have created an excel tool w/ A LOT of macros in it, and the tool "speaks" to the SQL server (downloads data from it and uploads it back). My big problem now is that every time I run any of the VBA code the amount of RAM that is getting allocated to Excel (2007) is increasing - I am checking it in the Task Manager. So if I work for a long time without closing down Excel - my entire RAM is getting consumed and the system slows down, and sometimes crushes.
I was wondering if there is a way in VBA to clean up memory that is used by Excel.
Do you have any hints? I am using extensively 3 things - screenupdating off; displaymessages off, and calculations off. But this speeds up Excel but doesn't reduce the memory usage.
View 7 Replies
View Related
Jun 24, 2006
I was reading in the sticky post about the new version of Excel that is due out this year. One of the improvements listed was the increase of usable memory by Excel from 1gig to the amount of Windows memory. In reading this, am I to understand that increasing a machine's physical memory above 1gig is useless for Excel in the current version? My specific issue is I have a very involved worksheet that takes about 2.5 minutes to calculate and this will increase as more data is added, it is heavy formula laden. I was going to increase memory from 2gig to 4gig to assist in reducing the calculation time.
View 3 Replies
View Related
May 12, 2007
I am working on a spreadsheet that is very large. Opening the spreadsheet and even operating the spreadsheet with the cells off of manual calc can slow down the computer quite a bit. The spreadsheet has quite a few sheets, alot of data, very basic formatting, and alot of vlookup (as well as other) formulas.
View 5 Replies
View Related
Feb 23, 2009
This seems like it is pretty easy but for some reason it is being goofy for me.
I entered this code to check it.
IF IsEmpty(Range("A1")) Then
msgbox("Yes, It is empty")
Else
msgbox("Not empty")
When it comes up I always get the box with "Not empty" even though I havent typed anything in the cell since I have created it. Even tried Clear Cell. Basically what it is for is to check if cell A1 is empty and if it is, it will create a Data header and if not, it will find the first empty cell and continue inputting data.
View 9 Replies
View Related
Apr 23, 2009
i have seen lot of formula especially sumproduct where "--" or "-" is used. i understand it is used for giving the conditions when the column has text or dates to be retrieved. But what decides two minus or single minus or no minus to be used.
If somebody can throw some light on this will be really helpful for my excel
View 9 Replies
View Related
Jun 26, 2009
done some VBScript spreadsheet reporting involving basically inserting data and some formatting.
I need some advice on what approach to take with Excel on my current project. The basic goal is to scrape project requirements off a word doc and load them into Quality Center (a test management tool from HP, henceforth referred to as QC). I have an hta/vbs process that offers the user choices gleaned from the QC API, scrapes the word doc, and creates a spreadsheet with a row for each requirement and a column for each property a "requirement" has in QC. These values are a combination of data from the word doc and values selected by the user in the preceeding hta interface.
The reason for this Excel step in the process is that all the values chosen up to this point are generic for the entire project. But there are a few properties that will be requirement specific. So all the info is collected in Excel and the user is to then go through each row and choose the "target cycle" (which I pulled off QC earlier in the process and display in Excel as a dropdown list). The "Platform Folder" and the "Module Folder".
Ok, so with that backround, here is my quandry... In the hta interface the user selected all the "Platform Folders" the project will have requirements in. These are then presented in a dropdown in the Excel, so the user can choose which of these Platform Folders this specific requirement should be loaded into. What I need to do is then have the "Module Folders" to be a dropdown that is dependant on the "Platform folder" chosen.
This is difficult enough for me, creating a dynamic dependant dropdown that will be replicated on each row. I don't know exactly how to do this but feel I can probably figure it out with all the help offered on these MVP sites.
What I can't figure out is where to do this. Should I set up the lists, formulas, and control code dynamically? Can these dependant list derive their values from comma delimited lists (like I did the non-dependent lists) or must they be in a worksheet? The only way I see to make the dependent dropdown work correctly is to have it run off a worksheet event. Can worksheet event code be applied dynamically or do I need to have a "template" type workbook containing the code in the application's folder and use that instead of creating a new workbook at exe time? I still have to write the upload into QC code, and that will probably be a macro that the user will have to install into their personal.xls. Can the dependent dropdown code be installed in the same way even though it needs to be event driven? Another aspect is getting the data for the dropdown. I can either get the lists earlier in the process when I already have a connection to QC and pass it along, or I can connect to QC from Excel and get the values dynamically. I am assuming passing them in will be more efficient.
View 9 Replies
View Related
Nov 21, 2006
We have designed a data collection program in excel which use's userforms for the data entry and form printing. There are 20 userforms, each with a "Next" button which hides the current userform and shows the next userform. The first "next" button has a copy and paste code which copies a today() statement and paste's it in a different cell to record how long it took to complete the data entry. We are able to go through the program once, but when we try a second time it freezes and the cpu usage goes to 100% resulting in us having to End Task.
View 6 Replies
View Related
Apr 25, 2014
how I have the data arranged... What I need to do is for each type I need to be able to calculate usage, so I will need to make sure it is subtracting say COKE usage the week of APRIL 1ST where beginning inventory on april 1ST was 99 and on April 7th the invnentory was 72, of course the usage is 99-72/7 will give me per day but I need the formula to be able to know which date will correlate to know which inventory to subtract from... So each time I add a new inventory in the sheet it will always be able to find where the data is it needs to subtract from to get the proper usage... Of course do this without manually entering the usage each week...
View 1 Replies
View Related
Jan 2, 2010
I have a worksheet where the user selects between two option controls, which are mutually exclusive. when they toggle an option, a user form pops up, depending on which option they selected, and askes them for a number, which is used in a calculation elsewhere in the page. The user form has a cancel button, in case they decide to back out. If they cancel, I want the option buttons to reset back to their original state.
The problem I'm having is that when I code it, if you select cancel on the user form, it causes the option to revert back to it's previous state, but that triggers the selection of the option again, which pops up the form, making a neverending loop! Is there a better way to do this? I tried it with a toggle button, with the same results.
View 14 Replies
View Related
Jan 20, 2010
basically im setting a range type variable to be the a column of data from a table that is on another sheet but in the same workbook. im wondering if im supposed to get rid of certain variables i have created...especially in an instance like this, where the variable seems to be more of a reference to the actual table data.
Dim curtzeNum As Range
Set curtzeNum = Worksheets("RawData").Range("Table3[Curtze_num]")
should there be an erase statement for these at the end of the sub?
View 9 Replies
View Related
Apr 25, 2014
Calculating usage...
Take the Current date and I need that inventory ---- find the date 7 days before this date ---- get the inventory for that date --- current inventory - previous inventory / 7 will give me the daily usage... the data will be in a pivot table so I need to make sure it doesnt change the usage when moving the table around...
View 1 Replies
View Related
Mar 27, 2009
I would like to combine the application.username function with the input box however, I do not know the proper syntax.
View 2 Replies
View Related
Jun 5, 2007
I send out multiple versions of the same sheet every month. I want to be able to have a script run every time the spreadsheet is opened to send an electronic log directly to a text file on my machine (or something similar)
Is this possible or some sort of a pipe dream?
It would be used for me to monitor usage of the sheet, and i'd require only the same of the spreadsheet (which is always unique), the date/time and possibly the computer name / username? (not really needed)
View 9 Replies
View Related
Sep 25, 2009
My spreadsheet has a sheet1 with 1000 rows and needs four columns with "sumproduct" formulas referencing sheet2 with 20000 rows. An example of the formula is:
=SUMPRODUCT(--(Sheet2!$F$2:$F$20000=$B3),--(Sheet2$A$2:$A$20000 >=$J$2))
Once I add them, the recalculation slows it to a crawl. In trying to understand the problem, I do a Manual/Automatic between running a sort. The recalculation still takes too long to be practical.
View 9 Replies
View Related
Aug 10, 2007
In a macro I am writing, I am trying to use the data stored in a variable inside a formula. Specifically, my variable holds a string that represents the title of a specific sheet in my workbook. I am trying to put this variable into the count formula to be used as the sheet location.
View 9 Replies
View Related
May 29, 2014
i am working on model where customer is billed based on usage of the equipment. Usage is measured in cycles per day. Assuming there are 30 Days a month. i am trying to build a spreadsheet where if i increase or decrease the monthly usage it automatically update the sheet. Remember there is limit on maximum number of times an equipment can be used.
Like for Example
Maximum life of one equipment. 3000 Cycles
Daily usage : From 1 cycle per day to 10 cycles.
Monthly usage = 30 Days * (daily usage)
Now the problem is each month new equipment is leased. so i have to keep track of each month's equipment how much it has been used and how much left. I have attached the example spreadsheet.
View 1 Replies
View Related
Jun 8, 2013
In the attached sheet, I have logged my gas energy consumption (kWh) from 2006 to 2012. I want to analyse my relative consumption over time, on a monthly basis.
However, the bills have been generated by different suppliers for irregular periods (eg. 10-May-2006 to 24-Jul-2006, or 23-Feb-2010 to 6-Mar-2010). So, how can I re-tool my sheet to arrive at rough figures that can be allocated against each month?
Or, if there is no dependability about that method, formulae which allow for effective comparison over time? Let's say, daily?
View 3 Replies
View Related
Jan 13, 2012
Some of my excel files, which are not massive seem to crash my computer when saving or recalulating or even using some filters. The files show as not responding for a couple of mins then come back to life after completing the requested action. Often this is not a complicate task that can cause this.
When this is happening the CPU usage shown on task manager is 99 for the excel file.
This doesn't happen on any other program, I can use word and powerpoint etc with no issues. Its just excel that my computer has a problem with.
This is really frustrating as I can end up twiddling my thumbs for 5 mins unable to do anything, when I just want the file to save or re calculate. I already use manual calculation on anything file more than 500 lines to work with the situation.
View 4 Replies
View Related
Aug 18, 2006
I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.
View 9 Replies
View Related
Mar 27, 2007
I currently produce excel reports for my company but need to somehow monitor how many times they are accessed and, if possible, when and by whom. All these excel files are situated on a server that our company uses and all users systems are connected to this. We use office 2003 and Outlook as the emailing system. My guessing is that the programming would have to intiate the usage file, amend it, and then close it each time a report is opened.
For each of the files that I want this file to monitor, I have written the following program which essentially opens up the usage file 'EPoS Usage' which is a simple grid, enters an extra one to the relevant counter cell then closes it. I really need to include a date and time for each opening and, if possible, get a username as well. Here it is:
Private Sub workbook_open()
Application. ScreenUpdating = False
Workbooks.Open Filename:= _
"S:Newman CommonCATEGORY MANAGEMENT - RangesEPoS Usage.xls"
Dim Counter As Integer
Counter = Cells(2, 8)
Counter = Counter + 1
Cells(2, 8) = Counter
ActiveWorkbook.Save
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Mar 27, 2009
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.
I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
View 4 Replies
View Related
Nov 30, 2008
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................
View 17 Replies
View Related
Dec 15, 2006
I have name 28 columns. The range in EACH columns is 6:14000, i.e. the first name is date and the range is from Row B6 to Row B14000.
I am using SumProduct instead of VlookUp to look up values. An example of one of my sumproduct formulas:
SUMPRODUCT(--(Date=$B$6),--(Book=$D$6),PnL___Book)
where the first columns has dates, the next column is bookname and the last column is the profit and loss for each book, i.e. the values that need to be addad.
Now, suddenly I do not get any numbers but just #VALUE! but if I change the range to a lot less, e.g. between Row B6 and B50, it works.
My questions is if this has to do with memory in Excel. Are there limits to how many formulas and how many names you can have before Excel crashes. I am aware that the 28 columns names are very large.
Are there any way of how to get around this, VBA-codes or other formulas that uses less power, not naming columns or what?
View 9 Replies
View Related
Oct 29, 2009
I have an excel sheet that has an onclose macro, and on certain computers i get an error message stating out of memory. can anyone explain why this is and how i could avoid it, by possibly clearing the memory first or something?
View 9 Replies
View Related
Aug 22, 2008
i have a spreadsheet on Excel 2000, which is 256 column's wide and 4000 long the only formulas are countif which start at row 1000-4000 and are copied to column 256. Just for the one sheet it is 14mb is this normal
View 11 Replies
View Related
Mar 31, 2009
I am currently using Excel 2003 to pull from 2 external spreadsheets to compile data. The first sheet (seniority list.xls) being referenced contains all of our employees (approximately 350 rows, but that can vary from 340 to 450) as well as some information (employee ID, full time or part time, etc.). The second sheet being referenced (pay.xls) contains a breakdown of all employees and their pay for a particular bi-weekly period.
What I'm looking to do is:
(1) Find out all of my current employees. I do this by referencing seniority list.xls.
(2) For each employee, find out how many hours they worked each week. I need to do this by referencing pay.xls. Unfortunately, that file works quite differently than seniority list.xls. Unlike the latter, which contains one employee number per row, pay.xls could contain as many or as few rows per employee as their schedule dictates. Each row contains things such as overtime, regular time, double time, alternate regular time, alternate OT, vacation, sick pay, etc. In total, pay.xls could contain as many as approximately 8000 rows. To determine how many hours they work, I'm using this CSE (array) formula:
(Note that the employee ID is in column A of the current sheet.)
View 9 Replies
View Related
Dec 23, 2013
I have a large amount of data on NBA teams and players that is extracted from external web pages. In order to have the data shown on separate sheets to do comparisons I have to use many array formulas and from what I have read that takes up a large amount of memory. I'm now at the point where I'm receiving a message that says "Excel cannot complete this task with available resources" when I open the workbook.
Is there any plausible way that I can resolve this situation? I have thought about converting the lookup formulas from arrays (index/match) to vlookups and hlookups.
I'm not as advanced with using Excel as most of you, and I realize I may be at a point to where I will need to use different software to analyze my data, but I would prefer to stick with Excel as it would be difficult to start from scratch and learn how to use a different program.
View 3 Replies
View Related
Nov 3, 2008
Using Excel 2007, Windows XP, 4gig ram,
I got this "Insufficient Memory .... close some application" message, once in a while.
I know that i got enough memory to run my Excel.
This is a random problem. Memory as been checked with mem86 and it work fine
View 9 Replies
View Related
Oct 4, 2006
Regarding the Memory Leak problem with ADO, does anyone know of a workaround?
The workaround mentioned on Microsoft's website (http://support.microsoft.com/default...;en-us;Q319998) pertains to using ADO to retrieve a recordset from an Excel worksheet, not from an Access Database.
1) Does anyone know if those workarounds would also address retrieving a recordset from an Access Database?
2) If so, is the "SELECT INTO" workaround effective?
Is this Memory Leak issue relatively minor or only affecting Excel 2000? I'm guessing it must be one of those 2 things since the concensus is ADO is superior to MSQuery..
View 2 Replies
View Related
