Excel become unresponsive after running macro using vbs

Question

From VBS, I am running a macro "Inc" in an already opened excel workbook. The macro is getting executed (this is confirmed after seeing the updated cell value after reopening the workbook), but the excel becoming soon after I run the code. Can you please let me know what I need to change in my code.

Macro code :

Sub Inc()

    Dim bs As Worksheet

    Set bs = Workbooks("Main.xlsm").Sheets("TimeStampWork")
    bs.Range("K11").Value = bs.Range("K11").Value + 1
    bs.Range("L11").Value = Now
    bs.Range("L11").NumberFormat = "dd-mm-yyyy, hh:mm:ss"

End Sub

VBS code :

Option Explicit
Dim xlApp, xlBook

Set xlBook = GetObject("E:\Main.xlsm")
Set xlApp = xlBook.Application
xlApp.Visible = True
xlApp.Run "Inc"
xlBook.Save

Set xlBook = Nothing
Set xlApp = Nothing

Update: Instead of the macro "Inc" I tried to run a macro with just a simple message box, and it works without any issues. I can't see any problems with excel.

acr acr · Accepted Answer · 2017-08-28T11:18:23

This issue has been resolved now. Changed my macro with screenupdate option which resolved the issue

Sub Inc()
Application.ScreenUpdating = False
Dim bs As Worksheet

Set bs = Workbooks("Main.xlsm").Sheets("TimeStampWork")
    bs.Range("K11").Value = bs.Range("K11").Value + 1
          bs.Range("L11").Value = Now
               bs.Range("L11").NumberFormat = "dd-mm-yyyy, hh:mm:ss"

Application.ScreenUpdating = True
End Sub

Excel become unresponsive after running macro using vbs

1 Answers