Entering numbers is very slow

Jym396

Board Regular
Joined
Jan 15, 2007
Messages
72
Office Version
  1. 2007
Platform
  1. Windows
Workbook with 3 sheets: Timesheet, Charts, Data.
ONLY the Timesheet when I enter a number or time, calculation is slow (~5-6 secs).
It does not happen with alpha characters.
Dimension for Timesheet is A1:AG566
A few vlookups and many if statements.
The thing that puzzles me is this happened suddenly.
Many of the cells are stand alone and not a precedent or dependent.

Thank, -Jim
Windows 7 Pro
16GB DDR3
1TB SSD
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Any VBA code in your workbook?
 
Upvote 0
Yes, 1 only. Would you like the code?
 
Upvote 0
Yes, it would be helpful to see that code.
 
Upvote 0
Dim myCell As Range

For Each myCell In Worksheets("Timesheet").Range("C9:C546")
If myCell.Value = Date And myCell.Offset(, -1) = "" Then
myCell.Offset(0, 3) = Format(Now, "hh:mm am/pm")
myCell.Offset(0, 18).Value = Format(Now, "hh:mm am/pm")

Exit Sub
End If

Next
Beep
MsgBox " Day is closed...Time not updated."



End Sub

BTW: This VBA runs with no hesitation and it does enter values/numbers
 
Last edited:
Upvote 0
You left off the first line, which tells me some important things.
I am trying to ascertain if this is Event Procedure code that is called automatically, or is triggered in some other manner.
 
Upvote 0
The only thing I left out is this:
Sub ChartOut1()
'
' ChartOut1 Macro
' Updates Timesheet
'


'
I have it assigned to a picture. I invoke it myself.
 
Last edited:
Upvote 0
OK, so it is not Event Procedure code that is being invoked automatically.

I think the key to focus on is here:
The thing that puzzles me is this happened suddenly.
Identify the date that this started happening, and identify anything that changed around then, such as:
- upgrades/patches, especially to Windows or Office, or security (Virus checking, etc)
- installation of new programs (especially Virus checking, etc)
- installation of new hardware to the computer
- changes to the network (especially servers where files are housed)
- significant changes to your data

Usually, if things have been working for a while, and then all of a sudden behave very differently, it is the result of something that changed recently. The trick is to try to identify what that is.
 
Upvote 0
I have not done any of the aforementioned. I thought maybe I was using too many volatile functions in that sheet. No?
 
Upvote 0
Possibly. I think Excel can only track so many dependencies (something like a million).
I have only ever seen anyone reach that limit once. After that point, it does a recalc after every data input, and it can be really slow.

If you ever reach that limit, that is a red flag that you are probably using the wrong tool for the job. If you have that many dependencies, then what you really have is a database, and you would be better off using a database program (like Access, SQL, or Oracle).

Out of curiosity, if you change your calculation mode to manual, does the issue go away?
I know that isn't optimal to leave it that way, but it might at least give some hints at what is going on. If it has no effect, then it is probably not calculations that are the issue, but maybe something else (something scanning, autosave, etc).
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top