Passing variable from child sub to parent sub

Halvar · Mar 24th 2015

Would anyone know a way to pass a variable from a child sub back to its parent sub (i.e., from a sub that's been called in the main routine to that main routine? Something along the lines:

Code

Sub Parent()


Dim i as Integer
   Child
   ...
   i = i + 1


End Sub

Code

Sub Child()


   i = 1


End Sub

And then when I continue in Parent sub after executing Child sub it would already know that i = 1 since that's what we've determined in Child sub. So, my answer in Parent sub would be 2 in the provided example.

Would appreciate your help!

cytop · Mar 24th 2015

Re: Passing variable from child sub to parent sub

Make the Child Sub a Function returning a value...

Code

Sub Parent() 
     
    Dim i As Integer 
    i = Child() +1
    ... 


     
End Sub 


Function Child() as Integer
     
    Child = 1 
     
End Sub

Display More

**PCI** · Mar 24th 2015

Re: Passing variable from child sub to parent sub

What about

Code

Option Explicit


Sub Parent()
Dim i As Integer
Dim ii As Integer


    ii = Child(i)
  
    ii = ii + 1
     
End Sub






Function Child(ii As Integer) As Integer
     
    Child = 1
     
End Function

Display More

Halvar · Mar 24th 2015

Re: Passing variable from child sub to parent sub

cytop and PCI, that would be a good solution, but unfortunately Child needs to stay a Sub as it runs a lot of other stuff.

Here's why I'm doing it this way: Child sub opens an external workbook and imports some information. If that information has already been imported I want to exit Child and go back to Parent. Parent has a message box notifying the user whether the Parent macro has finished running successfully, and it evaluates a number of things in Parent to determine that. One of the factors that I would like to consider is whether Child sub was able to successfully import the information. I thought the easiest way to do this would be to pass a boolean variable from Child to Parent (True for successful import and False for unsuccessful).

Hope this clears things a bit.

**PCI** · Mar 24th 2015

Re: Passing variable from child sub to parent sub

Or perhaps

Code

Option Explicit
Public i As Integer


Sub Parent()


    Child
  
    i = i + 1
     
End Sub






Sub Child()
     
    i = 1
     
End Sub

Display More

Halvar · Mar 24th 2015

Re: Passing variable from child sub to parent sub

I see, so basically turning i into a Public variable? That might work. Thanks a lot, that's a good suggestion.

**PCI** · Mar 24th 2015

Re: Passing variable from child sub to parent sub

Not sure to get it right but why not to change the sub Child to a function returning True or False.
What are the other information to exchange between Child and Parent.
Whatever the Public declaration could be good.

**PCI** · Mar 24th 2015

Re: Passing variable from child sub to parent sub

To turn i to Public gives the possibility to use i where ever you want (in the module) not only in Parent or Child

Halvar · Mar 24th 2015

Re: Passing variable from child sub to parent sub

PCI, I've mentioned in one of my posts that I'm importing data and doing other things in Child, so although I think it might be possible to do all that if I convert Child to formula passing the variable is not the main purpose of that routine. The variable is there just let the subs "talk" and exchange information about successful/unsuccessful macro run. But I think Public variable is a good solution here, I will go with that one.

**rory** · Mar 25th 2015

Re: Passing variable from child sub to parent sub

Quote from Halvar;741541

unfortunately Child needs to stay a Sub as it runs a lot of other stuff.

That doesn't really make any sense - a Function can do anything a Subroutine can do (assuming you're not calling it from a cell, which you clearly aren't).

In any event you can just pass a variable from the calling routine to the child routine, have it changed and then the calling routine can use it as needed:

Code

Option Explicit 
Sub Parent()
    Dim i As Integer
     
    Child i
     
    i = i + 1
    MsgBox i
     
End Sub
 
Sub Child(i As Integer)
     
    i = 1
     
End Sub

Display More

Halvar · Mar 25th 2015

Re: Passing variable from child sub to parent sub

rory, as I've explained, the main purpose of my routine is not to pass the variable, that is just a "subproduct." After people have mentioned the idea I've realized this can be done, but I think it's confusing and not a good coding practice, at least not in my book. When I look at the code, I expect Functions to calculate variable's value and pass it back; when I think of Subs, I look for actions that Excel is asked to perform. Call it a personal preference, but since there's another easy solution I'd rather go with public variable.

**rory** · Mar 25th 2015

Re: Passing variable from child sub to parent sub

I can't say I disagree particularly - although I don't see why you wouldn't make the part that sets the variable a separate function.

One of the other fundamentals of good programming is to restrict variable scope to the narrowest possible. In this case, simply passing a variable from the calling routine to the callee is preferable to using public variables, IMO.

Passing variable from child sub to parent sub

Participate now!

Share