Author Topic: Array variables using VBA in Microsoft Excel  (Read 4054 times)

bbasujon

  • Administrator
  • VIP Member
  • *****
  • Posts: 1826
  • I want to show my performance at any where
    • View Profile
    • Higher Education
Array variables using VBA in Microsoft Excel
« on: August 15, 2012, 05:55:38 PM »
Static array variables

Insted of using several unique variables to store information, you can use an array variable.
When you know how many elements you need to store in the array, you can use a static array variable like this :

Sub TestStaticArray()
' stores 10 names in the workbook in the array variable MyNames()
Dim MyNames(1 to 10) As String ' declares a static array variable
Dim iCount As Integer
    For iCount = 1 To 10
        MyNames(iCount) = ThisWorkbook.Names(iCount)
    Next iCount
    Erase MyNames() ' deletes the varible contents, free some memory
End Sub

Dynamic array variables

Dynamic array variables are useful when you in advance don't know how many elements that you need to store information about.
You declare dynamic array variables just like a static rray variable, except that you don't give any information about the
array size :

Sub TestDynamicArray()
' stores all names in the workbook in the array variable MyNames()
Dim MyNames() As String ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer
    Max = ThisWorkbook.Names.Count ' finds the maximum array size
    ReDim MyNames(1 to Max) ' declares the array variable with the necessary size
    For iCount = 1 To Max
        MyNames(iCount) = ThisWorkbook.Names(iCount)
    Next iCount
    Erase MyNames() ' deletes the varible contents, free some memory
End Sub

If you know that you will need an array variable with 1000 items, use a static variable.
The downside is that you will use memory for a 1000 items every time, also in the cases that
you only stores information about 10 items. If you use a dynamic array variable you will use the memory more efficient..

Sometimes it's not possible to calculate how large tha array variable will need to be.
In these cases the size of the array variable need to be increased as necessary. When
you use a ReDim-statement to change the array variable size, the variable contents is also erased.
to avoid deleting the variable contents when you redim the array variable you will nedd to use the ReDim Preserve-statement :

Sub GetFileNameList()
' stores all the filenames in the current folder
Dim FolderFiles() As String ' declares a dynamic array variable
Dim tmp As String, fCount As Integer
    fCount = 0
    tmp = Dir("*.*")
    While tmp <> Empty
        fCount = fCount + 1
        ReDim Preserve FolderFiles(1 to fCount)
        ' declares the array variable again (size+1)
        FolderFiles(fCount) = tmp
        tmp = Dir
    Wend
    MsgBox fCount & " filenames are found in the folder " & CurDir
    Erase FolderFiles ' deletes the varible contents, free some memory
End Sub
Acquire the knowledge and share the knowledge so that knowing,learning then sharing - all are the collection