Solved

Dr. Watson with OnTime in Excel VBA Macro

Posted on 2001-08-16
8
942 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now