Variables being randomly destroyed
 

I have this code that I was assigned some time ago (I didn't write it
myself)... What is happening when I add watches for variables nm,
nmtrust is that I see they are being randomly assigned values like
'??????r', 'VBAProject' while I step thru the code. Any ideas why
that would happen?

It does not happen on my machine, but two other machines is is
happening. All machines are XP Professional SP2, office 2003 SP2. The
machines it is happening to are P4 2.8 Ghz, w/ 512 MB Ram and plenty
of HD Space avail.

Note: Option explicit is not turned on, and it would be quite a mess
to turn it on. The staff that wrote this code didn't do anything very
standard. No comments or docs when I took this code over.

Sub putRedSub()
On Error Resume Next
dater1 = Format(Range("A75").Value, "mm/dd/yyyy")
''If dater1 = Format(Now(), "mm/dd/yyyy") Then
'' GoSub getSubs
''Else
''End If

GoSub getSubs
y = 1
While wTrust.Offset(0, y).Value < ""
nmtrust = wTrust.Offset(0, y).Value
If IsNumeric(nmtrust) Then
nm = Trim(Str(nmtrust))

Else
unchar = InStr(1, nmtrust, "-", vbTextCompare)
If Not unchar = 5 Then
nm = Left$(nmtrust, 5) & "_" & Mid$(nmtrust, 7, 2) & "_" & Mid$
(nmtrust, 10, 1)
nm = Replace(nm, " ", "")
nm = Replace(nm, "-", "_")
' GoSub PutValue
Else
End If
End If

wSubscription.Offset(0, y).FormulaR1C1 = "=_DSTsource.xls!sb" & nm
If Left(Str(wSubscription.Offset(0, y).Value), 5) = "Error" Then
wSubscription.Offset(0, y).Value = Null
Else: End If

wSubscription.Offset(1, y).FormulaR1C1 = "=_DSTsource.xls!rd" & nm
If Left(Str(wSubscription.Offset(1, y).Value), 5) = "Error" Then
wSubscription.Offset(1, y).Value = Null
Else: End If

' ActiveCell.FormulaR1C1 = "=_DSTsource.xls!rd09303_02_L"
y = y + 1
Wend
With Range(wSubscription, wSubscription.Offset(1, y))
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Application.CutCopyMode = False
Exit Sub

getSubs:
Set regions1 = Range("b4").CurrentRegion
Set wSubscription = Range("a:a").Find(What:="Subscription",
After:=Range("a1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
wSubscription.Select

'While wSubscription.Offset(0, 1)

Set wTrust = Range("a:a").Find(What:="TRUST ACCT", After:=Range("a1"),
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
wTrust.Select
Located = Range(wSubscription, wTrust).Rows.Count - 1
Debug.Print Located
'Located.Select
Return
End Sub

Variables being randomly destroyed
 

Without having a copy of your spreadsheet is will be impossible to diagnose
this problem. The variables are not getting random values. One thing you
could try looking at is on the machines that are having the problem, open up
the VBE and look at Tools - References and look for missing references.

If that code was given to me to maintain I would do 1 of 2 things. Give it
back to the person who wrote it and tell them that I will not support code
that poorly written, or if that was not possible it would be a complete
re-write. That code is extremely prone to errors from which it will not
recover gracefully. It breaks most of the rule of good coding practices. In
short you have a real mess on your hands...
--
HTH...

Jim Thomlinson


"Bill Schanks" wrote:

I have this code that I was assigned some time ago (I didn't write it
myself)... What is happening when I add watches for variables nm,
nmtrust is that I see they are being randomly assigned values like
'??????r', 'VBAProject' while I step thru the code. Any ideas why
that would happen?

It does not happen on my machine, but two other machines is is
happening. All machines are XP Professional SP2, office 2003 SP2. The
machines it is happening to are P4 2.8 Ghz, w/ 512 MB Ram and plenty
of HD Space avail.

Note: Option explicit is not turned on, and it would be quite a mess
to turn it on. The staff that wrote this code didn't do anything very
standard. No comments or docs when I took this code over.

Sub putRedSub()
On Error Resume Next
dater1 = Format(Range("A75").Value, "mm/dd/yyyy")
''If dater1 = Format(Now(), "mm/dd/yyyy") Then
'' GoSub getSubs
''Else
''End If

GoSub getSubs
y = 1
While wTrust.Offset(0, y).Value < ""
nmtrust = wTrust.Offset(0, y).Value
If IsNumeric(nmtrust) Then
nm = Trim(Str(nmtrust))

Else
unchar = InStr(1, nmtrust, "-", vbTextCompare)
If Not unchar = 5 Then
nm = Left$(nmtrust, 5) & "_" & Mid$(nmtrust, 7, 2) & "_" & Mid$
(nmtrust, 10, 1)
nm = Replace(nm, " ", "")
nm = Replace(nm, "-", "_")
' GoSub PutValue
Else
End If
End If

wSubscription.Offset(0, y).FormulaR1C1 = "=_DSTsource.xls!sb" & nm
If Left(Str(wSubscription.Offset(0, y).Value), 5) = "Error" Then
wSubscription.Offset(0, y).Value = Null
Else: End If

wSubscription.Offset(1, y).FormulaR1C1 = "=_DSTsource.xls!rd" & nm
If Left(Str(wSubscription.Offset(1, y).Value), 5) = "Error" Then
wSubscription.Offset(1, y).Value = Null
Else: End If

' ActiveCell.FormulaR1C1 = "=_DSTsource.xls!rd09303_02_L"
y = y + 1
Wend
With Range(wSubscription, wSubscription.Offset(1, y))
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Application.CutCopyMode = False
Exit Sub

getSubs:
Set regions1 = Range("b4").CurrentRegion
Set wSubscription = Range("a:a").Find(What:="Subscription",
After:=Range("a1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
wSubscription.Select

'While wSubscription.Offset(0, 1)

Set wTrust = Range("a:a").Find(What:="TRUST ACCT", After:=Range("a1"),
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
wTrust.Select
Located = Range(wSubscription, wTrust).Rows.Count - 1
Debug.Print Located
'Located.Select
Return
End Sub

Variables being randomly destroyed
 

I don't have the luxury of giving this back, that person is no longer
with the company. As far as a re-write that has been on my agenda
since I took it over. It's a HUGE mess, and I dread it every time I
get a call about it.

But as far as this particular problem... there are no broken
references. What I did see there was VBAProject listed twice, but
neither of them were checked.

Variables being randomly destroyed
 

VBA project is the default name of the project attached to each worksheet.
Most likely you just have two spreadsheets open. Personal.xls is one possible
culpret.
--
HTH...

Jim Thomlinson


"Bill Schanks" wrote:

I don't have the luxury of giving this back, that person is no longer
with the company. As far as a re-write that has been on my agenda
since I took it over. It's a HUGE mess, and I dread it every time I
get a call about it.

But as far as this particular problem... there are no broken
references. What I did see there was VBAProject listed twice, but
neither of them were checked.