VBA Variable carryforward?

[Chris]

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!

[daleyman]

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

[Chris]

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?

[daleyman]

feel free to send me the workbook...

david@monnetqos.com