How to pass workbook name between subroutines

  • Hi all,


    I have some code that opens a file, if the file name contains X or Y, it does an X defined macro and a Y defined macro.


    The issue I'm having, is how to pass the workbook name that I've defined between subroutines.


    For instance my code is as follows:



    Instead of the MSGboxs, I want the macro to take data from the file I open and copy and do further manipulation.


    The problem is, I don't want to re-open the file again for each new case to get the workbook name (ie wb2 variable). I want to pass that variable name between subroutines. Confused with the syntax so far....

  • Re: How to pass workbook name between subroutines


    Just pass it as a normal parameter.

  • Re: How to pass workbook name between subroutines


    Thanks Cytop!


    many different ways to skin a cat I suppose. I ended up defining string variables to capture the workbook name and then I passed the string variables in my call procedure.


    then in my called procedure, I set the workbook name to the workbook string variable.


    interesting that there's always other ways to get to the same solution

  • Re: How to pass workbook name between subroutines


    There are 99 ways to do anything in VBA.


    Not that it matters for such a simple piece of code, but you already have a reference to the workbook in wb2. You can pass this directly to the procedure; so what's the point of storing the name in a string, passing the string and then using that string to 'find' the workbook in the called procedure? 2 extra steps, must waste all of 6 clock cycles :)


    Seriously though, it's more the principal of the thing - defining the string, storing the name of the workbook in the string, creating another variable to refer to the workbook in the called procedure and setting the reference to the workbook using the string are all unnecessary. Internally, both procedures will refer to the same object so why not simply pass the object to begin with?

  • Re: How to pass workbook name between subroutines


    Quote from cytop;772001

    There are 99 ways to do anything in VBA.


    Not that it matters for such a simple piece of code, but you already have a reference to the workbook in wb2. You can pass this directly to the procedure; so what's the point of storing the name in a string, passing the string and then using that string to 'find' the workbook in the called procedure? 2 extra steps, must waste all of 6 clock cycles :)


    Seriously though, it's more the principal of the thing - defining the string, storing the name of the workbook in the string, creating another variable to refer to the workbook in the called procedure and setting the reference to the workbook using the string are all unnecessary. Internally, both procedures will refer to the same object so why not simply pass the object to begin with?


    Your technique is much more elegant.


    The problem is that I'm learning, so I google my problem and I find something that suits my needs and I adjust the code and test.


    I googled, how to pass a workbook variable to another sub routine and found someone who used the string solution.


    if anyone knows a good way of knowing what every command and syntax does, let me know!