Calling macro's from another workbook

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
Hi Everyone,

I have been combining multiple macro's into one large Macro and after research, it seems that only the call function works without any hitches.

Is there a way to use the call feature in one workbook while the macro's themselves, 5 or 6 of them, are in another workbook that is closed?

All users would have access to both workbooks.

The reason I am trying this is because I do not want all the other users to have to choose between multiple macro's and some of my colleagues like to use a radio button to link to the workbook. so it needs to have one file in it...

thanks for your help
kevin
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No. The other workbook needs to be open. You can then use:
Code:
 Application.Run "'workbook name.xls'!macro_name"
but you can't use Call. (Call is never necessary actually)
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
Hello Rorya,

Thanks for that.

Is there another way to combine 5 macro's into one and make then run as one continuous macro?

when i run then all individually in sequence, all is ok, but if i run them together as one by having removed the end sub, sub, i end up with a reference calculation on the macro file itself, very strange...

kev
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I don't really understand the question - macros cannot run simultaneously, so they would always be running in succession. I also don't know what you mean by a " reference calculation".
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336

ADVERTISEMENT

Hi

Sorry for not being clear.

I have written five individual macro's that I manually run in Succesion as you state, this is true. When i say Continuous, I mean the flow or appearance to the end user is that of "one continuous macro".

As for the "Reference calculation". this is a bit tricky to describe so I will give you the whole picture.

I have two workbook's open, the raw data file and the Macro that I created.
I run the macro once i am in the raw data sheet and in a1.
when the macro is finished running, the macro file has a reference error as if i had deleted a column that a formula was referencing.

I hope this makes more sense.

So my goal is to have one workbook with one macro in it, that runs everything I need to.
The reason I create 5 macro's is because we use to use only one large one, but as a few things changed, i created a few small ones to go before and after the large one instead of re-writing the whole macro each time one change occurs.

thanks for the help
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I guess we'd need to see the macros. There should be no difference between you running the macros in sequence yourself, and using one macro that simply calls the others in the same sequence.
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336

ADVERTISEMENT

macro 1

Columns("Y:Y").Cut
Range("Z1").End(xlToRight).Select
Columns("AN:AN").Insert Shift:=xlToRight
Range("A1").Select

macro 2

Rows("1:6").Insert Shift:=xlDown
......
Range("A8").Select
End Sub

macro 3

Selection.End(xlToRight).Select
Columns("AM:AM").Select
Range("AM8").Activate
Selection.Cut
Columns("Z:Z").Select
....
ActiveWindow.LargeScroll Down:=-1
Range("A8").Select
End Sub


macro 4

Range("B8").End(xlDown).End(xlDown).End(xlToRight).End(xlToRight).End(xlToRight).End(xlToRight).Select
Range("Y1999").Copy
Range("Z1999").Select
ActiveSheet.Paste
Range("A8").Select
End Sub

now when i use the following everything works

Sub EnglishMacroNovember2009()

' combination of all macro's

Call BeforeEnglishMacro
Call NewEnglishMacro
Call AfterEnglishMacro
Call TotalLineMacro

End Sub

but this doesnt help because people in my office dont want to see 4 macro's and they have to choose one.

they want only one in this workbook so when they hit alt + f8 they only see what they need and nothing else.

as previously mentionned, i have tried removing the sub and end sub and it doesnt seem to work...

thanks for the help
keep in mind i still feel as a knewbie in VBA even though im fairly competent in excel itself, but it is fun to learn this new part of excel

thanks again
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Make macros 1-4 private (i.e. use Private Sub rather than just Sub).
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
I have changed macro 1 to 4 to Private Sub
Now when i run the macro that calls the others i get errors.
Compile error sub or function not defined and the first call is highlighted

Sub EnglishMacroNovember2009()

' combination of all macro's

Call BeforeEnglishMacro
Call NewEnglishMacro
Call AfterEnglishMacro
Call TotalLineMacro

End Sub

do i need to change anything in this module?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
They should all be in the same module. By making the others Private, they should not appear in the Macros dialog.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,489
Messages
5,596,455
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top