none
Which is the best approach to read/write data from multiple sheet using vba code? RRS feed

  • Question

  • Hi

    Which is the best approach to read/write data from multiple sheet using vba code? And I want to know the reason why Approach1/Approach2 is best?

    Approach 1

    Without activating particular sheet by using

    sheets(<sheetname>).Range("A1").value=XYZ

    Approach 2

    Activating particular sheet by using

     sheets(<sheetname>).Activate
    Range("A1").Value = XYZ


    Bala

    Monday, June 6, 2016 3:17 PM

Answers

  • Use approach I.

    Avoid activating/selecting the objects everytime you can.

    -I is faster then II

    -II could fire undesirable Worksheet_Activate/Deactivate events

    -II can flick the screen if you acess a lot of objets in different worksheets, giving a bad experience.

    ---

    I recommend using ThisWorkbook.Worksheets(<worksheet_name>).Range("A1").Value2

    ThisWorkbook will tell Excel to acess <worksheet_name> of the workbook that contains the code (more reliable). If you don't qualify with ThisWorkbook, Excel will consider the active workbook, and not always the active workbook is the one you want your code to run.

    ---

    Value2 is faster and doesn't mess cell formatting, on the contrary of Value.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Balaramji Tuesday, June 7, 2016 3:52 PM
    Monday, June 6, 2016 4:30 PM

All replies

  • Use approach I.

    Avoid activating/selecting the objects everytime you can.

    -I is faster then II

    -II could fire undesirable Worksheet_Activate/Deactivate events

    -II can flick the screen if you acess a lot of objets in different worksheets, giving a bad experience.

    ---

    I recommend using ThisWorkbook.Worksheets(<worksheet_name>).Range("A1").Value2

    ThisWorkbook will tell Excel to acess <worksheet_name> of the workbook that contains the code (more reliable). If you don't qualify with ThisWorkbook, Excel will consider the active workbook, and not always the active workbook is the one you want your code to run.

    ---

    Value2 is faster and doesn't mess cell formatting, on the contrary of Value.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Balaramji Tuesday, June 7, 2016 3:52 PM
    Monday, June 6, 2016 4:30 PM
  • Hi Felipe,

    Thank You Very Much !!!


    Bala

    Tuesday, June 7, 2016 3:53 PM