EDC
asked on
Dr. Watson with OnTime in Excel VBA Macro
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Sub GoPauseGo()
Range("B10").Select
Application.OnTime Now + TimeValue("00:00:05"), "GoB4"
End Sub
Sub GoB4()
Range("B4").Select
End Sub
ASKER
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?
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?
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
8->
Marcus
One last idea, if you are putting it in the module have you tried making it a function instead of a sub?
ASKER
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.
I'm going to attempt to split the points.
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
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
Sub GoPauseGo()
Dim valTime
Range("B10").Select
valTime = Now + TimeValue("00:00:05")
Application.OnTime valTime, "!GoB4"
End Sub