Solved

Dr. Watson with OnTime in Excel VBA Macro

Posted on 2001-08-16
8
945 Views
Last Modified: 2012-06-21
I have constructed the following test set of macros to check out the OnTime method (it moves the cursor after 5 seconds):

Sub GoPauseGo()
    Range("B10").Select
    Application.OnTime Now + TimeValue("00:00:5"), ThisWorkbook.Name & "!GoB4"
End Sub

Sub GoB4()
    Range("B4").Select
End Sub

If I put it in my Personal.xls, it works fine in a test workbook and also in my big workbook -- but if I put it in the workbooks' own modules, it works OK in the test workbook, but gives me a Dr. Watson Exception: access violation (0xc0000005), Address 0x00000000 every time in my big workbook.  What sorts of difference between workbooks could give rise to this behavior?
0
Comment
Question by:EDC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 150 total points
ID: 6394918
Have you tried this, when putting the procedure in the own workbooks' module?

Sub GoPauseGo()
   Range("B10").Select
   Application.OnTime Now + TimeValue("00:00:05"), "!GoB4"
End Sub

Sub GoB4()
   Range("B4").Select
End Sub


0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6395090
Using a variable like this

Sub GoPauseGo()
Dim valTime
  Range("B10").Select
  valTime = Now + TimeValue("00:00:05")
  Application.OnTime valTime, "!GoB4"
End Sub
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6395110
Oops, make that

Sub GoPauseGo()
  Range("B10").Select
  Application.OnTime Now + TimeValue("00:00:05"), "GoB4"
End Sub

Sub GoB4()
  Range("B4").Select
End Sub
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:EDC
ID: 6397199
Sorry, Calacuccia and xSinbad -- Those are valid procedures, but they don't work in my strangely behaving workbook.

Using the valTime variable ascertained that Excel is choking on the TimeValue function.

Here's another clue:  MsgBox doesn't work, either -- gives the same Dr. Watson, at address 0x0fb78e03 -- but works OK when run from Personal.xls, or in another workbook's module.  Could I be accessing just a subset of VBA functions somehow?
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6504899
How is this going EDC I cannot reproduce your fault, have you tried creating a new workbook and trying in that because this one looks corupt...


8->
Marcus
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6507888
One last idea, if you are putting it in the module have you tried making it a function instead of a sub?
0
 

Author Comment

by:EDC
ID: 6508836
Thanks for your efforts, xSinbad and calacuccia -- it seems that I did indeed have a corrupted workbook.  I moved everything over to a fresh file and things are working fine now.

I'm going to attempt to split the points.
0
 

Expert Comment

by:ComTech
ID: 6511108
Hi EDC, I have refunded 150 points for a split.  I have accpeted calacuccia's points here.

You nee to create an NEW question in this Topic Area and Entitle it *Points for xsinbad*

In the commmet box type *Points for xsinbad for your split in my question #20169698*

Choose 150 points and click on Submit, that will finish the split (of course, when he comments, just accept the comment and, it will close.

Regards,
ComTech
Community Support Moderator
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question