I have an excel purchase order form automated using macros. I have a
module named "Setup" in which I define all of my public variables (29
to be exact). In it is an "Auto_Open" sub in which I assign initial
values to the variables. When the users finish filling in the form and
try to print or save it I run a "Check_Entries" sub which checks to
insure specific cells contain data. With some regularity a user will
receive the "Object variable or With block variable not set" error. It
occurs on the first line of code of the "Check_Entries" sub that
contains a variable reference. Further investigation indicates that
defined variables have been lost. This happens about once in every 50
executions of the .xls. It occurs on different machines, different
users and both XP SP2 and Win98SE OS's. I have not been able to
intentionally duplicate the error. Can anyone give me any ideas as to
what might be going on?

BrianG

Do you have any "End" lines in your code--not "end if", "End with" "end
sub"--just plain old End's?

if a=b then
end
else
'do something
end if

End's will reset those variables.

I think I'd get rid of them somehow.

And you may want to create a dedicated subroutine that initializes those
variables.

Then you could use:

Public VariablesAreInitialized as boolean
sub initializeVariables()
'do all the initialization
variablesareinitialized = true
end sub

Then in each of your subroutines:

if variablesareinitialized = false then
call initializeVariables
end if

(assumes that you don't do anything to those variables once they're
initialized.)



wrote:

I have an excel purchase order form automated using macros. I have a
module named "Setup" in which I define all of my public variables (29
to be exact). In it is an "Auto_Open" sub in which I assign initial
values to the variables. When the users finish filling in the form and
try to print or save it I run a "Check_Entries" sub which checks to
insure specific cells contain data. With some regularity a user will
receive the "Object variable or With block variable not set" error. It
occurs on the first line of code of the "Check_Entries" sub that
contains a variable reference. Further investigation indicates that
defined variables have been lost. This happens about once in every 50
executions of the .xls. It occurs on different machines, different
users and both XP SP2 and Win98SE OS's. I have not been able to
intentionally duplicate the error. Can anyone give me any ideas as to
what might be going on?

BrianG

--

Dave Peterson

Thanks for the hint to look for an errant "End" statement but no luck
finding one. Still no explanation as to what is causing the error.
When the error occurs I have gone into Debug mode and added Public and
Set statements to try to figure out what is going on. The error occurs
on every line of code where I check a cell for a value. Maybe the way
I'm doing that is problematic. Should this work:

Public objDate as Range
Public objShipTo as Range

Set objDate = Range("H15")
Set objShipTo = Range("H16")

Call CheckEntries

CheckEntries()
If objDate.Value = " " Then
display error message
Exit Sub
If objShipTo.Value = " " Then
display error message
Exit Sub
End Sub

--
BrianG

Dave Peterson wrote:
Do you have any "End" lines in your code--not "end if", "End with" "end
sub"--just plain old End's?

if a=b then
end
else
'do something
end if

End's will reset those variables.

I think I'd get rid of them somehow.

And you may want to create a dedicated subroutine that initializes those
variables.

Then you could use:

Public VariablesAreInitialized as boolean
sub initializeVariables()
'do all the initialization
variablesareinitialized = true
end sub

Then in each of your subroutines:

if variablesareinitialized = false then
call initializeVariables
end if

(assumes that you don't do anything to those variables once they're
initialized.)



wrote:

I have an excel purchase order form automated using macros. I have a
module named "Setup" in which I define all of my public variables (29
to be exact). In it is an "Auto_Open" sub in which I assign initial
values to the variables. When the users finish filling in the form and
try to print or save it I run a "Check_Entries" sub which checks to
insure specific cells contain data. With some regularity a user will
receive the "Object variable or With block variable not set" error. It
occurs on the first line of code of the "Check_Entries" sub that
contains a variable reference. Further investigation indicates that
defined variables have been lost. This happens about once in every 50
executions of the .xls. It occurs on different machines, different
users and both XP SP2 and Win98SE OS's. I have not been able to
intentionally duplicate the error. Can anyone give me any ideas as to
what might be going on?

BrianG

--

Dave Peterson

Is that code in the userform's module or somewhere else?

Do you hide that form or unload it?

Are those public declarations in a general module?

I think you'll have to provide a bit more info.




wrote:

Thanks for the hint to look for an errant "End" statement but no luck
finding one. Still no explanation as to what is causing the error.
When the error occurs I have gone into Debug mode and added Public and
Set statements to try to figure out what is going on. The error occurs
on every line of code where I check a cell for a value. Maybe the way
I'm doing that is problematic. Should this work:

Public objDate as Range
Public objShipTo as Range

Set objDate = Range("H15")
Set objShipTo = Range("H16")

Call CheckEntries

CheckEntries()
If objDate.Value = " " Then
display error message
Exit Sub
If objShipTo.Value = " " Then
display error message
Exit Sub
End Sub

--
BrianG

Dave Peterson wrote:
Do you have any "End" lines in your code--not "end if", "End with" "end
sub"--just plain old End's?

if a=b then
end
else
'do something
end if

End's will reset those variables.

I think I'd get rid of them somehow.

And you may want to create a dedicated subroutine that initializes those
variables.

Then you could use:

Public VariablesAreInitialized as boolean
sub initializeVariables()
'do all the initialization
variablesareinitialized = true
end sub

Then in each of your subroutines:

if variablesareinitialized = false then
call initializeVariables
end if

(assumes that you don't do anything to those variables once they're
initialized.)



wrote:

I have an excel purchase order form automated using macros. I have a
module named "Setup" in which I define all of my public variables (29
to be exact). In it is an "Auto_Open" sub in which I assign initial
values to the variables. When the users finish filling in the form and
try to print or save it I run a "Check_Entries" sub which checks to
insure specific cells contain data. With some regularity a user will
receive the "Object variable or With block variable not set" error. It
occurs on the first line of code of the "Check_Entries" sub that
contains a variable reference. Further investigation indicates that
defined variables have been lost. This happens about once in every 50
executions of the .xls. It occurs on different machines, different
users and both XP SP2 and Win98SE OS's. I have not been able to
intentionally duplicate the error. Can anyone give me any ideas as to
what might be going on?

BrianG

--

Dave Peterson

--

Dave Peterson