I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line
Application.Volatile
to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :
Sub DropDown4_Change()
Application.CalculateFull
End Sub
but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.
So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?
And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?
My spreadsheet is very large & takes sometimes 5 to 10 seconds to recalculate. The problem is that is was wanting to recalc every time I edited a cell, which I do constantly all day long. Due to this, I have turned off automatic recalculation. Is there is a way I can use VBA to force just one cell or just one row to recalculate? Keep in mind that currently no cells recalculate until I hit F9 or go in & manually hit Calculate Sheet in tools > options > calculation.
Two questions: is there a way to not have the formulas in a range of cells, say from B2:AX20, evaluated during normal recalculations? I would like the formulas evaluated only if a form button is pressed.
How do I link the recalculation of the disabled cells to the form button?
The range of cells do not require updating unless certain other cells on a different worksheet are changed which happens rarely. However, other cells in the workbook do change so I would like to keep recalculation set to automatic. I searched the site as well as C. Pearson’s site and some others and have not found a solution. Any help would be appreciated because at present my workbook is extremely slow.
I want to create a macro that changes the calculation when opening excel to calculating without updating tables. When I first open excel, the file takes a while to update because it is updating tables, and I want to prevent this. Any ideas? Also, I already have the following code, but this only changes the workbook once its already opened:
Private Sub Workbook_Open() Application.Calculation = xlSemiautomatic End Sub
I have just written a function that sums all the values in the cells in a range that are not green. This works however if one of the non green cells is changed to green the function does not work. i have to re input it into the cell that i put it into.
Function SumNotGreen(SelectedCells As Range) ' Adds the values of the cells where the font colour is not green(35). Dim Cell As Object Dim x As Double x = 0 For Each Cell In SelectedCells If Cell.Interior.ColorIndex <> 35 Then x = x + Cell.Value End If Next Cell SumNotGreen = x End Function
How can i make the function recalculate i.e. go back into the loop every time a change is made.
I have this sheet full of random data and I want to recalculate extra fast so I keep my finger on F9 which causes the random data to randomize really fast of course. Now, in B1:AT1 I have numbers that change with every recalculation but here is the problem. I want the recalculation to stop when excel identifies a zero in that range which doesn't happen often.
I have written a custom function which is called in lots of cells. It had an error so I modified it. But the modification did not automatically trigger recalculation in the cells where it is used. Neither did F9 (manual recalculation). The only thing that did is hitting F2 for the cell then ENTER. But what a pain to do that for every cell it's used.
I have a workbook with a button to get updated data. The button simply opens the other workbook and then closes it. THe problem is it opens the book and then closes it before the opened book has a chance to finish it calculation resulting in lots of #value errors. The book that is being opened works fine but because it is shared it can't be left open and needs to be closed asap, though it should be allowed to finsih calcs before it gets closed again. If anyone know a way to make the following temporarily halt until the opened book finishes calculating before it closes it.
I have just set up a conditional format to change the colour of my cells in column F based on a yes or no value in column E. I have a colour function formula working to sum the totals of the cells coloured the 2 different colours seperately. Since applying the conditional format. the formula for colourfunction will only total 0. Is there a way i can apply the colourfunction formula to cells coloured by using conditional formatting
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).
I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)
which then compares the three-letter code with a list in the following format to create a new text file where data is only included if the code is on the list. Since I added the range search to the code it runs VERY slow
I required to do some Monte Carlo analysis for 1000000 simulation. I have managed to find some free code, however, the time it took to run 1000000 >30min. Is that normal? The code that it took the longest to run is following:
For i = 1 To number_of_trials Application.Calculate For j = 1 To number_of_formulas runs(j, i) = sel. Cells(1, 1 + j) Next j Next i
Is there any way I can implove this code to make it run faster? I have already tried Application. ScreenUpdating = False
I have a time (1:08:31) that it took to travel 35km. How can I calculate the average speed of this competitors plus a number of others who recorded faster or slower time?
I managed to convert the time to seconds but when I load a simple formula to convert to KPH it never works.
The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds.
1) Current Speed 2) Current Acceleration 3) Acceleration Growth
Assuming: Current Speed=0 Current Acceleration = 0.2 (each 'turn' the current speed will increase by this much) Acceleration Growth = 0.2 (each 'turn', the current acceleration will grow by this much)
This gives a current speed over a series of 'turns' as 0.0 + 0.2 = 0.2 0.2 + 0.4 = 0.6 0.6 + 0.8 = 1.4 1.4 + 1.0 = 2.4 2.4 + 1.2 = 3.6 3.6 + 1.4 = 5.0 5.0 + 1.6 = 6.6 6.6 + 1.8 = 8.4 8.4 + 2.0 = 10.4 etc.
What I'd like to do is have a formula (or some way other than calculating each step) to tell me how many turns it would take for the Current Speed to =>X (example 100)
Basically, Turns to X speed = something clever * acceleration growth * something else very clever.
I have a Frame on a UserForm. The Width of the frame depends on values given in TextBox.Dat1 and TextBox.Dat2.
Now I tried it with a width of 2200 with a scroll bar, but when I change the value in the Textboxes I need the Frame to be cleared. Now this is my question. I use "UserForm1.Frame1.Clear" but this can take up to 1 minute.
I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.
I've created a helper column where I've inserted the formula ....
i hv a excel file which have 200k row. recently i learn from this forum using VBA (excel macro code) for select some data for analysis. it take very long. any suggestion will be apprecaited.
i already increase my note book ram to 2GB. it still slow.
I am having a big problem with recalculating cells after running a macro. The macro runs at a great speed, but once the calculation is switched back to automatic, the cells are not calculating fast enough. I waited for 4 minutes and it still had not moved past 0%. The worksheet has approximately 9000 rows out to IG columns. 85% of the cells contain an if-then formula.
The formula: ={IF(AND(ISNUMBER($A3);($A3-DAY($A3)+1)=F$2);$D3;IF(AND(F$2 > ($B3-DAY($B3));F$2 < DATE(YEAR($C3);MONTH($C3)+1;0));$D3/DATEDIF($B3-DAY($B3);DATE(YEAR($C3);MONTH($C3)+1;1);"m");0))}
I need to use this formula for over more than 30.000 rows and more than 50 columns. Is it possible to speed up the formula? Or maybe to handle this by a macro?
