+ Reply to Thread
Results 1 to 2 of 2

Passing variables back and forth between subroutines

  1. #1
    Registered User
    Join Date
    03-01-2015
    Location
    Michigan
    MS-Off Ver
    MS Office 2010
    Posts
    1

    Passing variables back and forth between subroutines

    Hi, first post here. I have a pretty big subroutine that I'm looking to break into pieces because it got too large to compile. In any VBA programming I've ever done in Excel, I've always just contained everything in one sub and never have broken things into several subs so I just want to make sure I'm doing it optimally. Basically, I'm creating a financial planning program that is doing a large amount of mathematical calculations in the code and interfacing with the spreadsheet as little as possible in order to minimize the time it takes to run. This means I'm using a couple hundred variables, some of which are used all the way thru the main sub from start to finish.

    Here's a simple example of what I'd like to do:

    Please Login or Register  to view this content.
    Using the above as an example, if I need to program to calculate the volume of a cube I'd send the program into that subroutine passing it the variables of length, height, and width. It would calculate the volume and pass it back to the main subroutine. Does this look ok? Are there a limit to how many variables you can pass back and forth within the parentheses? Thanks.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Passing variables back and forth between subroutines

    1) to use such approach you shall make sure volume to be passed byref to subroutine. You could also use function to have the desired behavoiur.
    2) second option is to use public variables (defined outside of subroutines - preferred for easy reading - above subs or functions). If they are to be modified - it is better to make these variables declared outside than every time passed to sub and back.

    Try such sample code:


    Please Login or Register  to view this content.
    PS. just old teacher note - for the cube you need just 1 parameter :-)
    Last edited by Kaper; 03-12-2015 at 11:56 AM.
    Best Regards,

    Kaper

+ 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. Passing different variables back and forth between Sub and Function
    By jamesdeuce in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2014, 01:22 PM
  2. [SOLVED] can't debug some subroutines in my project, locked subroutines
    By florin_excel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-28-2012, 01:16 PM
  3. Passing Variables out of Subroutines
    By cmcconnehey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2007, 08:20 PM
  4. Calling subroutines and passing variables
    By matpj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2006, 11:25 AM
  5. Calling subroutines and passing variables
    By matpj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-29-2006, 10:55 AM

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