Array and Loop needed (I think) so I don't have to repeat the same sub routine call

[chrisr01536]

This is my first ever script with Excel VBA and first time I have used Excel 2010. I have written some UNIX scripts which pull performance information from servers and output .csv files. Excel then picks these up and generates performance graphs.

I think I have done quite well so far and have integrated a form and a do while loop elsewhere in the VBA solution, but I think I need an array and for loop to remove the repetition from the below code. I've tried lots of different syntax but I must be being stupid somewhere, because I just can't seem to get it to work.

2 variables change for each loop (vServer has 9 values and vType has 6 values). I am repeating this code in a total of 54 loops, so really I think I need an array to replace each variable and a for loop to remove all the repetitions. Although it is working I am looking to roll this out onto much more servers and include much more vTypes, so this could quickly become a bit of a mess.

For your info, the variables vServer and vType are declared globally (To the module at least).

'Loop 1
'' Set Variables/ Arguments
    vServer = "ora1dev"
    vType = "cpu"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

'Loop 2
'' Set Variables/ Arguments
    vServer = "ora1dev"
    vType = "mem"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

'Loop 3
'' Set Variables/ Arguments
    vServer = "ora1dev"
    vType = "inodu"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

'Loop 4
'' Set Variables/ Arguments
    vServer = "ora1dev"
    vType = "inoda"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

'Loop 5
'' Set Variables/ Arguments
    vServer = "ora1dev"
    vType = "disku"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

'Loop 6
'' Set Variables/ Arguments
    vServer = "ora1dev"
    vType = "diska"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

'' app1dev

'Loop 7
'' Set Variables/ Arguments
    vServer = "app1dev"
    vType = "cpu"
'' Call Sub-routine
    Call Performance_charts_Master(vMnth, vYear)

The intention would be to implement arrays and for loops now (Unless these is a better way of doing it) and then from there I would intend to have the array defined via forms (or similar) instead of being free-texted in the code. (But I need to think through my data a bit more to understand the best way to do that).

Eventually (once all working and robust) I'll be happy to publish the full 2 part solution (UNIX and VBA)

Help would be hugely appreciated and I have tried to follow the guidelines for posts and hope I have met the requirements.

 

[tlowry]

Here is one suggestion

(both arrays must have the same number of elements)

I, of course, could not test this, but I have hope

Sub demo()
    Dim aServer
    Dim aType
    Dim i
'===============================================
' you need to fill in the rest of the arrays text

    aServer = Array("ora1dev", "ora1dev")
    aType = Array("cpu", "mem")
'===============================================
    
    For i = 0 To UBound(aServer)
        vServer = aServer(i)
        vType = aType(i)
        Call Performance_charts_Master(vMnth, vYear)
    Next i

End Sub

 

[chrisr01536]

Here is one suggestion

(both arrays must have the same number of elements)

I, of course, could not test this, but I have hope

Sub demo()
    Dim aServer
    Dim aType
    Dim i
'===============================================
' you need to fill in the rest of the arrays text

    aServer = Array("ora1dev", "ora1dev")
    aType = Array("cpu", "mem")
'===============================================
    
    For i = 0 To UBound(aServer)
        vServer = aServer(i)
        vType = aType(i)
        Call Performance_charts_Master(vMnth, vYear)
    Next i

End Sub

Absolutely perfect; Thank you very much! :D

 

[chrisr01536]

Thanks alot for your help with the array, I am very impressed with how exactly it meets what I am trying to do.

I'm trying to take this to the next level and have opened a new thread:

http://www.mrexcel.com/forum/showthread.php?t=602931

I'm not sure if what I'm asking for is too difficult/ not possible/ not very well explained, but no-one seems to be able to help me with it yet.