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 39879 articles. 2 followers. Follow

13 Replies
2145 Views

Similar Articles

[PageSpeed] 11

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:

Moving macro/toolbar to another workbook file
This is killing me. I know I've used the "Organizer" to copy macros and toolbars to other workbook files. Well I don't know if I've used it in Excel, but I have used it in Word and on Mac OS 9.2 recently. I thought that I've used it on my Win XP Pro machine with Office XP, but still can't recall if I've ever used it with Excel. I've looked all through help and can't find any reference to it. Is the "Organizer" available to Excel 2002 on Win XP Pro? Does it have to be installed separately? How do I get to it? Thank you for any help any...

Creating graph from values in multiple tabs within workbook
I am trying to produce a simple line graph from values in worksheets within a workbook. Worksheet 01/01/07 Share price A �5 Worksheet 01/01/07 Share price B �2 Worksheet 01/02/07 share price A �4 Worksheet 01/02/07 share price B �2.50 I thought that you could hold the ctrl key down and select the data from each cell in the worksheet but I have obviously got that wrong. As you can see from above I am looking to initially create 2 lines over a say a year to monitor the values of a number of different share prices. Can someone point me in the direction of an idiot guide of how to do this....

Using A Macro/VBA code to re-set formulas
Hi can any one help me with a Macro/VBA code to re-set formulas in pre-defined set of cells i.e. in my case C14:C40 At present I have a copy of the Formulas in another part of th worksheet and when I want to reset the worksheet (as the user can ove right the formulas as the formula is based on either the sum of anothe two cells or the users input) I use a Macro to copy and paste (special the formulas. What I require is a Macro/VBA Code which already has the formula store in the actual Macro/VBA code i.e. so they are not stored as copy in m worksheet Ay help would be greatly appreciated T...

Secure Call Center Tracking
I'm looking to use MS CRM to track our members and what contacts we have with them. One of our departments operates a call center for Health Insurance question. They often deal with HIPAA covered info. I was hoping to add a checkbox labeled "secure", that if checked, restricts all access to just this particular group. Is this possible? Is there a better way? Should I just create a custom entity for call tracking and restrict access to that entity to just their team? Thanks! Doug Evans IT Manager Association of Washington Cities Doug - Yes, it is possible with the ch...

Need to do housecleaning on my macros
Having been migrated to a new system, it's time for a bit of housecleaning. I've got macros I wrote for one use and never used again, and I can't find some I've come to rely on. So my idea is to export all the modules from all my template projects and import them into one new template to use as an add-in. Along the way, I can evaluate whether the macro is something I still need or can do without. Is there an easy and automated way to export all modules from a project? Is there an easy and automated way to print these modules? What is the practical size limit of ...

A Tricky Question! Macro & Chart
Hi All I have a very tricky problem here. I have a column chart which has sa 4 columns - namely, A, B, C and D. First Issue - I would like to check is it possible to assign a specifi macro for A, B, C, and D to their respective columns? Second Issue - And as each macro has its own codes, would it b possible for the macro to be so smart that if I click on the column fo A, A macro will run... Please note that the order of the columns will change over time as will sort the values and put the one with the highest value as th first column. Kindly advice me whether the above are possible.....

2 different loan payments within 1 transaction
Hi, I'm using MS Money 2006 and I love the functionality so far, but I have recently run into this new problem. It's kind of a rare occurence, so I hope someone can help me out. I have 2 student loan accounts, one subsidized and one unsubsidized so they can't be consolidated further. Both of these loan accounts are set up separately within MS Money. The problem seems to be that only ONE combined payment (transaction) goes towards both loans at once. This one lump sum gets automatically divided at roughly 85%/15% between the loans. Now, I've split income transaction...

VBA calling sp with ANSI_NULLS set ON
Hi, On Query Analyzer, I created a sp as follow: > > if exists (select * from dbo.sysobjects where id = object_id(N'dbo.PCS_SEG1') and OBJECTPROPERTY(id, N'IsProcedure')= 1) DROP PROCEDURE dbo.PCS_SEG1 GO CREATE PROCEDURE dbo.PCS_SEG1 as set ANSI_NULLS on set ANSI_WARNINGS on ** sql statement *** > > I need the to set ANSI_NULLS and ANSI_WARNINGS on as it involves another server using linked server. When executing the sp from query analyzer, it works fine too: > exec PCS_SEG1 > But, when I called from VBA, I received error: Heterogeneous queries require t...

how do i change or make this macro??? pictures and code included
i need to either make this from scratch or change it to suit my needs. Firstly a link to the template it is from http://www.exinfm.com/excel%20files/FY2000WhatIf.xls the code is Sub projection() Dim revgrowth As Single, costpercent As Single, smgrowth As Single devgrowth As Single, gagrowth As Single Dim intincome As Integer, noncontitems As Integer, otherexps A Integer Dim taxrate As Single, avgshares As Integer 'dialog box gets displayed With DialogSheets("dialog3") If .Show = False Then Exit Sub End If revgrowth = .EditBoxes(1).Text costpercent = .EditBoxes(2).Text smgrow...

Workflow Call Assembly to add Date or Time
I cannot get a very simple workflow to work. All I want it to do is update a datetime field to add one month! Workflow monitor says it completed, but it has never worked... Has anyone ever been able to make this work? On 26 Mar, 18:54, "DubSport" <jamie.carmich...@cmgl.ca> wrote: > I cannot get a very simple workflow to work. All I want it to do is > update a datetime field to add one month! Workflow monitor says it > completed, but it has never worked... Has anyone ever been able to > make this work? Show your workflow rule OK here it is. its a manual rule, s...

Macro Buttons #2
i know how to create a macro button, my question is, how do i get that macro button to display on someone elses PC? i created the macro button in a worksheet then i sent that worksheet to a friend. the macro exists, but the button doesn't. any ideas? Thanks in Advance James You may want to attach the toolbar to the workbook. But if you do, read this first from Jan Karel Pieterse: http://google.com/groups?threadm=083701c3559b%246ce75e60%24a301280a%40phx.gbl James wrote: > > i know how to create a macro button, my question is, how do i get that macro > button to display on...

Macro to create pivotchart problems
Put together a quick macro to assemble a pivotchart, nothing fancy just ran through it recording and tried to run it on the same data afterwards, and have come upon this error: Run-time error '1004': Unable to get the PivotFields property of the pivottable class When I go to debug, the error is occurring at the asterisked line: Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Check results'!C1:C5").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", Def...

Insert a row based on a macro
Is there a way to insert a row based on a formula. For example, If A5=25, I then want to duplicate the existing row and insert it in the line below. See answers in your other post. Pls don't multi post -- Don Guillett SalesAid Software donaldb@281.com "Mindie" <Mindie@discussions.microsoft.com> wrote in message news:75A1DDD5-6334-410D-8646-091B43D1B198@microsoft.com... > Is there a way to insert a row based on a formula. For example, If A5=25, I > then want to duplicate the existing row and insert it in the line below. ...

2000 macro problem running with 2003
Is there any reason a macro written for Excel 2000 will not run with Excel 2003? John, Code written in Excel 2000 should work fine in 2003. Can you post an example of your code that is not working? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John Leonard - Sage" <sagegrp@adelphia.net> wrote in message news:%23c7QWziUEHA.3476@tk2msftngp13.phx.gbl... > Is there any reason a macro written for Excel 2000 will not run with Excel > 2003? > > I will get the code and post it. Thanks "Chip Pearson"...

in a macro how to make a part of the formula a variable
I am trying to copy a cell from one file into another file in a macro. There are hundreds of files all linking to the same cell so I thought I would set the formula up so that is has a variable for the filename but I can't figure out the syntax. The formula should link to cell $r$16 in worksheet "monthly" in the changing file name and the variable I used for the changing file name is budget_file. The following didn't work. Can someone tell me how to do this? ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16&qu...

Inconsistent macro problem in excel
XP was rolled out at my work yesterday. Most of the excel file I work with have Visual Basic macros coded into them. (I use Excel 2002) Sometimes, I'll open a file, and it will give me the "Enable Macros?" prompt. This is normal, and what I want it to do. Sometimes, I'll open the same file, and it will give me no prompt. And if I try to run one of the macros, it tells me that the macros are disabled because the security is set to high. So, I go the macro security area to check it out...it's on medium, like it always is. I've found that the only consistent way ...

Is there a simlpe SOAP -> method call example that doesn't use any transports, etc.?
I have not had any luck searching for a simple, simple example that calls a method on an object given a SOAP string. Here's what I'm looking for (in pseudocode): string SOAPCall = "bla bla bla"; // SOAP method invocation XML string SOAPResponse; MyObject x = new MyObject(); // and here's where I don't know what to do /* call the method on x as specified in SOAPCall and get the output into SOAPResponse. */ Can anyone be of any assistance? Thank you! "Nimai Malle" <nimai_malle@yahoo.com> wrote in message news:7ace219f.0408200729.29ba1498@posting....

How to save macro changes in Read-Only documents?
I have over 100 documents that need the same sentence changed in each one. The documents have the read-only recommended box checked. I ran a batch to remove the password to modify but it didn't remove the read-only recommend. So when I run a macro to change the text I get an error. When I ran Debug it highlighted "myDoc.Close Savechanges:=wdSaveChanges" but I don't know what to do with that. (I only copy the macros from this great site!) I am looking for a way to remove the read-only recommended from multiple documents in a file. Please help. Many th...

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...

Problem closing CDialog calling OnOK..
I am creating a dialog based program (using MFC VC3). Apart from the mainscreen I want each dialog to close on clicking the next button to progress to the next screen. I am using OnOK() which SEEMS to be closing the dialog but isn't isnfact being removed from memory, this is causing a problem when I go back and forth from the dialogs (admittedly more times that I would hope a user would). This causes a stack overrun and crashes my program.. Is there a way to properly close the dialog? I have tried EndDialog and DestroyWindow to no avail... Thanks for any help... Kay > I am using ...

macro to run automatically when a field is updated
I have two form fields on an employee appraisal form. One calculates a grand average score from four different scoring areas on the form. The second field evaluates this score (using a macro) to determine the increase percentage based on the grand score. This macro is assigned to the field to run on entry. My problem is that if an evaluator goes back through the form and changes a score, they must click in this percentage field to get it to update to the correct value. My question is: how could I make this field update automatically anytime a change is made anywhere on t...

What's this web page feature called?
On the yahoo home page, if the mouse pointer hovers over items in the "My Favorites" list, a floating window opens up. http://m.www.yahoo.com/?r0=1258393277 Or www.yahoo.com which takes you to the first link. What's that feature called? I'm trying to find a way to disable it in IE 8. No clear responses since yesterday. I'm hoping maybe someone here has enough page design knowledge to just provide a bit of vocabulary. This group deals with Windows XP issues, the internetexplorer.general group would be a better place to ask. -- -- &qu...

Formula in Macros
I am trying to do a average for a ongoing twelve week period in my macro. When the macro updates it adds the last column but I can't get it to drop the first column so it goes from averaging 12wks then 13wks,14 weeks and so on. Is there a command I'm missing. Thanks in advance for any help you can give me.I'm on Excel 2002. SH This code averages the 12 columns in the above row. ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-11]:R[-1]C)" By its nature, copy this formula across next week and it will pick up just 12 columns. What does your code look like? -- HTH ...

Calculations not keeping up with macro
Hello, I have a spreadsheet with 51 tabs. Tab_1 is a summary of data contained on the remaining 50 sheets (such as averages and sums). A macro iterates through values of 1 - 100. During each iteration a variable with dependents changes on each of the 50 sheets and resulting data for each iteration is output on the summary tab (output such as the averages and sums for each iteration). The macro works great when I step through it, the problems occur when I run it at full speed. The output data at each iteration does not keep up with the macro, thus the outputs are inaccurate. I...

Hyperlink target frames within Outlook
I have a problem where I have several hyperlinks within an Outlook message and after a user clicks the first hyperlink, I'd like the consequent hyperlinks within that e-mail to appear in that same window, but instead Outlook keeps popping up new windows. ...