0

As part of a larger script, I'm trying to open an Excel workbook to update a range of cell values, then save and close Excel. The problem I am having is that if I use the Cells(row, col) function, an invisible instance of Excel remains open.

Interestingly, if I use the Range() function then Excel closes as expected.

I have done a fair amount of googling but nothing has fixed the issue - and I can't find anyone else who has had this specific issue with the Cells() function!

Sub testExcel()
    Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.worksheet
    Dim xRange As Excel.Range
    
    Dim filePath As String
    
    filePath = CurDir() & "\testExcel.xlsx"

    Set xExcelApp = CreateObject("Excel.Application")
    Set xWb = xExcelApp.Workbooks.Open(filePath)
    Set xWs = xWb.Sheets(1)
    xWs.Activate
    
    'Using Cells() prevents closing of excel
    Set xRange = Cells(5, 5)
    
    'If i were to use the Range instead, there is no problem:
    'Set xRange = xWs.Range("E5")
    
    xRange.Value = "Test"
    
    xWb.Save
    xWb.Close
    xExcelApp.Quit
End Sub

I would like to know why this is happening or how I can fix it.

Alternatively, because using Range() doesn't cause the same issue, is there a way to input row and column values into Range() using variables like the snippet below?

Dim row, col As Integer
row = 5
col = 5
xRange = Range(row,col)
user1357607
  • 214
  • 4
  • 13

1 Answers1

0

I have found a solution to the issue but I have absolutely no idea why it works. It would be really good if someone could explain why?

The problem disappears if I put the worksheet before Cells (or Range):

'Excel won't close:
Cells(row,col).Value = "test"
Range("A1").Value = "test"
'Excel will close:
xWs.Cells(row,col).Value = "test"
xWs.Range("A1").Value = "Test"
user1357607
  • 214
  • 4
  • 13
  • I recommend that you pay attention to [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Алексей Р Aug 18 '22 at 06:55
  • Thanks! I had no idea "Select" was a thing. Very interesting and this will help my code. – user1357607 Aug 21 '22 at 22:26