• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Declare integer values globally

Hello,

I want to declare integer values say 7 to 13 globally and iterate all sub macros from 7 to 13. How can I do that?
Because my sheets and folder names (from which i am importing data) are ending with number.
e.g. PartData7, PartData8 etc. and H:\FeaturesData\data\7, H:\FeaturesData\data\8, etc
I have several sub macros where I have removed number and have added i value.

Code:
Sub Main()
Dim i As Integer
' loop thru sheets and folders
For i = 7 To 13
    Sheets("PartData" & i).Select
    Range("A1").Select
    test1
    test2
Next i
End Sub

Sub test1()
    Sheets("PartData" & i).Select
    ActiveWindow.SmallScroll Down:=-3
    Range("A2").Value = test
End Sub
Sub test2()
Sheets("PartData" & i).Activate
    Range("A1").Value = 10
End Sub
 
Put the dim first

Code:
Dim i AsInteger

Sub Main()
' loop thru sheets and folder
For i = 7 To 13
    Sheets("PartData" & i).Select
    Acvtivesheet.Range("A1").Select
    test1
    test2
Next i
EndSub
 
Last edited:
Just to add an alternative to the above, as you're not actually making any changes to the integer value and purely using it as a reference in the sub examples you listed you could always pass it as a parameter to the other sub routines:

Code:
Sub Main()
Dim i As Integer
' loop thru sheets and folders
For i = 7 To 13
    Sheets("PartData" & i).Select
    Range("A1").Select
    test1(i)
    test2(i)
Next i
End Sub

Sub test1(i as Integer)
    Sheets("PartData" & i).Select
    ActiveWindow.SmallScroll Down:=-3
    Range("A2").Value = test
End Sub

Sub test2(i as Integer)
    Sheets("PartData" & i).Activate
    Range("A1").Value = 10
End Sub
 
For the OP’s benefit passing the variable is quite ok
Just be careful as any changes to i in the subs won’t be reflected back in the main routine

Setting it as a global variable means changes can be made anywhere and follow on to future uses in that or other routines
 
Since the default is to pass ByRef, changes would be propagated back up to the calling routine - which is probably not wanted here, so passing ByVal would be better.
Also, it should either be:
Code:
    test1 i
    test2 i

or:

Code:
   Call test1(i)
   Call test2(i)

rather than:

Code:
    test1(i)
    test2(i)

It won't actually make a difference here, but that's a bad habit to get into. ;)
 
Put the dim first

Code:
Dim i AsInteger

Sub Main()
' loop thru sheets and folder
For i = 7 To 13
    Sheets("PartData" & i).Select
    Acvtivesheet.Range("A1").Select
    test1
    test2
Next i
EndSub
With above change, test1 and test2 sub don't know value "i". So what I did is put value of "i" in one of the sheet and accessed that cell value instead of "i" in sub test1 and test2. With this I could run Main sub in loop.
 
Back
Top