Have a VBA String-Array To Point to a Specific Cell of Another String Array

    Hello guys. I have an extremely large array of String in Excel VBA and I want to dump different parts of it onto different worksheets. To make it faster, I'm of course intending to do a single-step dump, so if I call that big array "DataCache ()" I want to do something like:



    Sheets (1).Range ("B2").Resize (1000000,250).Value = DataCache (2,2)         ' Starting from the slot (2,2) of the array and dump onto sheet1

    Sheets (2).Range ("B2").Resize (1000000,250).Value = DataCache (2000000,5)   ' Similar thing from array slot (2000000,5) for sheet2

    ...



    Is there any way to do this without having to copy the 2nd portion of the array into a new array? I was thinking maybe I can do something like this (conceptually) :


    Dim SubArry () As String

    ....

    SubArray = VarPtr ( DataCache (2000000, 5))

    Sheets (2).Range ("B2").Resize (1000000,250).Value = SubArray


    I'd appreciate it if anyone could help me with this. I'm using "Microsoft VBA 7.1" on Office 2021 x64.

    • Best Answer

    Hi F_Sadr,

    you can use the index function to slice the array .. something like below


    Code
    Sub test()
    With Sheet1
        .Cells(1, 1).Resize(1000000, 250) = Application.index(DataCache, Evaluate("ROW(1:1000000)"), Application.Transpose([row(1:250)]))
    End With
    With Sheet2
        .Cells(1, 1).Resize(1000000, 250) = Application.index(DataCache, Evaluate("ROW(1000001:2000001)"), Application.Transpose([row(1:250)]))
    End With
End Sub

    Thank you very much; it's working and very interesting too but I'm a bit confused about the parameters you have passed to the Index function. Would you please help explain a bit about the Application.Transpose([row(1:250)]) part? The first unfamiliar thing for me was the strange syntax of using brackets [] in it (which I just guessed is to define an Array structure) and the next one, was the use of "Transpose". Wasn't there a way to directly specify a one-row-multi-column array structure through something like Column(1:250)?

    Hi,

    column works.. thinking i was using evaluate function when the rows or columns were dynamic and used [ ] where the array was fixed


    with test array

    Code
    Dim dbArray, myrow As Long
    myrow = 5
    With Sheet3
        dbArray = .Range("A1:I20").Value
        .[K1:S20].ClearContents
        .Range("K2").Resize(5, 9) = Application.Index(dbArray, Evaluate("ROW(1:" & myrow & ")"), Application.Transpose([row(1:9)]))
        .[K9].Resize(5, 9) = Application.Index(dbArray, [ROW(6:11)], Application.Transpose([row(1:9)]))
        .Range("K16").Resize(5, 9) = Application.Index(dbArray, Evaluate("ROW(16:20)"), [column(1:9)])
    End With

  • F_Sadr

    Selected a post as the best answer.

Participate now!

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

Register Yourself Login