Entering numbers is very slow

[Jym396]

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

 

[Joe4]

Any VBA code in your workbook?

 

[Jym396]

Yes, 1 only. Would you like the code?

 

[Joe4]

Yes, it would be helpful to see that code.

 

[Jym396]

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

 

[Joe4]

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]

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.

 

[Joe4]

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]

I have not done any of the aforementioned. I thought maybe I was using too many volatile functions in that sheet. No?

 

[Joe4]

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).