Macro's maxing out CPU

[graemeal]

Hi there, just wondering are there any fixes for the below problem is is it normal for constant run macros to chew up resources.

I have 2 workbooks opened that extract live data via DDE. The information needs to be collected to the split second. All works well untill I start the macros. Then there can be a 6 second delay before the data reaches the cells. There are 20,000 DDE formula's and nearly 30,000 other formula's being crunches every split second between the 2 workbooks.
I have a quad core comp and notice that 1 core is pretty much running at 85-90% only once I start macros. Overall CPU usage is only 25% because the other 3 cores are hardly being used. It seem not to be that bad if one only workbook is opened so I am assumming it is because of the sheer number of formula's at play????.

Below are the macros in play.

Thanks

Public dTime As Date
Sub start_it()
On Error Resume Next
copy_data_to_column_J
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "start_it"
End Sub


Sub stop_it()
On Error Resume Next
Application.OnTime dTime, "start_it", , False
End Sub

Sub copy_data_to_column_J()
Windows("NOTS").Activate
Sheets("sm_secyg").Select
Calculate
Range("J2:J2000").Value = Range("E2:E2000").Value
Windows("TH").Activate
Sheets("TH").Select
Calculate
Range("J2:J2000").Value = Range("E2:E2000").Value
End Sub

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, 15)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

Sub The_Sub()
    Windows("NOTS.xls").Activate
    Sheets("sm_secyg").Select
    Range("A1:P1950").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Windows("TH.xls").Activate
    Sheets("TH").Select
    Range("A1:P1950").Sort Key1:=Range("H1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        Application.CutCopyMode = False
line1: StartTimer
End Sub

Sub StopTimer()

   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
 
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
 
Private Sub Worksheet_Calculate()
Const FName1 As String = "C:\WINDOWS\Media\ding.wav"
Dim h As Range
For Each h In Range("R2:R4")
    If h.Value = 1 Then
        Call PlaySound(FName1, 0&, SND_SYNC Or SND_FILENAME)
        Exit For
    End If
Next h
End Sub

XP 2.4 quad 4 XL2003

 

[gardnertoo]

One thing to look for in the 30,000 "other formulas" is volatile functions. If you are using either the NOW() function or the TODAY() functions (for example), they get recalculated every time anything on the sheet changes. Instead of having hundreds or thousands of cells each calculating what time it is, or what day it is, it will speed things up to have ONE cell do that, and refer to that cell in the thousands of formulas. Another thing to look for: cell conditional formatting is volatile, even if the formulas involved do not appear to be volatile in their own right.

Here's a link to a more detailed treatment of volatile functions.

 

[graemeal]

Thank you kindly