Calling Subrountines within subroutines

[Tangier]

Hi,

I ave received some great advice on designing a subroutine, lets call
it, subroutine 1, that creates a string of Child IDs to feed into a
query, subroutine 2.

Sub routine 2 will execute the query and output the result in another
spreadsheet.

However, where do I call subroutine 1 to generate the string. I get
errors when I try to call the subroutine1 within subroutine 2. What is
the proper syntax for calling subroutine 1 within subroutine 2?

 

[Bernard Liengme]

Generally you just code the name of the subroutine to be called. Let his be
Generate

.....
If X = 3 Then
Generate
Else
,,,,


But for this to work, the two subs need to live in the same module sheet.

 

[Tangier]

Generally you just code the name of the subroutine to be called. Let his be
Generate

....
If X = 3 Then
  Generate
Else
 ,,,,

But for this to work, the two subs need to live in the same module sheet.
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email









- Show quoted text -

Hi yes the two subroutines do reside inside the same module. But
subrountine 1 generates the string that I need for subroutine 2. So
how do I get that string generated from subroutine 1? Thanx!

 

[Bernard Liengme]

With an argument
Make the header: Sub Generate(mytext As Text)

.....
The call it with

ThisText = "Christmas"
Generate (ThisText)

A Google with search term "excel vba sub argument" gives about 73K sites
with advice
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Generally you just code the name of the subroutine to be called. Let his
be
Generate

....
If X = 3 Then
Generate
Else
,,,,

But for this to work, the two subs need to live in the same module sheet.
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email









- Show quoted text -

Hi yes the two subroutines do reside inside the same module. But
subrountine 1 generates the string that I need for subroutine 2. So
how do I get that string generated from subroutine 1? Thanx!

 

[ShaneDevenshire]

Hi Tangier,

Another way to solve this is to declare a global variable:

At the top of the Module, above any subroutines type
Dim myVar as string

Then once one macro is called and myVar is populated the next macro will
know the value of myVar.

in you code you will store the string in myVar

The name is not important.

If you wanted to use this variable in any module you would declare it with
Public myVar as String

The as String part is not required but good practice in both cases.