Delay Calculation Of Worksheet Functions
Nov 9, 2006
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
View 2 Replies
ADVERTISEMENT
Dec 3, 2009
Is there a way to delay the result of a cell formula by three seconds or so? For example, I need to delay the result value of cell B12 for 3 seconds.
View 9 Replies
View Related
Oct 20, 2009
I have used Chip's StringConcat code [url]
When I first entered the function code as a formula on the worksheet, the result was instant and correct.
However, if I change the contents of one of the cells in the stringconcat range, the result does not change.
I need to edit (eg F2 then enter) either the cell with the text or the cell with the formula for the result to update.
If I highlight the column where the formula is used and Find / Replace to change "=" for "=", they all update at once (tip I learn't here)
Has anyone experienced this or have any ideas why it may happen?
Notes:
I am using Excel 2000 (work)
I have 6 cols of info (I to N),
These 6 columns contain formulae that basically copies plain text from the same row in Cols A to F. (Since not all the left hand side cells are full, the formula copies down the value above if the cell isblank, or copies the cell value if there istext)
Formula is in Cell G2 and reads
=stringconcat("; ",TRUE,I2:N2)
Code is stored in the Module1
I have 1 header row (Row 1)
I have 16 rows of text (Rows 2 to 17)
Not all rows have text in all 6 columns, but the are no blank cells betweeen data working left to right
I need our IT Admin to install Excel Genie!
View 9 Replies
View Related
May 23, 2007
I don't know any way ask this question without attaching an example. But the problem itself is pretty easy to explain. On the attached worksheet, whenever anyone makes a change to the information in column C (labeled as Act. Chicks) this triggers a worksheet change that makes that cell have a red font. It also triggers a routine (in a standard module) that updates textboxes that continually display a running total.
Here is the problem: After the information is entered, and the routines have finished executing, there is an incredibly annoying delay of about three seconds. That is, it takes three seconds before you can even select a different cell. This is very difficult for the user to deal with, especially when she has a great deal of information that she would like to enter very quickly. What is strange to me is that if I run the routine for the Worksheet Change within the VBE, I get the delay after the last line of the code (in the standard module) has been executed. In other words, I can't see any code that is running during this long "pause." What is the computer doing during this time, and what can I do to fix it?
View 7 Replies
View Related
Jan 15, 2007
Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:
= left(worksheet name! row(1) column.range("startno"),2
View 10 Replies
View Related
Jul 9, 2014
I use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).
My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.
My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.
View 9 Replies
View Related
Aug 2, 2009
I've been reading about usage of worksheet functions in macros, and I guess I'm still not getting it. What I want to do, is use Countif and Indirect worksheet functions in a macro but it always fails. Here is my formula
IF(Worksheetfunction.Countif(Worksheetfunction.INDIRECT("[DaysWorking.xls]'Collect No Stats'!$A:$A"),'2009'!Range("D" & x))>0)
Basically, I want to see if 2 different cells in 2 different workbooks match and then I would use the information to write different info in another cell. X is just a variable in a DO WHILE LOOP. Can these functions be used in a macro? Is there an alternative?
View 3 Replies
View Related
Aug 4, 2009
I have an excel problem with formula. I’m not sure of the formula I think its sumif but don’t know how to express the criteria . I am trying to create a spreadsheet that will total the data from Colum B, C & D. I have 3 columns with the following data.
Column B Heading Length
Column C Heading Width
Column D Heading Depth
Column E Heading LM (lineal meters) will have the data only from column B. Column F Heading M2 will equal the data of column B & C (remembering the math is M2 = column B x Column C. Column G Heading M3 will equal the data of Column B, C & D (remembering the math is M3 = column B x Column C x Column D. That’s the easy part but in columns H to J, I have other headings and column K is my Rate.
Column L is my subtotal and this is where my problem lies, because the formula is =SUM(E1:J1)*K1 it has the potential to double up on figures when I use the dropdown formula for Columns E,F, and G all data is carried down with them. I want Column E to show only the value, if column B has a number and Columns C & D are empty. I want Column F to show only the value, if columns B & C have a number and Column D is empty. I want Column G to show on the value, if all columns have a number. If all these conditions are met, then only a value will be displayed in either column E, F, G Remembering that the values returned must be multiplied by the value of each number in the columns.
View 3 Replies
View Related
May 16, 2006
How do I use excel functions in VBA code.
For example I do I use the max function which, gives the maximum value
from a range.
View 4 Replies
View Related
Jun 19, 2009
I have a spreadsheet which has extra worksheets at the end that the user can move around, if they need them.
Then i found out that users were adding there own sheets, so i protected the workbook, but i found out that this stops the user from moving the sheet.
Whats the easiest way probably using code to disable the worksheet functions, ie the ones that appear if you click on the tab (insert,delete,rename,move+copy,select all sheets etc) but still allow the user to move the sheet within the workbook
View 7 Replies
View Related
Aug 29, 2009
I am learning to use the index and match worksheet functions.
I read through some examples and tried to set up a spreadsheet as attached.
For some reason, it is working only partially. For some cells the value is returning correctly and in some it is returing #ref.
View 2 Replies
View Related
Jun 23, 2009
I have a spreadsheet using Data Validation to offer a list of text values for cells in the range E6 - E100. I have a worksheet change funtion to change the colour of the entire row based on the text chosen from the list.
What I would like to add is an input box that will appear when the value in the cell = "COMPLETE" asking for a completion date and adding the inputted date to the cell in column I in the same row.
Existing Worksheet Change Code;
Private Sub Worksheet_Change(ByVal Target As Range)
'Colour code rows based on order status
Dim rng As Range, i As Long
Dim cell As Range, Answers As Variant
Dim Colors As Variant
Colors = Array(24, 15, 38, 44, 42, 20, 36)
Answers = Array("CLOSED", "SUSPENDED", _
"COMPLETE - Awaiting Inspection", "COMPLETE", "WORKING", _
"SCHEDULED", "READY")
Set rng = Range("E6:E100")
rng.EntireRow.Interior.ColorIndex = xlNone
For Each cell In rng
For i = LBound(Answers) To UBound(Answers)
If LCase(cell) = LCase(Answers(i)) Then
cell.EntireRow.Interior.ColorIndex = Colors(i)
Exit For
End If
Next i
Next cell
End Sub
View 9 Replies
View Related
Dec 10, 2013
What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB:
Function SHEETOFFSET(offset, Ref)
[COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR]
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
View 8 Replies
View Related
Nov 19, 2009
I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?
View 4 Replies
View Related
May 2, 2008
Broadly, my workbook contains 5 worksheets. Worksheets 1, 2 and 3 contain calculations, worksheet 4 contains a summary of calculations from worksheets 1, 2 and 3. Worksheet 5 is a data table used for worksheets 1, 2 and 3.
I need the worksheets to calculate in the following order:
1. Worksheets 1 and 2 extract data from worksheet 5.
2. Worksheet 4 captures that data.
3. Worksheet 3 uses the data from worksheet 4 and extracts data from worksheet 5.
4. worksheets 1 and 2 recalculate using the calculation from worksheet 3.
5. Worksheet 4 captures the refreshed data from worksheets 1 and 2.
Is there a way, using formulas in the worksheets, to ignore worksheet 3 on the first iteration of worksheets 1 and 2, and prevent recalculation on the second iteration of worksheet 1 and 2?
In my mind I can see Excel simply recalculating over and over again, or will it stop. Is there actually a "problem" here or am I perceiving something that will not occur?
View 4 Replies
View Related
Oct 14, 2009
I use VBA's WorkSheet_Calculate event to detect when a cell of particular interest calculates to a specified value. It works well as long as there is only one worksheet (tab). However, when I duplicate that tab (and the final app could have up to 10 copies) to run a different set of data concurrently, I get bad results. The data is online, real time trading data, with each selected stock being tracked in a different tab.
Apparently both (or all) tabs in the workbook react to the same event, whereas I would assume that the event routine in each tab would react only to a calculation in that tab. If this is in fact the case, is there a way to a) make the event routine in each tab respond only to a calculation in that tab, or b) upon a calc event anywhere, determine which tab it occurred in?
Here is the present event code; right now it reads exactly the same in every tab. As long as nothing is happening in any other tab, it does its job correctly.
View 4 Replies
View Related
Dec 22, 2009
I am trying to do is trigger an event when a cell changes, and this cell is updated via a linked drop-down box.
The issue is that the code seems to work fine (in about 10 seconds or less) if a manual calculation is entered into the sheet - but when it's done via the drop-down box, it takes about 3 minutes to complete the macro.
View 3 Replies
View Related
Feb 14, 2013
I have a rather large spreadsheet that takes a long time to calculate. One sheet has the data (about 2800 rows by 650 columns), with samples in rows and data for each sample in the columns. A second sheet has functions to group the data according to predefined patterns. That second spreadsheet is about 800 columns wide and as many rows as I need it to be, as the samples don't need to be analyzed all at once.
Ideally I would like to do all samples at once by making the second spreadsheet 2800 rows high, but the calculation time is just way too long. And there's the problem. The calculation time seems to increase exponentially with the number of rows I calculate at a time. I ran a few tests making it different sizes, and here are the results:
rows
time(seconds)
100
3.5
150
12
200
23
[Code]...
I tried both HLOOKUP and INDEX/MATCH functions and it didn't make a lot of difference. These numbers are for INDEX/MATCH.
Graphing these numbers show that it follows an exponential curve pretty closely. What is interesting is that the progress indicator at the bottom of the screen finishes in a few seconds, about 5 seconds for 350 rows, and I can see the new data after those few seconds. But then it sits and appears to do nothing for the rest of the time. So, for 350 rows, it looks to be done with the calculations within 5 seconds, but then it freezes for another 2 minutes with CPU usage maxed out before I can do anything with the program. That actual calculation time seems to scale linearly with the number of rows I calculate, but the time after it finishes calculating before it finishes whatever else it's doing scales exponentially.
The spreadsheet does have a macro and a couple of other sheets and a macro that do some later analysis and I wondered if that had something to do with it. To check that, I created a new spreadsheet and set it up the same as the original one, with the data in one spreadsheet and the INDEX/MATCH formulas in the other. I copied the data over, but setup the formulas new without copying/pasting anything. It didn't make any real difference.
View 2 Replies
View Related
Apr 21, 2009
Is there a way that I can set a single worksheet in a workbook to always be on manual calculation, but keep all other sheet in the workbook set to automatic?
I want to be able to open the workbook, any calculations to perform automatically and then i will select the 'manual' sheet and perform these calculations manually. I also need to these settings to always apply each time i open the workbook.
View 13 Replies
View Related
Jan 29, 2010
I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies
View Related
Jun 1, 2008
I have workbooks in which summary tables are generated by lookup formulas. Tables are on different sheets but all use the same lookup value by referring to a cell on the 'master' sheet. The lookup value appears in cells on all sheets, by reference to the master sheet (e.g. [formula] = mastersheet!$B$2).
The master sheet contains the main summary table and is copied and detached for distribution, using a macro. For practical reasons, this is done in two stages, first copying the sheet within the workbook (to make minor alterations), then moving it to a new book to save and distribute.
This is where the problem arises. After the sheet has been detached, we find that if we now change the lookup value on the master sheet in the original file, the tables on that sheet will update normally, but the cells on other sheets remain frozen at the previous value and the tables on those sheets do not update.
The only way round the problem is by Shift + Ctrl + Alt + F9. (Maybe I should add that all my workbooks are always set to automatic calculation.)
The macro itself is not the cause of the problem; if we follow the same procedure manually, the result is the same. However, if we move the sheet in one step, eliminating the intermediate copying stage, the problem does not arise. But this is evading the problem, not solving it, and I would be reluctant to have to resort to this.
The original problem remains as stated, viz. failure of automatic calculation.
View 5 Replies
View Related
Jun 13, 2008
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data.
In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
View 3 Replies
View Related
Sep 23, 2007
I can download my data from the web. I can set it to download every so many minutes. But, I have to select run on my vb program to complete calculations and put data in correct format/sheets. I am trying to put a delay/timer in the program so it will refresh on itself and I will not have to keep selecting program run. If I put this in program it appears to refresh in a timely manner however I do not get any data on the sheets ? I have tried the wait routine and the if now = time + routine
View 11 Replies
View Related
Sep 26, 2008
I'm downloading data from a database that takes sometimes up to a minute to download the data. However I have a sum function in the macro that calls the cells that the downloaded data will be in. The problem is the sum function is occuring before the data is downloaded so the sum is always 0. Any way to build in a delay timer?
View 9 Replies
View Related
Jul 13, 2006
is it possible to put a delay of 1 second between these 2 lines?
Private Sub cmdsave_click()
Sheet14. Range("c1000").End(xlUp).Offset(1, 0) = Sheet14.Range("c1000").End(xlUp)
1 second delay here
Sheet14.Range("c1000").End(xlUp).Offset(1, 0) = Sheet14.Range("c1000").End(xlUp)
End Sub
View 7 Replies
View Related
Mar 11, 2009
I want to close a spreadsheet on a timed event. i can manage the timed close but i need to reset the timer if any action occurs. I have attached what i have so far...
View 4 Replies
View Related
Sep 23, 2007
I’m using live data and was wondering if it’s it possible to trigger B1 to change its value from ‘no’ to ‘yes’ 20 seconds after cell A1 changes its value from 0 to 1?
View 9 Replies
View Related
Feb 13, 2008
If for example:
I place number 7 in cell A2, A3, A12 and A22. I want to know the delay this particular number has. Looking at the example placed before the pattern should be the following (A2) 0, (A2-A3) 1, (A3-A12) 9, (A12-A22) 10. After A22 there's is no more number 7 therefore the delay in the example is of 3 i want this to change automatically until the next 7 appears. I've given an example, from C1 to P1 i have written 14 numbers i would like to know the delay of all these numbers.....
View 9 Replies
View Related
Feb 24, 2014
Trying to find a way to close outlook from excel workbook close. If outlook is open then delay excel workbook 45 seconds from closing.If outlook is not open then close excel normal. Here is what I've tried with no luck
[Code] .....
View 3 Replies
View Related
Jan 15, 2009
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.3"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.4"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.5"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.6"
etc . . .
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this??
Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
View 4 Replies
View Related
