VBA Variable carryforward?

Chris

New Member
Joined
Mar 3, 2002
Messages
25
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top