You must avoid few statements while writing the VBA codes. so that your programs will be more accurate and reliable. Here are few things we should avoid to write better programs:

    Avoid direct use of Worksheet Names

    Constants
    Avoid using the worksheet names directly in your code. Instead, you can assign to a constant and use the constant whenever it’s required.

    For example, you are entering the data into Sheet2 from Sheet1 and performing some tasks. Your code will looks lie as shown below:


    Sheets(“Sheet2”).Range(“G5”)=Sheets(“Sheet1”).Range(“F2”)
    '
    '
    'Here your statements to perform some task
    '
    Sheets(“Sheet2”).Range(“G25”)=Sheets(“Sheet1”).Range(“F3”)
    '
    '
    'Here your statements to perform some task
    '
    Sheets(“Sheet2”).Range(“G30”)=Sheets(“Sheet1”).Range(“F5”)
    '
    '
    'Here your statements to perform some task

    So, you have written the code and completed the task. And delivered to your client.

    And the next day, your client asked you to change the worksheet names (Sheet1 as ‘Source’ and Sheet2 as ‘Destination’). So, again you have to change the sheet name all the places. You may do some mistakes while changing, if you have long code.

    So, you can declare the constants and use them in you codes as shown below:


    Const SH_Src = "Sheet1"
    Const SH_Des = "Sheet2"

    Sheets(SH_Des).Range("G5") = Sheets(SH_Src).Range("F2")
    '
    '
    'Here your statements to perform some task
    '
    Sheets(SH_Des).Range("G25") = Sheets(SH_Src).Range("F3")
    '
    '
    'Here your statements to perform some task
    '
    Sheets(SH_Des).Range("G30") = Sheets(SH_Src).Range("F5")
    '
    '
    'Here your statements to perform some task

    So, if you want to change the sheet names anytime, you can simply change the constants. And rest of your code will remain same:


    Const SH_Src = "Source" ‘Changed as per client request on:##/##/## (Change Date)
    Const SH_Des = "Destination" ‘Changed as per client request on:##/##/## (Change Date)

    Sheets(SH_Des).Range("G5") = Sheets(SH_Src).Range("F2")
    '
    '
    'Here your statements to perform some task
    '
    Sheets(SH_Des).Range("G25") = Sheets(SH_Src).Range("F3")
    '
    '
    'Here your statements to perform some task
    '
    Sheets(SH_Des).Range("G30") = Sheets(SH_Src).Range("F5")
    '
    '
    'Here your statements to perform some task