VBA Workbook index referencing failure

[lkrznchc]

I have a Module that has operated great for years, but now on a new computer, it is fails. The error message I receive is runtime error #9, which is subscript out of range.

I recognized the problem to be that it did not like the reference:

Workbooks(1).Worksheets("Corrected Data").Range("B8", "AY8") = Workbooks(1).Worksheets("Raw Data").Range("B9", "AY9").Value

The module works if I change the code to:

ThisWorkbook.Worksheets("Corrected Data").Range("B8", "AY8") = Workbooks(1).Worksheets("Raw Data").Range("B9", "AY9").Value

This is great, except for the program is used for data logging and I do not want it to start logging data on another book that I go look at while the code is looping and storing the data.

1) I am curious as to why the

Workbooks(1).Worksheets("Corrected Data").Range("B8", "AY8")

format is not being accepted as the first workbook open.

2) My plan is to change the code so that the active workbook when the module is run redefines the workbook to a specific name and uses the specific name throughout the module in all data logging loops. Does anyone see any problems with the strategy?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

The expression Workbooks(1) will reference the first workbook in the collection of open workbooks in the current application. On my system, Workbooks(1).Name returns "Personal.xlsb". That might explain the change in results that occurred with your new computer.

Knowing that, you could use the reference Workbooks(2) instead, but that's really throwing things to chance. If you opened another workbook earlier, the workbook you are intending to reference might actually be 3rd, 4th or 5th in the collection.

ThisWorkbook refers to the workbook that has the VBA code module making that reference. It's a very reliable way to reference a workbook without having to know its name in advance. It's not clear to me whether your former reference to Workbooks(1) was trying to reference the workbook with the VBA code.

The module works if I change the code to:

ThisWorkbook.Worksheets("Corrected Data").Range("B8", "AY8") = Workbooks(1).Worksheets("Raw Data").Range("B9", "AY9").Value

This is great, except for the program is used for data logging and I do not want it to start logging data on another book that I go look at while the code is looping and storing the data.

I don't follow what you're describing here. ThisWorkbook will continue to reference the workbook with the macro even if the ActiveWorkbook changes during execution of the code.

There's no need to rename the workbook to get your process to work, even if you can't use ThisWorkbook because your macro is not in the workbook you are trying to reference. Rather than rename the workbook, just store its Workbook.Name property as a variable. Then reference that Name throughout your code.

 

[lkrznchc]

Thank you for the info.

We were using Workbooks(1) to reference that workbook, so it seems much safer to use ThisWorkbook instead.

I was concerned about the definition of This and what that meant for the code to reference "This" workbook. I thought that may be similar to ActiveWorkbook where it may be variable depending on the Active window for example. It appears as long as the module is saved to the workbook project you wish to reference it is secure and will not change.

Thanks again!