Hello everybody. I'm new here, sorry for rushing out so fast to post this, but it's an emergency. Also, this might as well be the most technical Excel VBA question ever.
In my project I have a form that lets the user write their own VBA code and run it via Module Insertion. It used to work fine (you open the form, type some VBA code, press run, and voilá) until today.
I had to make a small change in the project, I just edited a little piece on ABSOLUTELY-UNRELATED code on another form, and suddenly, when I was testing it with my VBA scripter form, when I hit Run, the whole execution of VBA stopped. Everything but Excel closed, and now, I can't keep it from automatically shutting down the execution when the event of the button finishes. This has become really anoying, since I was already creating my own separate debugging files to load on it.
Here is the source code for the VBA Scripter window if you need it.
Private Sub BtnCancel_Click()
BtnClear_Click
Unload Me
End Sub
Private Sub BtnClear_Click()
TxtSc.Text = Empty
End Sub
Private Sub BtnLoad_Click()
On Error Resume Next
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show <> 0 Then
Dim x As Integer, y As String
x = FreeFile
Open .SelectedItems(1) For Input As x
Do Until EOF(x)
Line Input #x, y
TxtSc.Text = TxtSc.Text & y & vbCrLf
Loop
Close x
End If
End With
End Sub
Private Sub BtnRun_Click()
On Error GoTo Fail
Dim vbcomp As vbide.VBComponent
Dim LineNum As Long, f As String
Set vbcomp = Application.VBE.VBProjects("PSystm").VBComponents.Add(vbext_ct_StdModule)
vbcomp.Name = "QSTEMP"
With vbcomp.CodeModule
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Public Function Result() As String" & Chr(13) & _
"On Error Goto QSFail" & Chr(13) & _
TxtSc.Text & Chr(13) & _
"ExitFunction:" & Chr(13) & _
"Exit Function" & Chr(13) & _
"QSFail:" & Chr(13) & _
"Result=" & Chr(34) & "Error " & Chr(34) & " & Err.Number & " _
& Chr(34) & " executing script." & Chr(34) & Chr(13) & _
"Resume ExitFunction" & Chr(13) & _
"End Function"
End With
f = CStr(Application.Run("QSTEMP.Result"))
If f <> Empty Then MsgBox f, vbInformation, Me.Caption
Application.VBE.VBProjects("PSystm").VBComponents.Remove vbcomp
Exit Sub
Fail:
Msgbox Err.Description, vbCritical, Err.Number
End Sub
Note: The code on the script window does execute, but everything stops right away when the BtnRun_Click event finishes.
Bookmarks