none
Differences in VBA between Excel 2016 Home & Business 2016 and Excel 365 Business RRS feed

  • Question

  • I have a built an administrative system with Excel VBA and have used it during years through Excel 2010, Excel 2013 and Excel 2016. The program works perfectly in these environments! We are now switching to Office 365 Business and after setting up the environment in exactly the same manner as earlier, the VBA routines doesn't work properly any more!

    Spite all references and named ranges are the same, it's not possible to activate another workbook via VBA, even a worksheet  within the same workbook cannot be activated with Workbooks("aaa").Worksheets("bbb").Activate. I have to use the entire file name Workbooks("aaa.xlsm")...........otherwise I receive error 9 (subscript out of range)

    Furthermore it's not possible at all to select a named range in another worksheet within the same workbook, f.i. Range("AllRegFields").Select triggers Error 1004 (Select method of Range class failed).

    What have happened?

    Wednesday, October 5, 2016 10:08 AM

Answers

  • Spite all references and named ranges are the same, it's not possible to activate another workbook via VBA, even a worksheet  within the same workbook cannot be activated with Workbooks("aaa").Worksheets("bbb").Activate. I have to use the entire file name Workbooks("aaa.xlsm")...........otherwise I receive error 9 (subscript out of range)

    Furthermore it's not possible at all to select a named range in another worksheet within the same workbook, f.i. Range("AllRegFields").Select triggers Error 1004 (Select method of Range class failed).

    What have happened?

    Nothing, what you described is the normal behavior of Excel.

    Make a new file in XL2010 (or 2013 or 2016), open the VBA editor, add a regular modul and paste in this code:

    Sub Test()
      Dim Wb As Workbook
      'Works:
      Set Wb = Workbooks("aaa.xlsm")
      'Fails:
      Set Wb = Workbooks("aaa")
    End Sub
    

    Save the file as aaa.xlsm and debug the code. As you see it was never possible to refer to an existing file without the extension.

    And the RANGE.SELECT method always fails if the parent Worksheet of the RANGE object is not active.
    Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

    Andreas.

    Wednesday, October 5, 2016 10:26 AM