Referencing arrays

  • Hi. I'm trying to do a loop through four arrays named Asc, Bsc, Csc and Dsc. I'm using the following code to do this but it comes up with a Type Mismatch error on the penultimate line. Please could someone explain the problem? I'm getting a bit confused as to whether I'm mixing up different types of variables, and not sure if CVar is the appropriate function.


    Code
    For ch = 65 To 68
        Sc = CVar(Chr(ch) & "sc")
        For r = 1 To 3
            If Sc(r, 1) = "" Then                'ERROR HERE
                etc...


    Thanks in anticipation.

  • Re: Referencing arrays


    So, you are making a string, and then trying to reference that string as a variable name. You can't do that in VBA ( at least, I don't think you can ). There are several languages around where you can do this, but I don't think there is a way in VBA.


    How are you setting up these arrays? Do they match worksheet ranges or defined names?

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • Re: Referencing arrays


    One way would be to use a collection to store the arrays.
    [vba]Sub x()


    Dim colTemp As Collection
    Dim vntArr1(2, 3) As Variant
    Dim vntArr2(2, 3) As Variant
    Dim vntArr3(2, 3) As Variant
    Dim vntArr4(2, 3) As Variant
    Dim vntArr As Variant
    Dim lngItem As Long

    Dim lngIndexA As Long
    Dim lngIndexB As Long

    For lngIndexA = 1 To 2
    For lngIndexB = 1 To 3
    vntArr1(lngIndexA, lngIndexB) = lngIndexA * lngIndexB * 2
    vntArr2(lngIndexA, lngIndexB) = lngIndexA * lngIndexB * 4
    vntArr3(lngIndexA, lngIndexB) = lngIndexA * lngIndexB * 8
    vntArr4(lngIndexA, lngIndexB) = lngIndexA * lngIndexB * 16
    Next
    Next
    Set colTemp = New Collection
    colTemp.Add vntArr1, "vntArr1"
    colTemp.Add vntArr2, "vntArr2"
    colTemp.Add vntArr3, "vntArr3"
    colTemp.Add vntArr4, "vntArr4"

    For lngItem = 1 To 4
    vntArr = colTemp.Item("vntarr" & lngItem)
    For lngIndexA = 1 To 2
    For lngIndexB = 1 To 3
    Debug.Print "vntArr" & lngItem, lngIndexA; " "; lngIndexB, vntArr(lngIndexA, lngIndexB)
    Next
    Next
    Debug.Print
    Next

    End Sub[/vba]

  • Re: Referencing arrays


    Thanks guys. I'm going to try to get my head round Andy's code which isn't immediately obvious to my limited VBA mind. If I can't crack it I might be back!


    Quote

    So, you are making a string, and then trying to reference that string as a variable name. You can't do that in VBA.


    OK, I wondered if that was the case.


    Quote

    How are you setting up these arrays? Do they match worksheet ranges or defined names?


    They are worksheet ranges.

  • Re: Referencing arrays


    Quote


    They are worksheet ranges.


    In that case what about naming the ranges, and referencing the entries that way? ( using the Range(name) object )

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • Re: Referencing arrays


    Quote from GlennUK

    So, you are making a string, and then trying to reference that string as a variable name. You can't do that in VBA ( at least, I don't think you can ). There are several languages around where you can do this, but I don't think there is a way in VBA.


    How are you setting up these arrays? Do they match worksheet ranges or defined names?



    Not sure if this has anything to do with the OP, but...


    It is indeed possible to convert both 1D and 2D array strings to arrays in VBA. In fact I was just talking about it earlier today.



    I'm using the shorthand variant for the Evaluate method in the first two, so if you want to actually build a string and feed a variable you'd have to be more explicit with your Evaluate usage as I did in the third example.


    Pretty timely actually...
    Evaluate - Most Powerful Command in VBA?
    http://www.ozgrid.com/forum/showthread.php?t=52372



    I just posted the above topic in the cool forum. Probably the most powerful command the VBA developer has at his/her disposal.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Referencing arrays


    Quote from Aaron Blood

    That's great you've used it before... I was somewhat under the impression that you were unaware that it could convert strings to arrays.


    I hadn't thought of that as a solution to this particular problem. Great to remind us all of what can be done, thanks.

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!