Import a CodeModule then call its method in Workbook_Open()
 

I have a Module called "CodeFile.bas" which i Share amongst several
workbooks - It has a method called KickStart()

I want to do something like the following:

Private Sub Workbook_Open()

Me.VBProject.VBComponents.import "c:\CodeFile.bas"

Call KickStart()

End Sub


i.e. Import the code file and then subsequently call one of its
methods.

Any ideas how I could achieve this effect.

Dickster

Import a CodeModule then call its method in Workbook_Open()
 

The problem is that you get a compile error, so you need to get around that.
Luckily, if the call to KickStart is in another module, it overcomes the
compile error.

So, what you need to do is add a module that will always be in the workbook
(that is, not imported on open) with a procedure that calls KickStart (that
is all it will do). Assuming this procedure is called CallImportedMacro,
then your open code would be

Private Sub Workbook_Open()

Me.VBProject.VBComponents.import "c:\CodeFile.bas"

Call CallImportedMacro

End Sub


in the code module you would have

Public Sub CallImportedMacro()
Call kickStart
End Sub


An alternative way is to Application.Run it

Private Sub Workbook_Open()

Me.VBProject.VBComponents.Import "c:\CodeFile.bas"

Application.Run "KickStart"

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I have a Module called "CodeFile.bas" which i Share amongst several
workbooks - It has a method called KickStart()

I want to do something like the following:

Private Sub Workbook_Open()

Me.VBProject.VBComponents.import "c:\CodeFile.bas"

Call KickStart()

End Sub


i.e. Import the code file and then subsequently call one of its
methods.

Any ideas how I could achieve this effect.

Dickster

Import a CodeModule then call its method in Workbook_Open()
 

Works a treat Bob - Thanks

Dickster

Import a CodeModule then call its method in Workbook_Open()
 

Which option did you go with?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Works a treat Bob - Thanks

Dickster

Import a CodeModule then call its method in Workbook_Open()
 

Just curious...

Why not just keep that code in another workbook (maybe an addin) and run the
code directly from there?



wrote:

I have a Module called "CodeFile.bas" which i Share amongst several
workbooks - It has a method called KickStart()

I want to do something like the following:

Private Sub Workbook_Open()

Me.VBProject.VBComponents.import "c:\CodeFile.bas"

Call KickStart()

End Sub

i.e. Import the code file and then subsequently call one of its
methods.

Any ideas how I could achieve this effect.

Dickster

--

Dave Peterson

Import a CodeModule then call its method in Workbook_Open()
 

First of all thanks Bob for you answer which did address my original
problem.

Thanks Dave for guidance towards an "Add-In". This makes life way
easier

Im able to call the InitialiseConfig() method living in my Add-In from
the WorkBook_Open()

Im also able to reference the methods other in the Add-In in my VBA.
By setting a reference (Tools - References command )

The true benefit is that an update to the Add-In becomes available to
each Spreadsheet that references it the next time it is opened

i.e. change code in one place but used in many places (updates
automatically available)
just what i was looking for

:-)

Thanks Guys

A wee side question

I place a list of classes in my Add-In

eg.

AxaSpecific.cls
CMSpecific.cls
FPSpecific.cls
GESpecific.cls

Depending on which Workbook you open

i.e.

AXAimport.xls
CMimport.xls
FPimport.xls
GEimport.xls

I want to instantiate the appropriate object

currently i use something like

Select Case UCase(Left(ThisWorkBook.Name, 2))
Set providerSpecific = New AxaSpecific
Case "CM"
Set providerSpecific = New CMSpecific
Case "FP"
Set providerSpecific = New FPSpecific
Case "GE"
Set providerSpecific = New GESpecific
..
..
..
Case Else
End Select


Is there a slicker way as my case statement has many more options

Import a CodeModule then call its method in Workbook_Open()
 

That's the way I'd do it--but you're past what I know.

You may want to start a different thread if you don't get a reply in this one.

And remember to include the version of excel that'll be using this. Maybe
there's something that can be done in newer versions (Heck if I know, though).

wrote:

First of all thanks Bob for you answer which did address my original
problem.

Thanks Dave for guidance towards an "Add-In". This makes life way
easier

Im able to call the InitialiseConfig() method living in my Add-In from
the WorkBook_Open()

Im also able to reference the methods other in the Add-In in my VBA.
By setting a reference (Tools - References command )

The true benefit is that an update to the Add-In becomes available to
each Spreadsheet that references it the next time it is opened

i.e. change code in one place but used in many places (updates
automatically available)
just what i was looking for

:-)

Thanks Guys

A wee side question

I place a list of classes in my Add-In

eg.

AxaSpecific.cls
CMSpecific.cls
FPSpecific.cls
GESpecific.cls

Depending on which Workbook you open

i.e.

AXAimport.xls
CMimport.xls
FPimport.xls
GEimport.xls

I want to instantiate the appropriate object

currently i use something like

Select Case UCase(Left(ThisWorkBook.Name, 2))
Set providerSpecific = New AxaSpecific
Case "CM"
Set providerSpecific = New CMSpecific
Case "FP"
Set providerSpecific = New FPSpecific
Case "GE"
Set providerSpecific = New GESpecific
.
.
.
Case Else
End Select

Is there a slicker way as my case statement has many more options

--

Dave Peterson

Import a CodeModule then call its method in Workbook_Open()
 

That seems a slick enough way, but another way is to have a wrapper class
that is exposed to the workbook which handles all of the other classes. This
class would know about the other classes, the workbook code wouldn't, and
would route all the request to the class through the wrapper. Probably not
any less more (maybe even more), but it would clean up the workbook code,
and abstract the class code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
First of all thanks Bob for you answer which did address my original
problem.

Thanks Dave for guidance towards an "Add-In". This makes life way
easier

Im able to call the InitialiseConfig() method living in my Add-In from
the WorkBook_Open()

Im also able to reference the methods other in the Add-In in my VBA.
By setting a reference (Tools - References command )

The true benefit is that an update to the Add-In becomes available to
each Spreadsheet that references it the next time it is opened

i.e. change code in one place but used in many places (updates
automatically available)
just what i was looking for

:-)

Thanks Guys

A wee side question

I place a list of classes in my Add-In

eg.

AxaSpecific.cls
CMSpecific.cls
FPSpecific.cls
GESpecific.cls

Depending on which Workbook you open

i.e.

AXAimport.xls
CMimport.xls
FPimport.xls
GEimport.xls

I want to instantiate the appropriate object

currently i use something like

Select Case UCase(Left(ThisWorkBook.Name, 2))
Set providerSpecific = New AxaSpecific
Case "CM"
Set providerSpecific = New CMSpecific
Case "FP"
Set providerSpecific = New FPSpecific
Case "GE"
Set providerSpecific = New GESpecific
.
.
.
Case Else
End Select


Is there a slicker way as my case statement has many more options