Be the first user to complete this post

  • 0
Add to List

VBA-Excel: Arrays – One Dimension, Static Array

An Array is a collection of values under the same name and differentiated using index values.

For creating one dimensional static array, follow the below steps

  • Declare an Array
  • Store values in array
  • Retrieve values from array.


Declare an Array

Dim arrData(5)

Store values in array

arrData(0) = "1"

arrData(1) = "2"

arrData(2) = "3"

Retrieve values from array.

MsgBox arrData(3)

Complete Code:

Function FnStaticSingleArray()

    Dim arrData(5)    
 arrData(0) = "1"
  arrData(1) = "2"
  arrData(2) = "3"
  arrData(3) = "4"
  arrData(4) = "5"
  arrData(5) = "6"
  MsgBox arrData(5)

  For i = 0 To UBound(arrData)

        arrData(i) = i + 1

  Next

  MsgBox arrData(3)  

End Function



Also Read:

  1. VBA-Excel: Delete Blank Rows from Excel Work Sheet
  2. VBA-Excel: String Functions – String()
  3. VBA-Excel: Date-Time Functions – DateValue()
  4. VBA-Excel: Date-Time Functions – Month(), Year() and MonthName()
  5. VBA-Excel: String Functions – LTrim()