|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Array variables using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
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 variablesDynamic 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
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)
The One Page Business Plan: Start With a Vision, Build a Company!
MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb
Mortgages For Dummies®
Microsoft PowerPoint Version 2002 Step by Step
Positioning: The Battle for Your Mind | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|