[AccessD] Setting objects to nothing

Lambert,
Thank you for your input. I agree with your thought about y but I checked it
and it returns the expected value. I am going to test this idea further but
I am about to conclude that dimmed but unused objects can prevent excel from
properly closing. Regarding saving workbooks, generally I open a template
and add data using a function. If the operation was successful the function
returns true and I save the newly created workbook. If false I discard the
workbook and send a message that the workbook was not created. All this has
worked well for years. 

I agree with your comments about the goto statement. This is one of those
lines of code that worked and I have never bothered to improve. I will need
an exorcist to remove it from all the places I have copied it over time but
I will eventually have it cleaned up. I wish this was the only case of code
that should be rewritten as I've learned better ways to do things!

Regards,
Jim Hale

-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com]
Sent: Wednesday, October 05, 2005 4:21 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Setting objects to nothing


Re your comment about 14 and y: this strongly suggests that y is not being
set to the correct value in the first place (or is being altered later on).
In any case, where the number of elements in an array is (potentially) in
doubt I always use..

For x = lBound(SomeArrayName) to uBound(SomeArrayName)

... The next thing that confuses me (very easy to do!) is...

If blState = True Then .ActiveWorkbook.SaveAs FileName:=strFileSave

.ActiveWorkbook.Close SaveChanges:=False
End With

I'm not sure why you are trying to save the workbook to some specific file
name, but I assume that your code has changed the excel file that was opened
and you want to then save it to a *new* file with a different path/name. If
not, then why does the next line close the workbook without saving the
changes?

Now these routines do not allow you to open one file and save it under a
different name, but then I would instead copy the file to a new name first,
open the copy and change it any way I want, and then close the copy, saving
the changes.

One other comment, you have...

 If blState = False Then GoTo Err_Load_Financials

<soap box mode> 
The only time I ever need to use Goto is in setting up an error handler,
because that's the only way you can do it in VB/VBA. Otherwise I've never
needed to code anything using Goto. Plus, in your code it looks like the
Goto target really is an error handler, which means that if blState IS false
and your code jumps there, you will get another error when the Resume line
executes, because you did not enter the error handler legally (by raising an
error).
</soap box mode> 

Hope some of this helps.

Lambert

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.