Macro's maxing out CPU

graemeal

Active Member
Joined
May 17, 2007
Messages
316
Platform
  1. Windows
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

Code:
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

Code:
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

Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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