Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dr. Watson with OnTime in Excel VBA Macro

Posted on 2001-08-16
8
Medium Priority
?
950 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 600 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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