Reference to other workbook problem

[Terry Carpenter]

Hello!,
I have two workbooks, wb1 and wb2, with macro code in them. wb2
has a hard reference to wb1 by selecting and setting a reference to
wb1 from the Tools menu in the IDE. This way, wb2 can access objects
such as a user-defined Public array of structs with code like this:

Project.UserDefinedType

Rather than hard coding the reference to wb1, I am trying to set it in
wb2 something like this:

Set WBref = Workbooks(wb1.Name)

This works but when I get to the code where it's supposed to access
the user-defined type, it doesn't see it. The code for this looks
like:

For i to Ubound(WBref.udt) to 0 Step -1

Where udt is supposed to be the user-defined Public array of structs.
The problem is WBref doesn't have any of the user-defined objects
available in the pull-down. Am I stuck with a hard reference to wb1?

Thanks!
Terry

 

[Dick Kusleika]

Terry

How is WBref dimmed? It appears that all your doing is creating another
Workbook variable that will be exactly the same as wb1. To add a reference
to another workbook, use code like this

Sub test()

Dim wb As Workbook

Set wb = Workbooks("book3.xls")

ThisWorkbook.VBProject.References.AddFromfile wb.FullName

End Sub

You can't use anything from book3.xls in the same module where this sub is
or you'll get a compile error - it won't know what those things are because
the reference hasn't been set. So this code will have to go in its own
module.