Solved

Dr. Watson with OnTime in Excel VBA Macro

Posted on 2001-08-16
8
943 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
The blinking Office button 4 43
convert publisher file to an outlook email template 8 49
Vlookup nonexact IP address match 32 87
Finding a closest match in Excel 7 45
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

770 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