Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VBA - How to call module procedure from Workbook Object after instanciated

Posted on 2013-06-10
5
736 Views
Last Modified: 2013-06-12
Hi,
Is there a way to call and function module from the workbook object after instanciated based on the below scenerio...

The Book1.xlsm contain the macro that open up the Book2.xlsm and is set to workbook object.

e.g
Book1.xlsm
Module1

sub openfile()
      Dim wb as Workbooks.
      Set wb= Workbooks.Open("C:\test\Book2.xlsm",  ReadOnly:=True)
       wb.module1.test    '<-- how to call the procedure in his own module
       wb.module1.test1(wb) <-- how to call the procedure in his own module
End sub

*************

in Book2.xlsm
Module1

sub test1()
        msgbox ("Hi")

end sub

sub test2( wb as workbooks)
        bla bla bla...
       
end sub

Thank you.

best regards
YK
0
Comment
Question by:meniyk
5 Comments
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 50 total points
ID: 39236701
You could use this kind of code:-
Application.Run "'My Workbook.xlsm'!ThisModule.ThisProcedure"

Open in new window

The procedure you call must be declared as Public (default).
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 150 total points
ID: 39237015
First Part Book1.xlsm
=================
If the procedure is test like
Sub Test
...
End Sub

then you can call it simply by naming it
test
or
Call test

if test has a parameter wb then also you call it
test wb
or Call test wb

Second part book2.xlsm
====================
you have test1 and test2 subs not clear on how you want to call them from where
if you are in book2 like it is the active workbook then same as before simply name them
if you are calling them from book1 then
they have to public sub like

Public sub test1()
        msgbox ("Hi")

end sub

Public sub test2( wb as workbooks)
        bla bla bla...
       
end sub

now this is a bit delicate and need following code

Dim Wbmacro As String
Set WB = Workbooks.Open("Book2.xlsm")

Wbmacro = "'" & WB.FullName & "'!" & "Module1.test1"
Application.Run (Wbmacro)

Open in new window


Note the single quote that surround the fullpathname of the book2
Note also the modulename containing test1

Now if you want to call test2 passing it WB which is WB1 then you do this

Dim WB1 as workbook
Dim WB2 as workbook
Dim Wbmacro As String

Set WB1 = Activeworkbook
Set WB2 = Workbooks.Open("Book2.xlsm")

Wbmacro = "'" & WB2.FullName & "'!" & "Module1.test2"
Application.Run (Wbmacro,WB1)

Open in new window


Note we are passing WB1 as an argument in the application Run

Try it and let me know. One last thing when you open Book2.xlsm I assume that it is in the same directory as book1 reason why I only put its name if it is not you need to supply the fullpath to book2 or else you will get File not found.
gowflow
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 300 total points
ID: 39237196
Public properties subs and functions that are defined in the ThisWorkbook module show up as properties and methods of the object, so if you have in ThisWorkbook of C:\test\Book2.xlsm code like this

Public Property Get WbkVersion() As String
    WbkVersion = "2.32"
End Property

Public Sub HelloWorld()
    MsgBox "Hi"
End Sub

Open in new window

You can then in your code say something like:
Sub openfile()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\test\Book2.xlsm", ReadOnly:=True)
       
    MsgBox wb.WbkVersion
    
    Call wb.HelloWorld
       
End Sub

Open in new window

This is late bound, so you won't get intellisense for those properties and methods, but they are available at run time. You could of course get these routines to call into other code modules in your Book2.xlsm.
0
 

Author Comment

by:meniyk
ID: 39240558
Thank you Faustulus, gowflow and andrewssd3.
Greatly appreciate you response and contribution.
That's amazing, Andrewssd3 manage to reslove my the problem and very well explain the concept. Once again Thank you Andrewssd3. Point has been mostly rewarded to Andrewssd3.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39241230
@andrewssd3
Great input. We never cease to learn it opens up new horizons. Tks again for your valuable input.

gowflow
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question