Project Code Communication

[Walter Briscoe]

I have a Personal Macro Workbook in VBAProject(personal.xls).
It contains
Public Function Prelude() As Workbook
....
End Function

I want that function to be visible and hence callable from
VBAProject(stations.xls) which is also loaded
and starts
Option Explicit ' Force variable declaration
but
Prelude.Close SaveChanges:=False gets
Compile error: Variable not defined

Can Prelude be visible and hence callable from stations.xls?
If so, how?
A Declare statement will not work as Prelude is not in a DLL.

 

[Dave Peterson]

You have a couple of choices.

#1. You can use application.run

Dim myAddin As Workbook
Set myAddin = Workbooks("youraddinnamehere.xla")
Application.Run("'" & myAddin.Name & "'!prelude").Close savechanges:=False

Personally, I'd use an intermediate variable so that I could check to make sure
things worked ok:

Dim myAddin As Workbook
Dim PreludeWkbk As Workbook

Set myAddin = Workbooks("youraddinnamehere.xla")

Set PreludeWkbk = Application.Run("'" & myAddin.Name & "'!prelude")

If PreludeWkbk Is Nothing Then
MsgBox "It wasn't found"
Else
PreludeWkbk.Close savechanges:=False
End If

==============
#2. You can use a reference to that addin:

Inside the VBE
Activate the code that calls the procedure in the addin.
Tools|References
and check the addin's project

I'd rename that project to give it a unique name before I did this, though.

Dim myAddin As Workbook
Dim PreludeWkbk As Workbook

Set myAddin = Workbooks("youraddinnamehere.xla")

Set PreludeWkbk = Prelude

If PreludeWkbk Is Nothing Then
MsgBox "It wasn't found"
Else
PreludeWkbk.Close savechanges:=False
End If

Once you have that reference set, you can use all the functions/subs like
they're in your own project or built into excel!

 

[Walter Briscoe]

In message <[email protected]> of Sun, 29 Aug 2010
16:18:01 in microsoft.public.excel.programming, Dave Peterson

I was first confused by Dave's change from my use of a .xls to a .xla.
I decided to try choice #1 with personal.xls.
At first it worked well.
I then hit 1004 name not found for all names in personal.xls.
I had introduced a compilation error!

I infer that only procedures are visible across projects and that, if I
want to access data, I would need to write procedures to do so.

Thanks, Dave. I am very happy with the answer to my original question.