Loosing variables help needed
 

I have created a purchase order form automating many functions using
VBA in Excel97. One problem I seem to be having is that when users
take a long time to file out the form they end up with a runtime error
caused be the loss of variables. How can I ensure all variables remain
defined until the user exits the worksheet?

--
BrianG

Loosing variables help needed
 

Variables don't get lost over time by default. It think you would need to
identify why your variables are being lost. Is it a simple scoping problem
or do you have code that runs that uses END (End by itself).

--
Regards,
Tom Ogilvy


"BrianG" wrote:

I have created a purchase order form automating many functions using
VBA in Excel97. One problem I seem to be having is that when users
take a long time to file out the form they end up with a runtime error
caused be the loss of variables. How can I ensure all variables remain
defined until the user exits the worksheet?

--
BrianG

Loosing variables help needed
 

Not a problem I have seen associated with time. Variables (by which I
presume you mean the values in the variables) are lost either because
they have gone out of scope or because the code has stopped and
restarted.

In various places where I am expecting this to happen I create a class
object with properties for the variables. Then declare ist as a NEW
global object, so that if it doesn't exist when it is referred to it is
created. Then in the Termainate method provide a routine to store all
the values (eg on a worksheet or a text file or anywhere) so that on
initialise you can read these values in to make sure they are the
proper values.

Note that you will need a controlled terminate or controlled open or
initialise to reset values to zero/null states.

Cheers

there are many other great benefits from using class modules for this
(and not bnecessarily global ones, you can put breakpoints within the
class module to see when variables are being used or in the terminate
method to see when they are going out of scope.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=544672

Loosing variables help needed
 

In my form I'm trying to force users to enter values in specific cells
by preventing users from continueing to complete the form without date
enter in the specific cells. I haven't been able to locate any reason
for the variable values to be dropped so it may be something in my
coding. In trying to check for values in specific cells I'm setting
Public variables (as Range) to the range of the cell I'm wanting to
check. I'm then checking the value of that variable to see if it is
blank as exampled below. Is there a better (more reliable/easier) way
of doing this?

======
Public objDate as Range
set objDate = Range ("h15")
..
..
Call Check_Date
..
..

Sub Check_Date
If objDate.Value = "" Then
..
..
..
End Sub
======

--
BrianG

Loosing variables help needed
 

Not a full reply but:
1. make sure you are using Option Explicit this requires the
declaration of all variables - good programming practice and solves a
host of unintentional errors.
2. use the debug options Add Watch and break if value changes. This
might help

3. Where is the code? eg in a module or in a workbook event?

There are almost always better ways to write code.

Regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=544672

Loosing variables help needed
 

1. make sure you are using Option Explicit this requires the
declaration of all variables - good programming practice and solves a
host of unintentional errors.

Should Option Explict be in every module?

2. use the debug options Add Watch and break if value changes. This
might help

I have not been able to duplicate the problem. Multiple users have
experienced the problem though. The only common denominator I can
identify
is time. When it happens they have all taken an extended period of
time to
complete the form (10's of minutes rather than minutes).

3. Where is the code? eg in a module or in a workbook event?

Module

There are almost always better ways to write code.

Agreed, which is why I ask here. <g

--
BrianG

Loosing variables help needed
 

If you store the ranges in a class object you can put breakpoints on the
instantiate and termainate procedures and you will then trap where it is
happening.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=544672