none
Reference A Range With A Variable RRS feed

  • Question

  • I am writing VBA and find that I am constantly typing in sheets(T).Range where T is my sheet name. I am trying to shorten that. The following code works to get rid of the "sheets(T)" but I still have to type "Range". Is there a way to avoid that?

    Sub Macro1()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim R As Range

    Set WB = Workbooks(ActiveWorkbook.Name)
    Set WS = WB.Worksheets("Test")

    ' This is what I have to type in
    WS.Range("A1").Value = "Hi"

    ' This is what I want to type in
    R("B1").Value = "BYE"
    End Sub

    Tuesday, June 7, 2016 9:09 PM

Answers

  • When the workbook is the active workbook and the sheet "Test" is the active sheet, then you can skip the references:

    Sub Test()
    Range("A1") = "Hi"
    Range("B1") = "Bye"
    End Sub

    If the sheet is not active, you can use a WITH statement:

    Sub Test()
      With Sheets("Test")
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    If the workbook that contains the code is not the active workbook, then you can refer to ThisWorkbook:

    Sub Test()
      With ThisWorkbook.Sheets("Test")
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    Only when you want to write into a different workbook you have to refer to all objects:
    Sub Test()
      With Workbooks("MyFile.xlsx").Sheets("Test")
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    Which is the same as:
    Sub Test()
      Dim Wb As Workbook
      Dim Ws As Worksheet
      Set Wb = Workbooks("MyFile.xlsx")
      Set Ws = Wb.Worksheets("Test")
      With Ws
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    It makes only sense to declare Wb and Ws when you need it a 2nd time later in your code.

    Andreas.

    Wednesday, June 8, 2016 7:16 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, June 8, 2016 6:15 AM
  • When the workbook is the active workbook and the sheet "Test" is the active sheet, then you can skip the references:

    Sub Test()
    Range("A1") = "Hi"
    Range("B1") = "Bye"
    End Sub

    If the sheet is not active, you can use a WITH statement:

    Sub Test()
      With Sheets("Test")
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    If the workbook that contains the code is not the active workbook, then you can refer to ThisWorkbook:

    Sub Test()
      With ThisWorkbook.Sheets("Test")
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    Only when you want to write into a different workbook you have to refer to all objects:
    Sub Test()
      With Workbooks("MyFile.xlsx").Sheets("Test")
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    Which is the same as:
    Sub Test()
      Dim Wb As Workbook
      Dim Ws As Worksheet
      Set Wb = Workbooks("MyFile.xlsx")
      Set Ws = Wb.Worksheets("Test")
      With Ws
        'Note the dot in front of Range!
        .Range("A1") = "Hi"
        .Range("B1") = "Bye"
      End With
    End Sub

    It makes only sense to declare Wb and Ws when you need it a 2nd time later in your code.

    Andreas.

    Wednesday, June 8, 2016 7:16 AM