Entering numbers is very slow

Jym396

Board Regular
Joined
Jan 15, 2007
Messages
70
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,442
Office Version
365
Platform
Windows
Any VBA code in your workbook?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,442
Office Version
365
Platform
Windows
Yes, it would be helpful to see that code.
 

Jym396

Board Regular
Joined
Jan 15, 2007
Messages
70
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,442
Office Version
365
Platform
Windows
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.
 

Jym396

Board Regular
Joined
Jan 15, 2007
Messages
70
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,442
Office Version
365
Platform
Windows
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.
 

Jym396

Board Regular
Joined
Jan 15, 2007
Messages
70
I have not done any of the aforementioned. I thought maybe I was using too many volatile functions in that sheet. No?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,442
Office Version
365
Platform
Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,099,482
Messages
5,468,867
Members
406,617
Latest member
crowelbe

This Week's Hot Topics

Top