What version of Excel and Windows are you using?
Here's why I ask:
Excel (and Windows) attempts to determine if Windows application is taking "too
much time".
In a misguided attempt to manage CPU cycles so that it doesn't slow down your
computer usage,
it "relegates" Excel to a lower "priority" if it no longer has "focus".
That is, if Windows thinks you aren't "using" Excel, but instead are checking
out funny videos on the web, then it will choose to "demote" Excel and give your
entertainment the highest priority.
If you:
open your Task Manager
select the "Processes" tab
right-click the Excel process
Select "Set Priority"
You'll see the Priorities as:
Realtime
High
Above Normal
x Normal
Below Normal
Low
Now, in Excel versions prior to 2010 (or maybe 2007)
you could set the Priority to "Realtime", then the Excel process never
gets set to reduced priority.
This used to work just fine.
But in Excel 2010, it STILL seems to change (somewhat).
I have macros that run for 45 minutes (processing over 1.5 million records).
I normally run this macro in the wee hours of the morning.
Prior to Excel 2007, If I were to run it "interactively", once I select another
window
(and Excel was no longer the "focus"), this macro would then take over 4 hours
to run.
If I were to change the process "priority" to Realtime, then the macro once
again ran in 45 minutes.
I found (and modified) some VBA code that works to set the process priority when
I open the workbook. (see the end of the email)
But in Excel 2007 and 2010 (running on Windows7), this technique produced
limited results.
Setting the Priority works in the sense that the macro completes in the same
amount of time,
but after a specific number of cycles, the Excel window displays (Not
Responding)
and, even though the macro seems to be running correctly, the display is no
longer updating.
So, even though I have the statusbar updating every 10,000 records, it stops
updating until the macro is complete.
And.. it won't let me interrupt the macro.
So.. in MY case, the Excel application continues to run, but the screen is no
longer updating.
In order to monitor progress, in some cases, I have utilized a "writelog" macro:
=========================================
Sub test_write()
Dim stat, cnt
cnt = 0
stat = WriteLog("Project_Summary_Update: " & cnt)
End Sub
Public Function WriteLog(LogString)
Dim fLog, fso, LogFile
LogFile = "C:\temp\Project_Log.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.fileexists(LogFile)) Then
Set fLog = fso.OpenTextFile(LogFile, ForAppending, True)
fLog.WriteLine Format(Now, "ddd m/dd/yyyy h:mm:ss AMPM") & " : " &
LogString
fLog.Close
End If
End Function
=====================================
Using this, I can periodically check the log file and see how the macro
progresses.
I'm sure that your case is not exactly the same, but I suspect that the problem
is similar.
I suspect that your macro "priority" is being reduced to a "background" state,
because Windows thinks you're more interested in other things.
And thereby it looks like Excel is no longer functioning.
In my workbooks, (In a "standard" module) I use:
----------------------------------------------------------
Option Explicit
'
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As
Long, ByVal bInheritHandle As Long, ByVal dwProcessID As Long) As Long
Private Declare Function SetPriorityClass Lib "kernel32" (ByVal hProcess As
Long, ByVal dwPriorityClass As Long) As Long
Private Const PROCESS_QUERY_INFORMATION As Long = &H400
Private Const PROCESS_SET_INFORMATION As Long = &H200
Private Const NORMAL_PRIORITY_CLASS = &H20
Private Const BELOW_NORMAL_PRIORITY_CLASS = 16384
Private Const ABOVE_NORMAL_PRIORITY_CLASS = 32768
Private Const IDLE_PRIORITY_CLASS = &H40
Private Const HIGH_PRIORITY_CLASS = &H80
Private Const REALTIME_PRIORITY_CLASS = &H100
'
Public Enum ProcessPriorities
ppIdle = IDLE_PRIORITY_CLASS
ppBelowNormal = BELOW_NORMAL_PRIORITY_CLASS
ppAboveNormal = ABOVE_NORMAL_PRIORITY_CLASS
ppNormal = NORMAL_PRIORITY_CLASS
ppHigh = HIGH_PRIORITY_CLASS
ppRealtime = REALTIME_PRIORITY_CLASS
End Enum
'
Public Function ProcessPrioritySet( _
Optional ByVal ProcessID As Long, _
Optional ByVal hWnd As Long, _
Optional ByVal Priority As ProcessPriorities =
NORMAL_PRIORITY_CLASS) As Long
Dim thisProc As Long
ProcessID = GetCurrentProcessId()
thisProc = OpenProcess(PROCESS_QUERY_INFORMATION Or PROCESS_SET_INFORMATION,
0&, ProcessID)
Call SetPriorityClass(thisProc, Priority)
End Function
'
Public Sub TopPriority()
Dim myPriority As Long
myPriority = ProcessPrioritySet(Priority:=ppHigh)
End Sub
Sub Auto_Open()
TopPriority
End Sub
----------------------------------------------------------
let me know if this helps, or even makes sense!
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: Torstein Johnsen <sejohnse@yahoo.no>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Thu, November 22, 2012 5:10:56 PM
Subject: [ExcelVBA] counter problem
I'd like excel by VBA to count to 20 at different speeds (that is how long is
the pause before next number coming).
I have tried different methods but I find them all quite unstable, suddenly they
stop updating the counter and "excel does not answer".
I'm back to basic, letting a simple loop run for several times to create a pause
using this sub
Sub looptimeintervall()
timefactor = InputBox("pause value")
For i = 1 To 20
Range("h18").Value = i
For j = 1 To timefactor * 4600000
Next j
Next i
End Sub
Can anyone understanding more than me about whats happening inside excel tell me
why this sub is not functioning well/stop updating, when timefactor is about 10
and greater.
I have tried several other ways (wait, timer and so on) but they are not stable
either. Have any of you an idea of a stable counter sub?
Yes I will tell you later what this is for!
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar