Calling on a macro within a macro!

Hey All!!

Quick Question - How do you call up a macro within a current macro? I
have one big macro and I need it to jump to several other macros when
it runs! Does it matter where the other macros are stored or is it
better to have them all in one module?

Help appreciated Thnx!

0
1/31/2006 11:49:04 AM
excel 39880 articles. 2 followers. Follow

13 Replies
1147 Views

Similar Articles

[PageSpeed] 36

Hi Mellowe,

'=============>>
Public Sub Main()
    'Your code
    Call One
    'your code
End Sub
'<<=============

'=============>>
Public Sub One()
    MsgBox "Hi from One"
End Sub
'<<=============

> Does it matter where the other macros are stored or is it
> better to have them all in one module?

Unless there are a  larger number of macros, they can be stored in a single 
module. However, for organisational reasons, it is usually better to store 
macros by type in different modules.

---
Regards,
Norman



"mellowe" <melanie_lowe1@hotmail.com> wrote in message 
news:1138708144.588765.33730@g43g2000cwa.googlegroups.com...
> Hey All!!
>
> Quick Question - How do you call up a macro within a current macro? I
> have one big macro and I need it to jump to several other macros when
> it runs! Does it matter where the other macros are stored or is it
> better to have them all in one module?
>
> Help appreciated Thnx!
> 


0
normanjones (1047)
1/31/2006 12:02:09 PM
Thanks for the prompt reply Norman!!
Sorry but I dont understand where does this piece of code goes ?
I basically want start a macro that calls up in turn 5 other macros one
after the other - I have stored these other five macros in 5 seperate
modules under one Excel Sheet.
Just not sure how to pull it all together! thnx

0
1/31/2006 12:41:19 PM
I use  the following  command line to call "outside" macros from within my 
main macro.....

    Application.Run ("YourFirstOutsideMacroName")
    Application.Run ("YourSecondOutsideMacroName")
    Application.Run ("YourThirdOutsideMacroName")
    Application.Run ("YourFourthOutsideMacroName")
    Application.Run ("YourFifthOutsideMacroName")

These lines can be slpit up and placed at various places within your Main 
Macro at your desire.

Vaya con Dios,
Chuck, CABGx3



"mellowe" wrote:

> Thanks for the prompt reply Norman!!
> Sorry but I dont understand where does this piece of code goes ?
> I basically want start a macro that calls up in turn 5 other macros one
> after the other - I have stored these other five macros in 5 seperate
> modules under one Excel Sheet.
> Just not sure how to pull it all together! thnx
> 
> 
0
CLR (807)
1/31/2006 12:57:28 PM
Sounds good! Will give it a go thnx!

0
1/31/2006 1:30:38 PM
Sorry just a quick question on this - does it matter where the other 5
macros are stored? Is it ok for them to be in different modules under
the same Excel sheet? thnx?

0
1/31/2006 2:14:49 PM
It should not matter which module the called macros are stored in......I 
usually place them for my convienience of remembering where they are for 
future editing.

Vaya con Dios,
Chuck, CABGx3



"mellowe" wrote:

> Sorry just a quick question on this - does it matter where the other 5
> macros are stored? Is it ok for them to be in different modules under
> the same Excel sheet? thnx?
> 
> 
0
CLR (807)
1/31/2006 2:27:29 PM
If the macros are in the same workbook, then you don't need application.run.

You can just use:

	call Macro1
        call macro2
        call macro3

And it sounds like these types of macros should not be in any worksheet
module--or behind the ThisWorkbook module.

They should be in just plain old General modules.  The kind you get when you do
Insert|Module.

===
Application.run is very useful if you don't know the name of the macro that you
need to call (it'll be determined by the code that's running).  Or if the macro
resides in another workbook.

mellowe wrote:
> 
> Sorry just a quick question on this - does it matter where the other 5
> macros are stored? Is it ok for them to be in different modules under
> the same Excel sheet? thnx?

-- 

Dave Peterson
0
petersod (12004)
1/31/2006 2:28:07 PM
Thanks everyone for your help here!!...

This is the situation: I have one worksheet that has a button to run my
main macro - this macro opens up several other workbooks to initially
complie a dataset.

Once the dataset is completed I then need my main macro to run the
additional 5 macros in order - they use the completed dataset (they
also open up and change other workbooks' data) . All of these macros
are in general plain modules, as you said Dave from: Insert|Module, and
they are all behind one worksheet.

So should I use Application. Run and name the macros or is the Call
'Macro' option better?. Thnx again!

0
1/31/2006 2:47:42 PM
Why do you use "Call" before the name of the macro ?

I just write the name af the macro, and then it jumps to that macro.

0
1/31/2006 2:56:00 PM
It's not required.  I just find it nicer (personal preference only).



SpookiePower wrote:
> 
> Why do you use "Call" before the name of the macro ?
> 
> I just write the name af the macro, and then it jumps to that macro.

-- 

Dave Peterson
0
petersod (12004)
1/31/2006 3:10:05 PM
First, behind a worksheet means (to me) that they are behind one of the Sheet
modules--not in a general module.  If you really put them in a General module
(Insert|module), then it's just a wording issue.

It sure seems to me that there would be less things for excel to do if I Call a
routine--rather than using application.run.



mellowe wrote:
> 
> Thanks everyone for your help here!!...
> 
> This is the situation: I have one worksheet that has a button to run my
> main macro - this macro opens up several other workbooks to initially
> complie a dataset.
> 
> Once the dataset is completed I then need my main macro to run the
> additional 5 macros in order - they use the completed dataset (they
> also open up and change other workbooks' data) . All of these macros
> are in general plain modules, as you said Dave from: Insert|Module, and
> they are all behind one worksheet.
> 
> So should I use Application. Run and name the macros or is the Call
> 'Macro' option better?. Thnx again!

-- 

Dave Peterson
0
petersod (12004)
1/31/2006 3:12:13 PM
A few thoughts:

If I'm writing a routine that will use the same variables through several
different Subs, I will usually put all the Subs in the same module so I can
declare all the variables at the top.  That way I don't accidentally re-Dim
a variable name in another Sub and destroy it for the rest of the Subs.

Remember that when you call a Sub from within another one, you have NOT left
the first one!  When the second Sub is finished, you will drop back into the
first Sub just below the Call line.  I got myself very tangled more than a
few times by jumping from one to another, forgetting that at some point I
had to return and finish everything out.

If you're opening and closing a lot of files, you may want to monitor your
objects in the Locals window, especially just before you think you're done,
to make sure you haven't left some "ghost" of a file hanging in memory.
This has got me a couple of times, too!  If you want, put "Stop" on a single
line just before your final End Sub - it will break the code and give you a
chance to open the Locals window to check things.

HTH
Ed

"mellowe" <melanie_lowe1@hotmail.com> wrote in message
news:1138716889.309089.187890@f14g2000cwb.googlegroups.com...
> Sorry just a quick question on this - does it matter where the other 5
> macros are stored? Is it ok for them to be in different modules under
> the same Excel sheet? thnx?
>


0
ed_millis (164)
1/31/2006 9:41:05 PM
Thanks again everyone!!!
Used the Call functionality and works perfect - using Application.run
actually slowed down the macros!

0
2/1/2006 5:00:16 PM
Reply:

Similar Artilces:

VBA Macro help
Hi Friends, I have taken a project using Excel. Now wt i need is that if i click a command button in excel the macro coding must open a excel file from one location and do a some formating (bold the header, sorting by client / stcd / TOS etc in ascending / decending delete columns last action, pat type etc change PN Assign column to Currency insert SIF cloumn before TYPE SRVS .and page formating as landscap, header (XXXXXXXX), page number at the center bottom and save the file name with password (12345) with the filename continuing the current date for example "N0123(curren...

Covered Call Options
Hello all, I do I go about dealing with a covered call transaction in Microsoft Money 2007 Premium? Currently what I am doing is inputing the option symbol, and choosing the transaction "Sell to Open" then when I go to expire the option I try to do a Sell to Close. However it does not seem to work? Also what happens if instead of expired I has the position assigned? how would I account for that? Thank you. Shhhh In microsoft.public.money, Shhhh wrote: > >I do I go about dealing with a covered call transaction in Microsoft >Money 2007 Premium? Currently what I am doing is...

macros shortcut keys don't work
I have several macros they will run from tools, macro, run but not from the short cut keys. I look in the macro editor and the keyboard short cut is listed. when I do the shortcut I just get the exclamation sound. -- dm ...

Macro compatibility in xls2003
I'm having trouble exporting data from a source program (SAP) to excel. It worked fine with the '97 version, but since we upgraded to '03, nothing happens. I'm not too experienced with scripts, but I'm told that the macro in the SAP program just might not work properly with xls2003. I can, however, export the data to word2003 and then to excel, but it's a frustrating process. Does anyone have experience with macros incompatability in excel 2003 / suggestions that may narrow down this issue? -- ____________________ Peter Andersen account_name@hotmail.com _______...

Call in?
Do new owners of Office X have to call up Microsoft to register their copy? If yes, what's the number? thanks. Ming <asdf@asdf.com> wrote: > Do new owners of Office X have to call up Microsoft to register their > copy? If yes, what's the number? thanks. Nope. We're not using Windoze :->>> This version does not require activation. Corentin -- - Mac:MS MVP (Francophone) - (MS) MVP: http://support.microsoft.com/default.aspx?ln=FR&scid=fh;FR;mvp Newsgroups produits MS: http://support.microsoft.com/newsgroups/?ln=FR ...

Call center tracking and reporting
Anyone use CRM 4.0 for call tracking? Our Sales/Marketing is set up. Now I need to decide how to most efficiently customize CRM 4.0 for our company's call tracking portion. Can someone send me a link to information? thanks ...

Save Macro with date in name
Hi, I have a macro that I want to complete by saveing the file with the current date ate the end of the file name. I have this code but I get an error with the "DateAdd". ActiveWorkbook.SaveAs Filename:="Monthly Accrual Upload" & DateAdd ("MMMM") & " - " & DateAdd ("yyyymmdd") & ".xls", _ FileFormat:=xlText, CreateBackup:=False How can I get the file name for April to be; Monthly Accrual Upload - APRIL - 20101604 Thanks Rick Use Format() as below Msgbox "Monthly Accrual Upl...

Macro to insert blank lines
I wonder if anyone already has a macro for the following operation I perform quite often please? I insert blank lines in a long table whenever a particular column changes. A typical example is in a list containing a Year column, sorted by Year. It improves readability to add a blank line between the 1972 and 1973 sections, 1973 and 1974...etc. -- Terry, West Sussex, UK try Sub InsertRow() For i = cells(rows.count,"a").End(xlUp).Row To 2 Step -1 If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then Rows(i).Insert End If Next End Sub -- Don Gui...

Starting a "command line" .exe program from within an excel spreadsheet
Is there a way to link an external .exe file to a button on a worksheet. I have a fortran program, which carries out a set of analyses, that I would like to be able to call from a worksheet. Many thanks Richard Richard, Attach a macro to the button, along the lines of Sub RunDOSProgram() Dim myProgram As String myProgram = "C:\Program Files\BlahBlah.EXE -s" TaskID = Shell(myProgram, 1) End Sub I don't have any old DOS execeutables to test, so..... HTH, Bernie MS Excel MVP "kilter" <richard.pitman3@btopenworld.com> wrote in message news:1180955654.898401...

Recording and Deleting a Macro
I am new excel 2003 and I'm learning at my own pace. I have recorded several macros. As a new excel user I am having not having much success opening up and deleting some of the macros that I have created and in need of some step by step instruction. Might anybody have any instruction in this situation in deleting and opening up macros. Check out http://www.nwarwick.co.uk/Excel.html, There's is a short 3 step tutorial on Excel macros that will show you how to open and delete, and even do simple edits to your macros, if you have any more questions then post back or contact m...

Excel to Word macro
Hi, I have a bitmap picture in a sheet. I need a MACRO so as when I click on the bitmap picture a range (selection of cells say A167:E207 will be exported to MicroSoft Word. Help Please! Khalil ...

Using VBA to assign a macro to button
I've searched through several posts but can't find a solution that helps me. Here is what I am trying to do: I have a button with an attached macro that imports a data table from a sheet for the user to edit. When the user is done he must select a save button, which is attached to a macro that copies the data table back to its original place with the new data. All works well to this point. However, There are many data tables that need to be brought in by means of macro attached buttons. Once the user is done, each needs to be saved back to its original location when the use...

telephone calls
when placing a telephone call using outlook, everything works fine except I can't hear the person i am talking to. They can hear me find. When I use a telephone program not associated with outlook every thing works great. can someone help ...

Calling Macro
Hi All, I have macro like this sub unique() '''''''' '''''''' Counter_item = 0 For Each Item In nodups counter_item = counter_item + 1 bic_var = Item Call Ps_Match Next Item end sub I have another macro Sub Ps_Match() ''''' ''''' If counter_item <= 1 Then call another_macro() end if end sub In the first macro i am declaring counter_item equals to 1. In my second macro i need to call that number. At the to of the module put: Public Counter_I...

Workflow -Assembly Call
How does add subtract in the Workflow>> assembly call works. I do understand that ypu can write your own custom workflows, register it and then call them from here, but I am pretty confused with the existing functionality. workflows can call other workflows workflows can also call a method in a .net assembly. ie you could write a piece of code in c# or vb.net and then have the workflow call your code. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "SAM" <SAM@discussions.microsoft.com> wrote in message news:DE...

Creating macro for find and replace
I recorded a macro for find value 0 and replace with nothing. However after I run it on another excel worksheet, there is an error. Run-time error 91: Object variable or with block variable not set. Is there any way to rectify this? Thank you for your help hidaya, It would help if you would post your code. HTH, Bernie MS Excel MVP "hidaya" <anonymous@discussions.microsoft.com> wrote in message news:225d001c45cca$f74af350$a001280a@phx.gbl... > I recorded a macro for find value 0 and replace with > nothing. However after I run it on another excel > worksheet, there...

Async calls to WebService in MFC
Hey guys, I use a webservice over the net which is scripted in VB.Net. This web service receives a username and a password and returns a string structure with multiples infos. If the string is empty, I had the wrong username and/or password. Pretty simple isn't? Ok now the problem is a bit more complex. I'm using an MFC client to connect to these web services, and since they're on the internet, there's a delay between sending the data and receiving an answer. I don't know really how I can tell me client to "wait for the string". The wrapper MFC created for the w...

Macro security message pop up
Is there any way I can have the security message not pop up that asks t disable macros? This usually comes up if the security level is high. also have the excel cells password protected, but not sure if tha impacts the warning or not. Thanks -- sgods ----------------------------------------------------------------------- sgodse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1457 View this thread: http://www.excelforum.com/showthread.php?threadid=27253 Hi if you set the security level to 'Low' you won't get these warning message. BUT I wouldn'...

macros turned off help
I had a problem with refreshing a pivot table automatically. I sorted this with a Page code Excell 2000 trouble now is I seem to have turned off macros and it won't refresh anymore looked up help but can't find simple "to turn macros on" any help appreciated thanks --- Message posted from http://www.ExcelForum.com/ Have you tried closing and reopening the file? Richardjs60 wrote: > I had a problem with refreshing a pivot table automatically. I sorted > this with a Page code Excell 2000 > trouble now is I seem to have turned off macros and it won't refresh &g...

Invalid procedure call or argument?
Hello All This is a re-post of a problem I posted a while ago, but to which I got no suggestions: hopefully I'll be more lucky this time!? I have the following code in a Private Sub: If InStr([ResponseXML], "element") > 0 Then errorfield = "Invalid " & Mid([ResponseXML], InStr([ResponseXML], "element"), InStr(InStr(InStr([ResponseXML], "element"), [ResponseXML], "'") + 1, [ResponseXML], "'") - InStr([ResponseXML], "element") + 1) Else .... Most of the time this works fine, but for some...

Macro needed, Two saves in One
Hi, I need some help. I have a Visio page open and I have saved it once already as a VSD file. So it already has a filename with a aVSD extension. So I'd like to be able to click a macro button that does this Application.ActiveDocument.Save then this Application.ActiveWindow.Page.Export "C:\Documents and Settings\Visio Stuff\PNG\SameFileNameAsVisioFileName.png" i.e. Export the file (keeping the same filename as the VSD name) but as a PNG in my PNG subfolder . Any help appreciated. (Should I move this to a VBA newsgroup ?) Jojo On Mon, 26 Mar 2007 13:46:58 +...

Account balance dsicrepencies within Money 2004 Premium?
I have been using Money 2004 Premium for about six months now and love it. It is so easy to use and has really helped me get a handle on where my money is going and getting my finances under control. This has really been helpful with my "Total Money Makeover". However, I do have one, very small problem. A few months ago a 1 cent discrepency popped up in one account. I have one account with a very small balance of $210.74. The Money account register says $210.74, my bank statements say $210.74, my bank's online info says $210.74 and Money and my bank agree whenever I syn...

Why macro cannot refreshall?
Does anyone have any suggestions on why macro cannot refresh all link? On file.xls, there are import external links on many worksheets, which will automatically update all links once it is opened, but when I use a macro to open this worksheets, the worksheet does not update the import external links at all. Even though, the code .RefreshAll is included, this macro will close this file, but the links do not update, does anyone have any suggestions why RefreshAll coding does not perform? Thanks in advance for any suggestions Eric Public Sub UpdatingLists2() Dim WkbkName As...

Unable to get DxgkDdiRecommendFunctionalVidPn call in windows 7
I am trying to add an initial vidpn topology by implementing the DxgkDdiRecommendFunctionalVidPn call in the WDDM miniport driver. But starting from windows 7 i am unable to get the DxgkDdiRecommendFunctionalVidPn function call from dxgkrnl.After searching through the MSDN documentation i found out that the function is deprecated starting from windows 7. msdn link: http://msdn.microsoft.com/en-us/library/ee220395.aspx According to msdn "On a computer running Windows 7, the display mode manager (DMM) determines an appropriate VidPN topology to apply using VidPN history d...

Address Book sort order within the drop-down menu
I posted earlier, but noticed all the subjects identical to mine, yet the issue is totally different. Hopefully the new subject will clarify. Most users here are using Outlook XP. If you open the Address Book, and then pull down the drop-down menu under "show names from the", you'll see the Global Address List on top, then several hundred Schools and Departments, and at the very bottom you'll see "Outlook Address Book" with Contacts. What users are wanting here is to still have Global Address List as their default address list to be shown, but they don't wan...