I have an access database, for reporting I have a number of routines that each create an excel workbook with multiple worksheets of tables and charts.
All of these routines run fine and without error the first time I execute them. But if i execute any of them immediately after running another one I get an error "Object Variable or With Block Variable not set". I have to close the database each time re-open and it runs fine.
You have an interesting point, I have had some suspicions about that. I have to confess I am never entirely sure which variables need to be cleared and the best way to do so. The code is quite long (I'm not the most efficient programmer) it generates a 5 sheet workbook with a lot of charts and has 51 variables. The ones I clear are shown below:
14470 rst.Close
14480 rst2.Close
14490 rst3.Close
14500 qdf.Close
14510 qdf2.Close
14520 qdf3.Close
14530 db.Close
14540 Set rst = Nothing
14550 Set rst2 = Nothing
14560 Set rst3 = Nothing
14570 Set qdf = Nothing
14580 Set qdf2 = Nothing
14590 Set qdf3 = Nothing
14600 Set db = Nothing
14610 wbk.Close
14620 Set rng = Nothing
14630 Set cht = Nothing
14640 Set wsht = Nothing
14650 Set wbk = Nothing
14660 Set xl = Nothing
Dim db As Database
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim wsht As Worksheet
Dim cht As Chart
Dim rng As Range
Dim qdf, qdf2, qdf3 As QueryDef
Dim rst, rst2, rst3 As Recordset
Dim xComment As Comment
It looks like I haven't covered xComment! Have I cleared the others correctly? I am never sure what the difference is between closing something and setting it to nothing.
Object Variables are:
It looks like I haven't covered xComment! Have I cleared the others correctly? I am never sure what the difference is between closing something and setting it to nothing.
Hi. Closing is a method of the object. If it didn’t have a Close method, you would get an error. Closing doesn’t release the variable from memory. However, you have unintentionally declared some of your variables as Variants.
OK I have double checked all my code, prefixed all declarations for excel objects with "excel." and double checked that the workbook is closed and all object variables are set to = nothing. But still get error.
OK I have double checked all my code, prefixed all declarations for excel objects with "excel." and double checked that the workbook is closed and all object variables are set to = nothing. But still get error.
A long shot but you might try the following steps:
1. Reboot your computer
2. Open Task Manager
3. In the Processes tab, click on CPU to sort the running apps to the top
4. Open your database and run the code
5. Check all the running app in Task Scheduler while the code runs
6. After the code runs, do you see any process that was opened by Access but didn't go away?
7. Try running the code in Access again
8. Did any application process get duplicated in the Task Manager
I will preface my comments with a disclaimer: We have two schools of thought on whether it is either necessary or proper to close objects created using VBA. Various of our members have found some documentation and have gotten some responses from Microsoft that suggests that closure is generally a good idea in many cases, particularly after some troubling Windows security patches.
IF you create something in a subroutine or function, the variable related to that thing normally goes away when the sub/function returns to its caller. Simple variables vanish when the stack context vanishes during the return process. Isolated recordset operations probably don't matter, but repeated returns (say, inside a long loop) that don't close a recordset have been SUSPECTED of causing "resources exceeded" types of errors. Scrupulous closure might help.
However, if you created an application object and that object was used to open some external file, it is possible that the app (and thus the file) is still open after that return. In cases where there is another utility like Excel or Word that you open from a sub/function, it is recommended that you use the app object to close whatever you opened with it before you leave the sub/function. If there is a further chance that the app can have a life of its own (again, Excel and Word are the best examples), it isn't a bad idea to tell the app to .Quit if that is an option available for the app.
All of these routines run fine and without error the first time I execute them. But if i execute any of them immediately after running another one I get an error "Object Variable or With Block Variable not set". I have to close the database each time re-open and it runs fine.
This generally points to a "side effect" of using something that isn't purely local to the sub/function. When you close and re-open the database, you technically release all of the memory you WERE using and get a whole new batch of memory that you had not been using. The fact that a fresh chunk of memory works but trying to just re-run the code does not means that you have bollixed up something in memory and whatever cleanup you do has missed it. You say this line fails...
Code:
ActiveWindow.DisplayGridlines = False
ActiveWindow is a "special" pointer that is part of the VBA environment for Excel. It is in some ways similar though not identical to the "Me" construct for use in Form Class Modules. It is a dynamic rather than static reference because it depends on what was activated and can point to different things at different times.
As a debugging suggestion, if you can put a breakpoint on that instruction, the rule for VBA is that you break BEFORE executing the line containing the breakpoint. If you take the breakpoint, you can open the Locals Window and can easily examine what is in the ActiveWindow variable. You can verify whether in fact ActiveWindow doesn't point to what you expected. (It might be "Nothing" but it might be an unexpected something.)
Once you know if/where ActiveWindow points the second time through, you have a better chance of fixing this problem. So place the break, run it the first time, and note what was in ActiveWindow when you ran the code successfully. Then run another case of what you were testing, take the break, and see what changed.
A long shot but you might try the following steps:
5. Check all the running app in Task Scheduler while the code runs
6. After the code runs, do you see any process that was opened by Access but didn't go away?
7. Try running the code in Access again
8. Did any application process get duplicated in the Task Manager
5. the only two apps that run are EXCEL.EXE *32 and MSACCESS.EXE *32
6. after the code runs the CPU goes to zero for both
7. the same two apps run again
8. no duplication
yes, review all codes. if the method requires a sheet, qualify it, ie: sht1.Cells(), etc.
when in doubt of which is the parent object of that method., google the method name.
