vba script hangs at Workbook.Close

Question

I am trying to write a hello world application in Visual Basic for Applications, namely, to modify a cell in an Excel sheet. Here it is:

Sub hello()

    Dim obj As Object
    Dim Workbook As Object

    Set obj = CreateObject("Excel.Application")
    Set Workbook = obj.Workbooks.Open("C:\Users\gbuday\Desktop\Oktatás\Excel\start.xlsx")

    Workbook.Worksheets("Munka1").Range("B3") = "Hello World!"

    Workbook.Close
    Set Workbook = Nothing
    Set obj = Nothing

End Sub

When running, Excel hangs and I cannot stop the script running, only kill the excel process. Debugging it, it hangs at the Workbook.Close line. What is the problem with that line?

Answer 1

The problem is that you are not giving Excel enough time to finish it's operations. Usually a DoEvents will solve the problem. Also to avoid confusion, you might want to name your variable as `wbk' instead of 'Workbook'

Sub hello()
    Dim obj As Object, wbk As Object

    Set obj = CreateObject("Excel.Application")
    Set wbk = obj.Workbooks.Open("C:\Users\gbuday\Desktop\Oktatás\Excel\start.xlsx")

    wbk.Worksheets("Munka1").Range("B3") = "Hello World!"

    DoEvents

    '~~> Change True to False if you do not want to save
    wbk.Close SaveChanges:=True

    Set wbk = Nothing: Set obj = Nothing
End Sub