Reset VBA Code

[JerryD]

When I execute a macro, and if during the macro execution I press the "Esc" key, the execution stops and I get a Microsoft Visual Basic window with the message that code execution has been interrupted with buttons to Continue, End, Debug and Help. If I click the "End" button, the window closes and Excel is at rest - which seems to be some sort of Reset process.

I have a need to execute that process while Excel is at rest.
Is there any way to code the process in VBA?

TIA

JerryD

 

[JerryD]

OK. Alls well that ends well. Even though I don't understand why doing the VBA Reset worked as I was doing it, it did work. And the macro speed would return back to it's original speed, although only temporarily. But after talking with you guys, I did have to admit that something was probably wrong with my code. So I started experimenting.

This particular macro is approx 700 lines. I started using "go to's" and by-passing sections while observing the speed, to see if I could detect a difference. Nothing made a difference until I commented out the two lines you will see below. It seems that I was deleting my Query Table each time I imported a new file (and then adding a new Table). It still worked but apparently slowed down with each use. I have no doubt you guys would have picked up on that quickly. But I'm just stubborn enough to want to figure it out myself (with a little help of course). After all, half the fun is in the chase.

I probably would have left the macro like it was, had it not been for you guys. So thanks to Norie, PA HS Teacher, and VogII for uour help. I feel that the macro is at its best now (it takes about 5-6 seconds to cycle thru 43 text files)

The following is a brief description of the macro (just for ref) - some of it is hand written (not copied directly from the code - so there could be a typo). I'm sending it as text - being unfamiliar with how to send it as code. Once I commented out the Query Table delete line, the macro executes as quickly the nth time as the first time.

' cycle file names on sheet Files
x = 3 ' start in Col B Row 3
Path = Sheets("Files").Range("A2") ' set the Path
While Sheets("Files").Range("B" & x) <> ""

FName = Sheets("Files").Range("B" & x)
PathName = Path & FName

' (commenting out these two lines did the trick)
' On Error Resume Next
' Selection.QueryTable.Delete

Range("A1:A100")="" ' erase the data sheet
Range("A1").select
' import my data
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & PathName, Destination:=Range("A1"))
.Name = "MyData"
etc...
etc...
' do some error checking

' select each line of the data file
Do While Activecell <> "EOF"
c = 1 ' init pointer c

' cycle each line from begin to end
' if keyword found, extract data
While Mid(Activecell, c, 1) <> ""
' if keyword found, extract data (and do some other things)

c = c + 1 ' adv the pointer
Wend
if (certain things found) Exit Do
Activecell.Offset(1, 0).Select ' select the next line
Loop

' other loops and things

x = x + 1 ' adv pointer to next file
Wend


Thanks again

JerryD

 

[CycleTimeChart]

I accomplished a programmatic reset in the following way:

Sub ResetSub()
Rem This routine schedules the execution of MainSub _
four seconds later and then performs a reset
Application.OnTime Now() + 4 / 86400, "MainSub"
End
End Sub
Sub MainSub()
Rem Doing really important stuff here ...
...
End Sub

 

[jlbistc]

I also had a similar problem where events were still triggering even after clicking the "X" on my macro's userform, setting all the event variables to Nothing and using Unload Me on the userform. I pretty much deleted everything I could once it was done running, but still weird things were still happened after the macro ended for me too.

So I just used:

Workbooks(ThisWorkbook.Name).Save
Workbooks.Open (Workbooks(ThisWorkbook.Name).Path & "\" & ThisWorkbook.Name)

This reopened the workbook containing the macro and therefore "reset" everything.

There may be a better way to do this but I haven't heard of a better one yet and you're right that the code still stays stored in excel even after you exit.

Something you may want to do also is store the macro code in a completely different workbook than the one you do actual work in, and then save it as an "Excel Add-In (*.xlam)" workbook. This will automatically place the macor code workbook in your AddIns folder which will automatically make it available to you as a macro whenever you open any excel file. (as long as you enable the macro in your excel options of course after saving it for the first time, but you get the idea)

Hope this helps. Happy coding!