I'm running Excel 2003 under XP. Sometimes if I run an optimization
routine for an extended period VBA occasionally seems to get confused
and produce an error if the computer enters "sleep" mode. Not always,
but unpredictably. Then the computer hangs up waiting for me and I'm
not there to help it.

is there something I can do with VBA every ten minutes or so to give
XP a small keep awake nudge? When I think to do it, I manually turn
off the sleep mode before running a long optimization, but
unfortunately the human element (me) is not reliable enough.

Thanks.

Bill

I've set my computers not to enter sleep mode at all if they are desktops,
or if they are laptops which are plugged in.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Bill Martin" wrote in message
...
I'm running Excel 2003 under XP. Sometimes if I run an optimization
routine for an extended period VBA occasionally seems to get confused
and produce an error if the computer enters "sleep" mode. Not always,
but unpredictably. Then the computer hangs up waiting for me and I'm
not there to help it.

is there something I can do with VBA every ten minutes or so to give
XP a small keep awake nudge? When I think to do it, I manually turn
off the sleep mode before running a long optimization, but
unfortunately the human element (me) is not reliable enough.

Thanks.

Bill

You could use two OnTime macros that call each other every 15 minutes.

Sub WakeUp()
Application.OnTime Now + TimeValue("00:15:00"), "Nudge"
End Sub

Sub Nudge()
Application.OnTime Now + TiveValue("00:15:00"), "WakeUp"
End Sub

"Bill Martin" wrote:

I'm running Excel 2003 under XP. Sometimes if I run an optimization
routine for an extended period VBA occasionally seems to get confused
and produce an error if the computer enters "sleep" mode. Not always,
but unpredictably. Then the computer hangs up waiting for me and I'm
not there to help it.

is there something I can do with VBA every ten minutes or so to give
XP a small keep awake nudge? When I think to do it, I manually turn
off the sleep mode before running a long optimization, but
unfortunately the human element (me) is not reliable enough.

Thanks.

Bill

Here is something you can try out...

I found this snippet @
http://www.experts-exchange.com/Prog..._23186029.html

Run the first one before your code starts and the second one after your code
completes. Just set the times you want them to be set back to in the
"turn_on" procedure in minutes. Omit the hibernate mode if you do not need
it.

Mark Ivey

Sub turn_off_standby_and_hybernation_modes()
''' Before starting your code
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "cmd /c powercfg /change ""Always On"" /standby-timeout-ac
0", 0, True
objShell.Run "cmd /c powercfg /change ""Always On""
/hibernate-timeout-ac 0", 0, True
objShell.Run "cmd /c powercfg /setactive ""Always On""", 0, True
Set objShell = Nothing
End Sub


Sub turn_on_standby_and_hybernation_modes()
''' After starting your code
''' Set the appropriate times in minutes below
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "cmd /c powercfg /change ""Always On"" /standby-timeout-ac
30", 0, True
objShell.Run "cmd /c powercfg /change ""Always On""
/hibernate-timeout-ac 60", 0, True
objShell.Run "cmd /c powercfg /setactive ""Always On""", 0, True
Set objShell = Nothing
End Sub

On Sun, 2 Mar 2008 12:24:01 -0800, JLGWhiz
wrote:

You could use two OnTime macros that call each other every 15 minutes.

Sub WakeUp()
Application.OnTime Now + TimeValue("00:15:00"), "Nudge"
End Sub

Sub Nudge()
Application.OnTime Now + TiveValue("00:15:00"), "WakeUp"
End Sub
-----------------------------------

That's a very tidy, neat solution, but... Can you explain how this
keeps the OS from entering sleep mode? I'm already madly calling a
bunch of interrelated subroutines.

Is the "Application.OnTime" statement itself going to somehow keep the
OS from sleeping?

Thanks.

Bill

On Sun, 2 Mar 2008 16:37:22 -0600, "Mark Ivey"
wrote:

Here is something you can try out...

I found this snippet @
http://www.experts-exchange.com/Prog..._23186029.html

Run the first one before your code starts and the second one after your code
completes. Just set the times you want them to be set back to in the
"turn_on" procedure in minutes. Omit the hibernate mode if you do not need
it.

Mark Ivey

Sub turn_off_standby_and_hybernation_modes()
''' Before starting your code
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "cmd /c powercfg /change ""Always On"" /standby-timeout-ac
0", 0, True
objShell.Run "cmd /c powercfg /change ""Always On""
/hibernate-timeout-ac 0", 0, True
objShell.Run "cmd /c powercfg /setactive ""Always On""", 0, True
Set objShell = Nothing
End Sub


Sub turn_on_standby_and_hybernation_modes()
''' After starting your code
''' Set the appropriate times in minutes below
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "cmd /c powercfg /change ""Always On"" /standby-timeout-ac
30", 0, True
objShell.Run "cmd /c powercfg /change ""Always On""
/hibernate-timeout-ac 60", 0, True
objShell.Run "cmd /c powercfg /setactive ""Always On""", 0, True
Set objShell = Nothing
End Sub

-----------------------------

Thanks Mark. I'll play with that as well as with the JLGWhiz approach
and see what I can get to work.

Bill

On Sun, 2 Mar 2008 15:00:10 -0500, "Jon Peltier"
wrote:

I've set my computers not to enter sleep mode at all if they are desktops,
or if they are laptops which are plugged in.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
----------------------

The thing is that I like to use sleep mode 99% of the time when I'm
not at my machine John. The only time it seems to get me in trouble
is occasionally with Excel/VBA optimization routines.

This is in contrast to hibernation which seems to burn me in a number
of ways, so I don't use that except when a laptop lid gets closed.

Bill

You would need to program procedures named Nudge and WakeUp, using code like
Mark has provided. Application.OnTime doesn't do anything for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Bill Martin" wrote in message
...
On Sun, 2 Mar 2008 12:24:01 -0800, JLGWhiz
wrote:

You could use two OnTime macros that call each other every 15 minutes.

Sub WakeUp()
Application.OnTime Now + TimeValue("00:15:00"), "Nudge"
End Sub

Sub Nudge()
Application.OnTime Now + TiveValue("00:15:00"), "WakeUp"
End Sub
-----------------------------------

That's a very tidy, neat solution, but... Can you explain how this
keeps the OS from entering sleep mode? I'm already madly calling a
bunch of interrelated subroutines.

Is the "Application.OnTime" statement itself going to somehow keep the
OS from sleeping?

Thanks.

Bill