+ Reply to Thread
Results 1 to 7 of 7

how to use the variable defined in another sub

  1. #1
    Registered User
    Join Date
    10-31-2014
    Location
    canada
    MS-Off Ver
    2007
    Posts
    64

    how to use the variable defined in another sub

    Hi,

    I've created a variable in sub test1() with name foldername. This will create a folder with name 071919 in specific directory for example. This sub test1() will call another sub test2() which will produce an excel file. I want to save the excel file produced by sub test2() to the folder 071919. so how can I apply variable foldername in subtest2()?

    Dim foldername As String

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    972

    Re: how to use the variable defined in another sub

    You have to declare the variable in a Module Level ... that is; it must be declared at the top of the module over any existing procedures.

    Run the below procedure "Test1" to see...


    Please Login or Register  to view this content.
    .
    Last edited by Haluk; 07-19-2019 at 04:20 PM.

  3. #3
    Registered User
    Join Date
    10-31-2014
    Location
    canada
    MS-Off Ver
    2007
    Posts
    64

    Re: how to use the variable defined in another sub

    Thanks, Haluk,

    The way you mentioned above works for the 2 subs in the same workbook. My situation is I have one variable foldername defined in workbook1 sub test1() that will create a folder with name 071919 and open another workbook, workbook2. and sub test2() within workbook2 will create an excel file that I want to save to the folder that created in sub test1() 071919. so any idea for this variable declaration?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: how to use the variable defined in another sub

    There are several ways.

    1. Write variable value to temp worksheet and access it from there.
    2. Write variable value to external file and read it from there.
    3. Use Registory as variable store, and read and write there.

    Personally I'd go with option 1 or 2. There may be other methods. But these are quick and easy to implement.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  5. #5
    Registered User
    Join Date
    10-31-2014
    Location
    canada
    MS-Off Ver
    2007
    Posts
    64

    Re: how to use the variable defined in another sub

    Thanks, CK76

    I don't understand what you mean for those 3 options. I give my situation in more detail here. My workbook1 have a cell value "IMF ICCB 071919". The variable and code in sub dur_step_1() reformat this value and create folder 071919 in directory.

    Please Login or Register  to view this content.
    Actually another sub IMF_Duration() within this workbook1 will open another workbook, workbook2.

    Please Login or Register  to view this content.
    The sub IMF_reformat_new() within workbook2 will create a workbook that I want to save to the same folder I created before 071919.

    Please Login or Register  to view this content.
    You can see I defined variable "foldername" in sub dur_step_1, but the same variable I used in Sub IMF_reformat_new() does not work.

    Btw, can you explore more about option 1 and 2? or do you have any resources I can see if this is more convenient.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: how to use the variable defined in another sub

    So... write foldername value into some cell. Then in workbook2, read the value from the cell to be used.

    Or write to text file and read back value from text file in workbook2.

    Alternately you can alter workbook2 sub to have parameter argument. Then call it from workbook1 to pass variable.

    Ex:
    workbook2
    Please Login or Register  to view this content.
    workbook1
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-31-2014
    Location
    canada
    MS-Off Ver
    2007
    Posts
    64

    Re: how to use the variable defined in another sub

    Thanks. I think this is the way I am looking for. Since I am not quite familiar with this way, when I try to duplicate the above code, it does not work. I will speculate it and see how I can apply. I will get back if I cannot solve this issue.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Obect Variable not set error; variable already defined?
    By mrbusto71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2016, 06:27 PM
  2. Variable Error '1004' - Application-defined or object-defined error
    By christopheralan88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-01-2015, 12:09 AM
  3. Macro to Insert defined integer into range defined by variable criteria
    By stereofeedback in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2013, 12:33 PM
  4. Variable not defined error when I have defined the variable
    By jeffadkisson in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-27-2012, 07:00 PM
  5. Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1