Passing Arrays to Sub Procedure

Very Very Important Tutorial for VBA Developers.

"Passing Arrays to the SubRoutine"

You won't be complete without using arrays in programming. Arrays are like blessings to the programmers. Like Juliet. :D

So usually in the life of Excel VBA developers, we code sometimes repetitive task within a single procedure. Like creating the same excel sheet or template for their office staff or for their customers using a single data table.

So what happens actually, we always Loop through a database for generating each report or template of many. so, it's a time consuming and it's even a bad way of using code VBA resource.

So what arrays will do, in a first attempt, arrays will collect the whole table into it and then you can use it for the rest. but then comes if we more repetitive processes then we have to create subroutines or sub-procedures to cut down the length of the main procedure.

So, now it's again necessary to pass the array to that subroutine or subprocedure otherwise we have to do the same procedure, again and again, we keep wasting time to fill the arrays and so on. So here is the little code will help you to understand the concept.

Option Explicit
Sub TestingArrays()
Dim Arr() As String
Dim temp As String
Dim I As Byte, J As Byte

temp = "A|B|C|D"
Arr = VBA.Split(temp, "|")

'Following is Process
For I = 1 To 5
    Call TestingLinkedArray(Arr)
Next I
End Sub

Private Sub TestingLinkedArray(ByRef Arr2() As String)
Dim I As Byte
For I = LBound(Arr2) To UBound(Arr2)
    Debug.Print Arr2(I)
Next I
Debug.Print "-----"
End Sub

- Kamal Bharakhda