How to declare flexible variable for workbook?

Question

I would like to somehow declare dynamic workbook variables so that I can change workbook file names without causing errors. My original thought was to use Set Variable = ActiveWorkbook, but that appears to give some peculiar errors:

    Set WkBk1 = ActiveWorkbook

    MsgBox (WkBk1.Name)
    WkBk1.Worksheets(1).Select

The above code works fine by itself. Afterwards, in the same procedure, I open a new workbook and run the following:

    Set WkBk2 = ActiveWorkbook

    MsgBox (WkBk2.Name)
    WkBk2.Worksheets(1).Select
    WkBk1.Worksheets(1).Select

I get an error at the last line, "Run-time error 1004; select method of worksheet class failed". Can any one explain the source of this issue? I'm guessing that it has something to do with using ActiveWorkbook to declare variables, or a faulty method of going between workbooks.

I should mention that the opened file is a .dat file opened in excel.

Ultimately, I want to have code that will assign two worksheets, of whatever file name, to their own respective variables. I then want to copy and paste data between the two workbooks.

The actual code is below. The problem is with the copy statement in the for loop.
"Run-time error '438' object doesnt support this property or method". Ive used similar syntax before and can't figure out why it isn't working here.

Sub Import3()
    ' Imports TSS samples from backlog

    Dim TSS As Workbook, Backlog As Workbook
    Dim Sample As Range, SearchRange As Range
    Dim Count As Long

    Set TSS = ActiveWorkbook

    ChDir ("C:\lwuser6")
    Workbooks.OpenText Filename:="C:\lwuser6\BACKLOG.DAT", Origin:=437, _
           StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _
           1), Array(68, 1), Array(78, 1), Array(86, 1), Array(126, 1), Array(150, 1)), _
           TrailingMinusNumbers:=True
        ' for some reason _ has to be in between "Array(7, _1)"
            ' Can't be between list elements of encompassing list "Array"
    Set Backlog = ActiveWorkbook

    Set SearchRange = Backlog.Worksheets("BACKLOG").Range(Cells(1, 1), _
        Cells(Rows.End(xlDown).Row, 1))
    Count = 14

    For Each Sample In SearchRange
        Backlog.Range(Sample(1, 1), Sample(1, 3)).Copy
        TSS.Range("G" & Count).PasteSpecial (xlValues)
        Count = Count + 2
    Next

End Sub

It may not what you have been looking for, but [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) should solve your problem. — BigBen, Sep 26 '19 at 17:43
Thanks. As a new user, I've been getting further and further away from using Select, but I still use it sometimes for debugging. The above lines with select are actually not in my code, I just figured that they might demonstrate the root of the issue. That said, I'm curious if there is a better way other than .select or .activate to bring the user to a specific workbook/worksheet. I will definitely check out that link. — LabRat01010, Sep 26 '19 at 17:47
Note: instead of `Set WkBk2 = ActiveWorkbook`, use `Set WkBk2 = Workbooks.Open(yourpath)`.... — BigBen, Sep 26 '19 at 17:48
I think you have to select/activate the workbook and then the sheet. You can't select a sheet on a non-active workbook. — SJR, Sep 26 '19 at 17:57
@BigBen - Would I need to specify the file name for `Workbooks.Open(path)` ? That is what I am trying to avoid. @SJR I get an error when I attempt to activate or select the workbook by itself. — LabRat01010, Sep 26 '19 at 18:05
Actually I think you need `Activate` not `Select`. (But would re-iterate BigBen's point not to use either.) — SJR, Sep 26 '19 at 18:09
*I've been getting further and further away from using Select, but I still use it sometimes for debugging* - your go-to debugger tool and new best friend is the *immediate pane* (Ctrl+G) ;-) — Mathieu Guindon, Sep 26 '19 at 18:13

Mathieu Guindon · Answer 1 · 2019-09-26T19:04:30.390

I would warmly recommend running your code through Rubberduck inspections (it's free & open-source; I manage the project), since it explains not only the specific error you're asking about, but can also prevent you from stepping into a lot of common beginner traps.

The problem is that Backlog is a Workbook object, and Workbook doesn't have a Range member:

Member not found

A member access call is made against an extended interface that Rubberduck couldn't resolve, or the member couldn't be found. If VBA cannot resolve the type at run-time, error 438 will be raised. [...]

You need a Worksheet to access a Range, a Workbook won't work.

Note that the subsequent TSS.Range call is just as illegal ;-)

~~Take this:~~

Set Backlog = ActiveWorkbook

And get rid of the reliance on the side-effect of opening a workbook, by capturing the Workbook object returned by Workbooks.Open:

Set Backlog = Workbooks.OpenText(Filename:="C:\lwuser6\BACKLOG.DAT", Origin:=437, _
       StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _
       1), Array(68, 1), Array(78, 1), Array(86, 1), Array(126, 1), Array(150, 1)), _
       TrailingMinusNumbers:=True)

Nevermind, Workbooks.OpenText doesn't return a Workbook object reference.

Then take this:

 Backlog.Worksheets("BACKLOG")

And pull it into a local variable:

Dim backlogSheet As Worksheet
Set backlogSheet = Backlog.Worksheets("BACKLOG")

Now you can use it everywhere you need that particular sheet:

With backlogSheet
    Set SearchRange = .Range(.Cells(1, 1), .Cells(.Rows.End(xlDown).Row, 1))
End With

..and this should work, although IMO supplying Range objects to the Worksheet.Range property feels somewhat wrong (and will raise error 1004 if the Sample range isn't on the backlogSheet):

backlogSheet.Range(Sample(1, 1), Sample(1, 3)).Copy

Not sure which specific sheet in the TSS workbook you mean to paste things, but the same principle applies. That said, you probably don't even need to get the clipboard involved:

tssSheet.Range("G" & count & ":I" & count).Value = _
    backlogSheet.Range(Sample(1, 1), Sample(1, 3)).Value

Doh!!!! I should have known better than to try and run a range without a worksheet. I think it will help to be consistent with designating variables to worksheets instead of workbooks, as you have shown. For some reason, I get a compile error "expected function or variable" when just running the variable declarations and `Set Backlog = Workbooks.opentext` line. Regardless, its all really good stuff, and I'm excited to integrate it. I will check out the Rubberduck resource as well. Thanks! — LabRat01010, Sep 26 '19 at 18:58

How to declare flexible variable for workbook?

1 Answers1