What is faster for extracting data, constant "fully qualifying your references" or activating different workbooks

[Mescoman]

Hey hey people.

I am working on an excel macro which will take data out of 21 different workbooks, various worksheets from each workbook (about 50 distinct data points and a couple of graphs) and paste them onto a word document using a word template as a reference. The version I have where I copy data from only one document takes about 11 seconds to run, and I am wary this time may increase at a higher rate than linearly (perhaps having multiple workbooks open) . I am wondering between two choices I have come up with for how to extract data from each workbook:

(1) Use an "active workbook" method

(2) "Fully qualify my references", as in, specify

workbooks("workbook_name").worksheet("worksheet_name".range("variable_name_or_cell_location")

each time

I have been warned against using "active workbook", but in this specific case I would only need to change which workbook is active 21 times, and then VBA wouldnt have to be told each time which workbook Im referring to. I understand that active workbook has its disadvantages, but in terms of pure speed is there a large difference in these methods? I would rather not have to build both methods and then time them as this would take a lot of time for someone new like me

:^)

 

[jasonb75]

and then VBA wouldnt have to be told each time which workbook Im referring to.

It doesn't have to be told each time if you set them to variables or use with blocks.

With workbooks("workbook_name")
    With .worksheets("worksheet_name")
        With .range("variable_name_or_cell_location")
            .Value = "some value"
            .Number
        End With
    End With
End With

 

[Mescoman]

It doesn't have to be told each time if you set them to variables or use with blocks.

With workbooks("workbook_name")
    With .worksheets("worksheet_name")
        With .range("variable_name_or_cell_location")
            .Value = "some value"
            .Number
        End With
    End With
End With

I didnt know you could use multiple "with" blocks like that, that makes total sense. Thank you Jason