CODEWITHSUNDEEP
excelvba
dandan
dandan

Reputation: 509

Excel Macro hangs for a long time but never hangs during debugging

I have a macro which iterates through some sheets and exports data to file: I run it with a dedicated command button explained below. When I use the dedicated button to run it is always hangs for minutes at times, showing ~%40 CPU usage. When I run it in the debugger, I place a BP at the end, just before closing the file here:

Close #1

It never hangs when I run it the debugger and completes in a few ms. How can I debug it and see what causes it to hang?

Thanks

I run it using a dedicated command button which has this command:

=EMBED("Forms.CommandButton.1","")

which calls this:

Private Sub CommandButton1_Click()
    Call Module1.ExportCommandsToFile
    'Call ExportCommandsToCmdFile
End Sub

and finally ExportCommandsToCmdFile is this:

Sub ExportCommandsToFile()

Dim FName As String
Dim Sep As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim SheetNamesToExport As Variant
Dim ThisSheet As String

ThisSheet = Application.ActiveSheet.Name

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

'Sheets("ExportFor VA").Select
'Sheet3.ExportToTextFile
'Sheets("Keystrokes Editor").Select
'showFile = Shell(Environ("windir") & "\notepad.exe " & kFile, 1)    'opens notepad
'MsgBox ("Export was successful." & vbCrLf & "Generating 2 files:" & vbCrLf & kFile & vbCrLf & ThisWorkbook.Sheets("ExportFor VA").Range("D3"))
SheetNamesToExport = Array("Radio Comm", "Sim Comm", "Icp Comm")
Sheets("Icp Comm").Select
FName = ActiveSheet.Range("D3").Value

Sep = ""



    

' change the date
ActiveSheet.Range("B4").Value = Now()

Open FName For Output Access Write As #1

For i = 0 To UBound(SheetNamesToExport)
    Sheets(SheetNamesToExport(i)).Select

    'With ActiveSheet.UsedRange
    StartRow = 10
    StartCol = ActiveSheet.Range("A4").Value
    'StartCol = 11
    EndRow = 400
    EndCol = StartCol
    'End With

    For RowNdx = StartRow To EndRow
    'For RowNdx = 6 To EndRow
        WholeLine = ""
        'For ColNdx = StartCol To EndCol
            ColNdx = StartCol
            If Cells(RowNdx, ColNdx).Value = "" Then
                ' CellValue = Chr(34) & Chr(34)
                CellValue = ""
            Else
                CellValue = Cells(RowNdx, ColNdx).Value
            End If
            WholeLine = WholeLine & CellValue & Sep
        'Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
        If WholeLine <> "" Then
            Print #1, WholeLine
        End If
    Next RowNdx
Next i
'PLACING BP BELOW THIS
Close #1
MsgBox ("Cammands saved." & FName)

Upvotes: 0

Views: 175

Answers (3)

dandan
dandan

Reputation: 509

I changed the last bit to this after reading your suggestions:

.
.
.
Close #1

'MsgBox "Cammands saved to file"

'EndMacro:
'On Error GoTo 0
Application.ScreenUpdating = True


Sheets(ThisSheet).Select

MsgBox "Cammands saved to file: " & FName

That solved it!

Upvotes: 1

Gokhan Aycan
Gokhan Aycan

Reputation: 124

Not enough reputation to comment.

Not sure if this may be related but, up in the code you have

Application.ScreenUpdating = False

and are not switching it back to true. Maybe switch it back on before msgbox?

Upvotes: 1

HackSlash
HackSlash

Reputation: 5811

Try adding a DoEvents between the Close and the message:

Close #1
DoEvents
MsgBox "Commands saved to: " & FName, vbInformation, "Complete"

I also removed the unnecessary parenthesis around your msgbox details.

NOTE: DoEvents is a workaround that is often frowned on because it can cause unexpected things to happen. It allows other events and code to run before it continues.

Upvotes: 0

Related Questions