how to declare a public workbook?

[choi4u]

I'd like to declare a workbook and a worksheet before the first
procedure so that all other procedures can use them.

When I tryied to use Set Statement as "Set myBook =
Workbooks("test.xls")", I've got "invalid outside of a procedure"
error.

So, I guessed the Set statement may not work outside of a procedure
(I'm not sure). And then, I declared a string constant as "Public
Const myBook as "test.xls"" and used myBook in other procedure as
"Workbooks(myBook)", which worked fine.

However, I'd like to use something like myBook.mySheet.myRange.---,
insted of
Workbooks("myBook").Worksheets("mySheet").Range("myRange").---,

Any recommendation?

Thanks in advance.

 

[NateBuckley]

I'm quite positive you cannot set any type of object outside of a sub/function

Here is something that offers a few different ways of setting either
WorkBooks, WorkSheets or a Range

Public Sub test()

Dim aBook As Workbook
Dim aSheet As Worksheet
Dim aRange As Range


Set aBook = Workbooks("Book1")
Set aSheet = Workbooks("book1").Sheets("Sheet1")
Set aRange = Workbooks("Book1").Sheets("Sheet1").Range("A1:A10")
aBook.Sheets("Sheet1").Cells("1, 5") = "Funkah"
aSheet.Cells(1,3).Value = "Rar"
aRange.value = "Test"

End Sub

It's all pretty much straight forward, but makes it all that easier to
shrink down certain calls.

Hope this helps somewhat!

Nate

 

[Bob Phillips]

You can declare the workbook variable outside of the procedure, but you have
to load it from within a procedure.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

You can put the code in the Workbook_Open procedure in the ThisWorkbook code
module (it must be in ThisWorkbook, not some other module).

Private Sub Workbook_Open()
On Error Resume Next
Set myBook = Workbooks("Test.xls")
If Err.Number <> 0 Then
' Test.xls not found. do something
Else
' Test.xls found. do something.
End If
End Sub

Next, declare the myBook variable in a regular code module (not in
ThisWorkbook).

Public myBook As Excel.Workbook


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)