Calling macro's from another workbook

[taz23340]

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

 

[RoryA]

No. The other workbook needs to be open. You can then use:

 Application.Run "'workbook name.xls'!macro_name"

but you can't use Call. (Call is never necessary actually)

 

[taz23340]

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]

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]

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]

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]

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]

Make macros 1-4 private (i.e. use Private Sub rather than just Sub).

 

[taz23340]

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]

They should all be in the same module. By making the others Private, they should not appear in the Macros dialog.