Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import of text file - numbers destroyed! | Excel Discussion (Misc queries) | |||
Storing variables in a macro and using those variables to performcalculations. | Excel Programming | |||
Floating commandbar problem -- toolbars destroyed only if sheet first upon opening ... ? | Excel Programming | |||
Global variable destroyed when form closed | Excel Programming | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) |