VBA Variable carryforward?
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA Variable carryforward?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a module which runs a subroutine and some variables are defined within this subroutine. I also have the variables set as Public before the Sub MACRO() line. The routine runs, and then another sub-macro is called within this one which will use the variables as defined from the previous macro. When the sub-macro runs, it doesn't seem to carry forward the values for the variables that were defined in the previous macro! Hopefully that sort of made sense but here's an example to illustrate what I mean

    EG

    Public MONTHI as Integer
    Public YEARI as Integer
    Public YEARS as String
    Public MONTHS as String
    Sub MyMacro()

    For MONTHI = 1 to 12
    For YEARI = 85 to 86

    MONTHS = MONTHI
    YEARS = YEARI

    ......
    PARTTWO

    Next MONTHI
    Next YEARI

    End Sub

    Sub PARTTWO()

    Workbooks.Open Filename = "ABC" & YEARS & ".xls"

    End Sub


    So when it goes to open ABC for the current year the macro is working on, the YEARS variable is empty.

    Thanks in advance!




  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One problem i see is that your next statements are the wrong way round to correspond with the for statements.

    Suggestion is that you can append integers using &, you don't need to convert them to strings and can get rid of these publics.

    If the variables are not carrying through, it may be because you have put this code into a workbook or worksheet module. Open a new module of your own outside of these scopes and do the code, or at least the public declarations there, and they should work.

    Another approach might be to just pass the variables directly without them needing to be public...

    Sub partone()

    For months = 1 To 5
    For years = 1 To 5

    parttwo months, years

    Next
    Next
    End Sub

    Sub parttwo(months, years)
    MsgBox months & " ... " & years
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried both of these and so far no luck. When i place my cursor over the word "YEARS" in the code it says "Empty" and I get the error "Subscript out of range". Any other ideas?


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    feel free to send me the workbook...

    david@monnetqos.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com