$show=home

VBA - Arrays

VBA - Arrays Shout4Education

We know very well that a variable is a container to store a value. Sometimes, developers are in a position to hold more than one value in a single variable at a time. When a series of values are stored in a single variable, then it is known as an array variable.

Array Declaration

Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses parenthesis. In the following example, the size of the array is mentioned in the brackets.
'Method 1 : Using Dim
Dim arr1() 'Without Size

'Method 2 : Mentioning the Size
Dim arr2(5)  'Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")
  • Although, the array size is indicated as 5, it can hold 6 values as array index starts from ZERO.
  • Array Index cannot be negative.
  • VBScript Arrays can store any type of variable in an array. Hence, an array can store an integer, string, or characters in a single array variable.

Assigning Values to an Array

The values are assigned to the array by specifying an array index value against each one of the values to be assigned. It can be a string.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim arr(5)
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript"    'String
   arr(2) = 100        'Number
   arr(3) = 2.45        'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
  
   msgbox("Value stored in Array index 0 : " & arr(0))
   msgbox("Value stored in Array index 1 : " & arr(1))
   msgbox("Value stored in Array index 2 : " & arr(2))
   msgbox("Value stored in Array index 3 : " & arr(3))
   msgbox("Value stored in Array index 4 : " & arr(4))
   msgbox("Value stored in Array index 5 : " & arr(5))
End Sub
When you execute the above function, it produces the following output.
Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM

Multi-Dimensional Arrays

Arrays are not just limited to a single dimension, however, they can have a maximum of 60 dimensions. Two-dimensional arrays are the most commonly used ones.

Example

In the following example, a multi-dimensional array is declared with 3 rows and 4 columns.
Private Sub Constant_demo_Click()
   Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
   arr(0,0) = "Apple" 
   arr(0,1) = "Orange"
   arr(0,2) = "Grapes"           
   arr(0,3) = "pineapple" 
   arr(1,0) = "cucumber"           
   arr(1,1) = "beans"           
   arr(1,2) = "carrot"           
   arr(1,3) = "tomato"           
   arr(2,0) = "potato"             
   arr(2,1) = "sandwitch"            
   arr(2,2) = "coffee"             
   arr(2,3) = "nuts"            
           
   msgbox("Value in Array index 0,1 : " &  arr(0,1))
   msgbox("Value in Array index 2,2 : " &  arr(2,2))
End Sub
When you execute the above function, it produces the following output.
Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee

ReDim Statement

ReDim statement is used to declare dynamic-array variables and allocate or reallocate storage space.

Syntax

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

Parameter Description

  • Preserve − An optional parameter used to preserve the data in an existing array when you change the size of the last dimension.
  • Varname − A required parameter, which denotes the name of the variable, which should follow the standard variable naming conventions.
  • Subscripts − A required parameter, which indicates the size of the array.

Example

In the following example, an array has been redefined and then the values preserved when the existing size of the array is changed.
Note − Upon resizing an array smaller than it was originally, the data in the eliminated elements will be lost.
Private Sub Constant_demo_Click()
   Dim a() as variant
   i = 0
   redim a(5)
   a(0) = "XYZ"
   a(1) = 41.25
   a(2) = 22
  
   REDIM PRESERVE a(7)
   For i = 3 to 7
   a(i) = i
   Next
  
   'to Fetch the output
   For i = 0 to ubound(a)
      Msgbox a(i)
   Next
End Sub
When you execute the above function, it produces the following output.
XYZ
41.25
22
3
4
5
6
7

Array Methods

There are various inbuilt functions within VBScript which help the developers to handle arrays effectively. All the methods that are used in conjunction with arrays are listed below. Please click on the method name to know about it in detail.
Sr.No.Function & Description
1LBound
A Function, which returns an integer that corresponds to the smallest subscript of the given arrays.
2UBound
A Function, which returns an integer that corresponds to the largest subscript of the given arrays.
3Split
A Function, which returns an array that contains a specified number of values. Split based on a delimiter.
4Join
A Function, which returns a string that contains a specified number of substrings in an array. This is an exact opposite function of Split Method.
5Filter
A Function, which returns a zero based array that contains a subset of a string array based on a specific filter criteria.
6IsArray
A Function, which returns a boolean value that indicates whether or not the input variable is an array.
7Erase
A Function, which recovers the allocated memory for the array variables.

VBA - LBound Function

The LBound Function returns the smallest subscript of the specified array. Hence, LBound of an array is ZERO.

Syntax

LBound(ArrayName[,dimension])

Parameter Description

  • ArrayName − A required parameter. This parameter corresponds to the name of the array.
  • Dimension − An optional parameter. This takes an integer value that corresponds to the dimension of the array. If it is '1', then it returns the lower bound of the first dimension; if it is '2', then it returns the lower bound of the second dimension and so on.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim arr(5) as Variant
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript     'String
   arr(2) = 100           'Number
   arr(3) = 2.45          'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
   msgbox("The smallest Subscript value of  the given array is : " & LBound(arr))

   ' For MultiDimension Arrays :
   Dim arr2(3,2) as Variant
   msgbox("The smallest Subscript of the first dimension of arr2 is : " & LBound(arr2,1))
   msgbox("The smallest Subscript of the Second dimension of arr2 is : " & LBound(arr2,2))
End Sub
When you execute the above function, it produces the following output.
The smallest Subscript value of the given array is : 0
The smallest Subscript of the first dimension of arr2 is : 0
The smallest Subscript of the Second dimension of arr2 is : 0

VBA - UBound Function

The UBound Function returns the largest subscript of the specified array. Hence, this value corresponds to the size of the array.

Syntax

UBound(ArrayName[,dimension])

Parameter Description

  • ArrayName − A required parameter. This parameter corresponds to the name of the array.
  • Dimension − An optional parameter. This takes an integer value that corresponds to the dimension of the array. If it is '1', then it returns the lower bound of the first dimension; if it is '2', then it returns the lower bound of the second dimension, and so on.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim arr(5) as Variant
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript     'String
   arr(2) = 100           'Number
   arr(3) = 2.45          'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
   msgbox("The smallest Subscript value of  the given array is : " & UBound(arr))

   ' For MultiDimension Arrays :
   Dim arr2(3,2) as Variant
   msgbox("The smallest Subscript of the first dimension of arr2 is : " & UBound(arr2,1))
   msgbox("The smallest Subscript of the Second dimension of arr2 is : " & UBound(arr2,2))
End Sub
When you execute the above function, it produces the following output.
The smallest Subscript value of the given array is : 5
The smallest Subscript of the first dimension of arr2 is : 3
The smallest Subscript of the Second dimension of arr2 is : 2

VBA - Split Function

A Split Function returns an array that contains a specific number of values split based on a delimiter.

Syntax

Split(expression[,delimiter[,count[,compare]]]) 

Parameter Description

  • Expression − A required parameter. The string expression that can contain strings with delimiters.
  • Delimiter − An optional parameter. The parameter, which is used to convert into arrays based on a delimiter.
  • Count − An optional parameter. The number of substrings to be returned, and if specified as -1, then all the substrings are returned.
  • Compare − An optional parameter. This parameter specifies which comparison method is to be used.
    • 0 = vbBinaryCompare - Performs a binary comparison
    • 1 = vbTextCompare - Performs a textual comparison

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   ' Splitting based on delimiter comma '$'
   Dim a as Variant
   Dim b as Variant
   
   a = Split("Red $ Blue $ Yellow","$")
   b = ubound(a)
   
   For i = 0 to b
      msgbox("The value of array in " & i & " is :"  & a(i))
   Next
End Sub
When you execute the above function, it produces the following output.
The value of array in 0 is :Red 
The value of array in 1 is : Blue 
The value of array in 2 is : Yellow

VBA - Join Function

A Function, which returns a string that contains a specified number of substrings in an array. This is an exact opposite function of Split Method.

Syntax

Join(List[,delimiter]) 

Parameter Description

  • List − A required parameter. An array that contains the substrings that are to be joined.
  • Delimiter − An optional parameter. The character, which used as a delimiter while returning the string. The default delimiter is Space.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   ' Join using spaces
   a = array("Red","Blue","Yellow")
   b = join(a)
   msgbox("The value of b " & " is :"  & b)
  
   ' Join using $
   b = join(a,"$")
   msgbox("The Join result after using delimiter is : " & b)
End Sub
When you execute the above function, it produces the following output.
The value of b is :Red Blue Yellow
The Join result after using delimiter is : Red$Blue$Yellow

VBA - Filter Function

A Filter Function, which returns a zero-based array that contains a subset of a string array based on a specific filter criteria.

Syntax

Filter(inputstrings,value[,include[,compare]]) 

Parameter Description

  • Inputstrings − A required parameter. This parameter corresponds to the array of strings to be searched.
  • Value − A required parameter. This parameter corresponds to the string to search for against the inputstrings parameter.
  • Include − An optional parameter. This is a Boolean value, which indicates whether or not to return the substrings that include or exclude.
  • Compare − An optional parameter. This parameter describes which string comparison method is to be used.
    • 0 = vbBinaryCompare - Performs a binary comparison
    • 1 = vbTextCompare - Performs a textual comparison

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim a,b,c,d as Variant
   a = array("Red","Blue","Yellow")
   b = Filter(a,"B")
   c = Filter(a,"e")
   d = Filter(a,"Y")
  
   For each x in b
      msgbox("The Filter result 1: " & x)
   Next
  
   For each y in c
      msgbox("The Filter result 2: " & y)
   Next
  
   For each z in d
      msgbox("The Filter result 3: " & z)
   Next
End Sub
When you execute the above function, it produces the following output.
The Filter result 1: Blue
The Filter result 2: Red
The Filter result 2: Blue
The Filter result 2: Yellow
The Filter result 3: Yellow

VBA - IsArray Function

The IsArray Function returns a boolean value that indicates whether or NOT the specified input variable is an array variable.

Syntax

IsArray(variablename)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim a,b as Variant
   a = array("Red","Blue","Yellow")
   b = "12345"
  
   msgbox("The IsArray result 1 : " & IsArray(a))
   msgbox("The IsArray result 2 : " & IsArray(b))
End Sub
When you execute the above function, it produces the following output.
The IsArray result 1 : True
The IsArray result 2 : False

VBA - Erase Function

The Erase Function is used to reset the values of fixed size arrays and free the memory of the dynamic arrays. It behaves depending upon the type of the arrays.

Syntax

Erase ArrayName
  • Fixed numeric array, each element in an array is reset to Zero.
  • Fixed string array, each element in an array is reset to Zero length " ".
  • Array of objects, each element in an array is reset to special value Nothing.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim NumArray(3)
   NumArray(0) = "VBScript"
   NumArray(1) = 1.05
   NumArray(2) = 25
   NumArray(3) = #23/04/2013#
  
   Dim DynamicArray()
   ReDim DynamicArray(9)   ' Allocate storage space.
  
   Erase NumArray          ' Each element is reinitialized.
   Erase DynamicArray      ' Free memory used by array.
  
   ' All values would be erased.
   msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
   msgbox("The value at First index of NumArray is " & NumArray(1))
   msgbox("The value at Second index of NumArray is " & NumArray(2))
   msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub
When you execute the above function, it produces the following output.
The value at Zeroth index of NumArray is 
The value at First index of NumArray is 
The value at Second index of NumArray is 
The value at Third index of NumArray is 

Comments

Blogger
Name

.NET_Interview,1,Accenture,1,Accenture News,1,Accenture_GFT,1,Accenture_Prep,1,Advance_Excel,22,Advance_Python,10,Advanced_Linux,6,Advanced_SQL,18,Advanced_Unix,6,AI,1,Alexa,1,Amazon,1,Amazon News,5,AMCAT,1,AMCAT_Prep,1,AMCAT_Solved_Papers,1,Ancient India,5,Android,1,Ansible,2,Apache_Sqoop,10,Aptitude,1,AWS,23,AWS CLI,6,AWS DeepRacer,1,AWS Tutorials,13,AWS_Dumps,1,AWS_Interview,1,AZ-104,1,AZ-900,2,Azure,3,Azure Administrator Associate,1,B_Tech,19,B.Tech,1,B.Tech Jobs,1,Backup,2,Banking Exam,1,Banking_Exam,1,Basic_Linux,29,Basic_Python,19,Basic_SQL,24,Basic_Unix,30,Best_Websites,1,Big_Data_Analytics,70,Blog,447,Blogger,3,Blogging,2,Blogspot,1,Books,2,BTech,20,C++_Interview,1,CBSE,93,Certification,5,Cheat Sheet,2,Civil_1st_Semester,1,Class 11,2,Class 11 Physics,2,Class 12,61,Class 12 Biology,16,Class 12 Chemistry,16,Class 12 English,14,Class 12 Physics,15,Class_12,28,Class_12_Chemistry,4,Class_12_Computer_Science,7,Class_12_Mathematics,1,Class_12_NCERT,15,Class_12_NCERT_Solutions,15,Class_12_Physics,18,Class_12_Physics_NCERT_Solutions,15,Class_12_Science,29,Cloud,2,Cloud_Service,1,CloudFormation,2,Coding,1,Cognizant News,1,Communication,2,Computer,18,Computer_Memory,2,Computer_Programming,2,Computer_Science,4,Control_System,9,Crack_Interview,3,CSE_5th_Semester,1,CSS,1,Data Analyst Jobs,1,Data Science,2,Data Science Interview,1,Data_Analytics,16,Data_Science,18,Data_Science_Interview,1,Database,47,Database Interview,2,Database_Interview,4,DP-900,1,Dumps,2,ECE_1st_Semester,1,ECE_1st_Year,1,ECE_4th_Semester,9,Electrical,2,Electrical_1st_Semester,1,Electronics,2,Electronics & Communication,1,Electronics_&_Communication,14,English,2,Error,1,Ethical Hacking,2,Ethical_Hacking,1,ETL_Tools,17,Exam Dumps,1,Exam Preparation,13,Exam_Cracker,3,Exams,5,Exams_Banking,1,Exams_Prep,1,Excel,22,Excel_Macros,22,Excel_Terms,1,Excel_VBA,22,File System,1,Free_OS,1,Free_Softwares,1,Games,3,GATE,20,GATE EC,1,GATE Electronics & Communication,1,GATE_2019,16,GATE_2020,16,GATE_2021,7,GATE_EC,10,GATE_ECE,9,GATE_ECE_Best_Book,1,GATE_Electrical,1,GATE_Electronics,12,GATE_Made_Easy,1,GATE_ME,6,GATE_Mechanical,6,GGSIPU,1,Google,1,Google Cloud,2,Google News,2,Government Jobs,2,Graphic,1,GRUB,1,Handwritten_Notes,10,Hardware,6,HCL_Prep,1,HDFS,1,Hive,1,Hive Tutorials,1,Hosting,2,How To,15,How_To,8,HR Interview,2,HR Interview Questions,1,HR_Interview,3,Hyderabad_News,1,IBPS,2,IBPS_English,1,IBPS_PO,2,Indian History,5,Informatica,1,Informatica_Interview,1,Information,20,Internet,5,Interview,20,Interview Preparation,5,Interview_Prep,20,IPU,1,ISRO Jobs,1,IT Jobs,4,IT News,1,Java,2,Java Interview,1,Java_Interview,2,Java_Questions_&_answers,1,JavaScript,1,JEE,6,JEE_Mains,6,Job Alert,6,Jobs,1,Kali Linux,1,Kali Linux Tools,1,Katoolin,1,Keyboard,1,Keyboard_Shortcuts,1,Learn_VBA,22,Linux,42,Linux Command,1,Linux Interview,1,Linux Mint,1,Linux Tools,4,Linux Tutorials,1,Linux_Distributions,1,Linux_Interview,1,Linux_Redirections,1,Linux_Scripting,30,Linux_Shell_Arrays,1,Linux_Shell_Functions,1,Linux_Shell_Quote,1,Linux_Signals_And_Traps,1,Logical_Reasoning,1,M.Tech Jobs,1,Machine Learning,1,Machine Learning Interview,1,Machine_Learning,1,Machine_Learning_Interview,1,Macros,22,Manufacturing_Processes,1,ME_1st_Semester,1,ME_Fluid_Mechanics,1,ME_Industrial_Engineering,1,ME_Machine_Design,1,Mechanical,6,Memory,1,Microcontroller,1,Microsoft,5,Microsoft Azure Associate,1,Microsoft Azure Fundamentals,3,Microsoft_Azure,1,Microsoft_Azure_Interview,1,Mobile,1,Mobile News,1,MS_Access,40,MySQL,44,NCERT Solutions,63,Network,2,News,3,Notes,31,OOPs,1,Open_Source_OS,1,OpenTelemetry,1,Operating_Systems,2,Operating_Systems_Interview,1,Oracle,42,Oracle Interview,1,Oracle_Interview,1,Paytm Jobs,1,Physics,2,PL_SQL,42,PL_SQL_Interview,1,Placement,21,Placement Preparation,18,Placement_Prep,24,Poetry,1,Programming,28,Programming_Languages,1,Python,28,Python_Built_In_Strings_Methods,2,Python_built_In_Tuple_Functions,1,Python_CAlling_a_Function,1,Python_CGI,1,Python_Class,1,Python_Data_Types,1,Python_DAte,1,Python_Decision_Making,1,Python_Dictionary,1,Python_DOM_APIs,1,Python_Features,1,Python_Files_Functions,1,Python_For_Loop,1,Python_Functions,6,Python_GUI,1,Python_History,1,Python_If_Else,1,Python_import_Statements,1,Python_Installation,1,Python_Interview,1,Python_JPython,1,Python_Lists,2,Python_Loops,1,Python_Methods,1,Python_Modules,1,Python_MySQL,1,Python_Nested_If_Else,1,Python_Nested_Loops,1,Python_Number_Type_Conversion,1,Python_Numbers,2,Python_Object_Oriented,1,Python_OOP,1,Python_Pass_By_Reference_vs_Value,1,Python_Programming,28,Python_Scripting,28,Python_Special_Operators,1,Python_Strings,2,Python_Strings_Functions,1,Python_Threading_Module,1,Python_Time,1,Python_Tkinter,1,Python_Tuples,2,Python_Tutorial,28,Python_Types_of_Loops,1,Python_Variables,1,Python_Web_Server,1,Python_While_Loop,1,Python_wxPython,1,Python_XML_Processing,1,PythonPath_Setup,1,Quantitative,1,Quantitative_Aptitude,2,RDBMS,1,Run,1,S3,1,Sabrent,1,Samsung,1,SBI Jobs,1,Scripting,52,Scripting Interview,1,Security,1,Server,2,service now,1,Shell_Command_Manual,1,Shell_Logging_Commands,1,Shell_Scripting,31,Shell_Scripting_Interview,1,Software Engineering Interview,1,Software_Engineering_Interview,1,Solutions,1,Spinnaker,1,SQL,46,SQL Interview,1,SQL_Alias_Syntax,1,SQL_Alter_Table_Query,1,SQL_Alter_Table_Statement,1,SQL_AND_OR_Query,1,SQL_AND_OR_Statement,1,SQL_Architecture,1,SQL_Clone_Table,1,SQL_Commands,1,SQL_Conjunctive_Operators,1,SQL_Constraints,1,SQL_Create_Database,1,SQL_Create_Table,1,SQL_DataTypes,1,SQL_Date_Functions,1,SQL_Date_Statement,1,SQL_DCL,1,SQL_Server,39,SQL_Temporary_Table_Statement,1,SQLite,43,Sqoop,9,Sqoop_Tutorial,10,SSC,4,SSC CGL,1,SSC CHSL,1,SSC_CGL,3,SSC_CGL_English,1,SSC_CHSL,1,SSC_CPO,1,SSC_GS,1,SSC_Quantative,1,SSD,1,ssl,1,Storage,1,Talend,17,Talend Interview,1,Talend_ETL,15,Talend_Tutorials,16,TCS Interview,1,TCS Jobs,1,TCS News,1,TCS_Interview,1,TCS_Prep,1,Tech News,15,Tech Tips,18,Teradata_Interview,1,Terraform,2,Tips & Tricks,12,Tips_&_Tricks,18,Top 10,3,Top 50,10,Top_10,1,Top_50,18,Top25,1,Tutorials,165,Tutorials_Python,28,Tutorials_VBA,11,Unix,32,Unix Interview,1,Unix Tutorials,1,Unix_Interview,1,Unix_Scripting,31,UPSC,5,VBA,22,VBA_Basics,22,VBA_Excel,22,VBA_Scripting,22,VBA_Tutorials,22,VirtualBox,1,Visual_Basic_Application,22,VPN,1,Websites,3,Windows,22,WordPress,2,Yarn,1,
ltr
item
Shout4Education - Get Jobs, Tutorials and Notes: VBA - Arrays
VBA - Arrays
VBA - Arrays - VBA Tutorials ... Best VBA Tutorials only @ Shout4Education ... Learn VBA in an Easy and Simplified Way ... Keep Shouting For Education and Keep Learning ... VBA - Arrays Shout4Education We know very well that a variable is a container to store a value. Sometimes, developers are in a position to hold more than one value in a single variable at a time. When a series of values are stored in a single variable, then it is known as an array variable. Array Declaration Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses parenthesis. In the following example, the size of the array is mentioned in the brackets. 'Method 1 : Using Dim Dim arr1() 'Without Size 'Method 2 : Mentioning the Size Dim arr2(5) 'Declared with size of 5 'Method 3 : using 'Array' Parameter Dim arr3 arr3 = Array("apple","Orange","Grapes") Although, the array size is indicated as 5, it can hold 6 values as array index starts from ZERO. Array Index cannot be negative. VBScript Arrays can store any type of variable in an array. Hence, an array can store an integer, string, or characters in a single array variable. Assigning Values to an Array The values are assigned to the array by specifying an array index value against each one of the values to be assigned. It can be a string. Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim arr(5) arr(0) = "1" 'Number as String arr(1) = "VBScript" 'String arr(2) = 100 'Number arr(3) = 2.45 'Decimal Number arr(4) = #10/07/2013# 'Date arr(5) = #12.45 PM# 'Time msgbox("Value stored in Array index 0 : " & arr(0)) msgbox("Value stored in Array index 1 : " & arr(1)) msgbox("Value stored in Array index 2 : " & arr(2)) msgbox("Value stored in Array index 3 : " & arr(3)) msgbox("Value stored in Array index 4 : " & arr(4)) msgbox("Value stored in Array index 5 : " & arr(5)) End Sub When you execute the above function, it produces the following output. Value stored in Array index 0 : 1 Value stored in Array index 1 : VBScript Value stored in Array index 2 : 100 Value stored in Array index 3 : 2.45 Value stored in Array index 4 : 7/10/2013 Value stored in Array index 5 : 12:45:00 PM Multi-Dimensional Arrays Arrays are not just limited to a single dimension, however, they can have a maximum of 60 dimensions. Two-dimensional arrays are the most commonly used ones. Example In the following example, a multi-dimensional array is declared with 3 rows and 4 columns. Private Sub Constant_demo_Click() Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns arr(0,0) = "Apple" arr(0,1) = "Orange" arr(0,2) = "Grapes" arr(0,3) = "pineapple" arr(1,0) = "cucumber" arr(1,1) = "beans" arr(1,2) = "carrot" arr(1,3) = "tomato" arr(2,0) = "potato" arr(2,1) = "sandwitch" arr(2,2) = "coffee" arr(2,3) = "nuts" msgbox("Value in Array index 0,1 : " & arr(0,1)) msgbox("Value in Array index 2,2 : " & arr(2,2)) End Sub When you execute the above function, it produces the following output. Value stored in Array index : 0 , 1 : Orange Value stored in Array index : 2 , 2 : coffee ReDim Statement ReDim statement is used to declare dynamic-array variables and allocate or reallocate storage space. Syntax ReDim [Preserve] varname(subscripts) [, varname(subscripts)] Parameter Description Preserve − An optional parameter used to preserve the data in an existing array when you change the size of the last dimension. Varname − A required parameter, which denotes the name of the variable, which should follow the standard variable naming conventions. Subscripts − A required parameter, which indicates the size of the array. Example In the following example, an array has been redefined and then the values preserved when the existing size of the array is changed. Note − Upon resizing an array smaller than it was originally, the data in the eliminated elements will be lost. Private Sub Constant_demo_Click() Dim a() as variant i = 0 redim a(5) a(0) = "XYZ" a(1) = 41.25 a(2) = 22 REDIM PRESERVE a(7) For i = 3 to 7 a(i) = i Next 'to Fetch the output For i = 0 to ubound(a) Msgbox a(i) Next End Sub When you execute the above function, it produces the following output. XYZ 41.25 22 3 4 5 6 7 Array Methods There are various inbuilt functions within VBScript which help the developers to handle arrays effectively. All the methods that are used in conjunction with arrays are listed below. Please click on the method name to know about it in detail. Sr.No. Function & Description 1 LBound A Function, which returns an integer that corresponds to the smallest subscript of the given arrays. 2 UBound A Function, which returns an integer that corresponds to the largest subscript of the given arrays. 3 Split A Function, which returns an array that contains a specified number of values. Split based on a delimiter. 4 Join A Function, which returns a string that contains a specified number of substrings in an array. This is an exact opposite function of Split Method. 5 Filter A Function, which returns a zero based array that contains a subset of a string array based on a specific filter criteria. 6 IsArray A Function, which returns a boolean value that indicates whether or not the input variable is an array. 7 Erase A Function, which recovers the allocated memory for the array variables. VBA - LBound Function The LBound Function returns the smallest subscript of the specified array. Hence, LBound of an array is ZERO. Syntax LBound(ArrayName[,dimension]) Parameter Description ArrayName − A required parameter. This parameter corresponds to the name of the array. Dimension − An optional parameter. This takes an integer value that corresponds to the dimension of the array. If it is '1', then it returns the lower bound of the first dimension; if it is '2', then it returns the lower bound of the second dimension and so on. Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim arr(5) as Variant arr(0) = "1" 'Number as String arr(1) = "VBScript 'String arr(2) = 100 'Number arr(3) = 2.45 'Decimal Number arr(4) = #10/07/2013# 'Date arr(5) = #12.45 PM# 'Time msgbox("The smallest Subscript value of the given array is : " & LBound(arr)) ' For MultiDimension Arrays : Dim arr2(3,2) as Variant msgbox("The smallest Subscript of the first dimension of arr2 is : " & LBound(arr2,1)) msgbox("The smallest Subscript of the Second dimension of arr2 is : " & LBound(arr2,2)) End Sub When you execute the above function, it produces the following output. The smallest Subscript value of the given array is : 0 The smallest Subscript of the first dimension of arr2 is : 0 The smallest Subscript of the Second dimension of arr2 is : 0 VBA - UBound Function The UBound Function returns the largest subscript of the specified array. Hence, this value corresponds to the size of the array. Syntax UBound(ArrayName[,dimension]) Parameter Description ArrayName − A required parameter. This parameter corresponds to the name of the array. Dimension − An optional parameter. This takes an integer value that corresponds to the dimension of the array. If it is '1', then it returns the lower bound of the first dimension; if it is '2', then it returns the lower bound of the second dimension, and so on. Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim arr(5) as Variant arr(0) = "1" 'Number as String arr(1) = "VBScript 'String arr(2) = 100 'Number arr(3) = 2.45 'Decimal Number arr(4) = #10/07/2013# 'Date arr(5) = #12.45 PM# 'Time msgbox("The smallest Subscript value of the given array is : " & UBound(arr)) ' For MultiDimension Arrays : Dim arr2(3,2) as Variant msgbox("The smallest Subscript of the first dimension of arr2 is : " & UBound(arr2,1)) msgbox("The smallest Subscript of the Second dimension of arr2 is : " & UBound(arr2,2)) End Sub When you execute the above function, it produces the following output. The smallest Subscript value of the given array is : 5 The smallest Subscript of the first dimension of arr2 is : 3 The smallest Subscript of the Second dimension of arr2 is : 2 VBA - Split Function A Split Function returns an array that contains a specific number of values split based on a delimiter. Syntax Split(expression[,delimiter[,count[,compare]]]) Parameter Description Expression − A required parameter. The string expression that can contain strings with delimiters. Delimiter − An optional parameter. The parameter, which is used to convert into arrays based on a delimiter. Count − An optional parameter. The number of substrings to be returned, and if specified as -1, then all the substrings are returned. Compare − An optional parameter. This parameter specifies which comparison method is to be used. 0 = vbBinaryCompare - Performs a binary comparison 1 = vbTextCompare - Performs a textual comparison Example Add a button and add the following function. Private Sub Constant_demo_Click() ' Splitting based on delimiter comma '$' Dim a as Variant Dim b as Variant a = Split("Red $ Blue $ Yellow","$") b = ubound(a) For i = 0 to b msgbox("The value of array in " & i & " is :" & a(i)) Next End Sub When you execute the above function, it produces the following output. The value of array in 0 is :Red The value of array in 1 is : Blue The value of array in 2 is : Yellow VBA - Join Function A Function, which returns a string that contains a specified number of substrings in an array. This is an exact opposite function of Split Method. Syntax Join(List[,delimiter]) Parameter Description List − A required parameter. An array that contains the substrings that are to be joined. Delimiter − An optional parameter. The character, which used as a delimiter while returning the string. The default delimiter is Space. Example Add a button and add the following function. Private Sub Constant_demo_Click() ' Join using spaces a = array("Red","Blue","Yellow") b = join(a) msgbox("The value of b " & " is :" & b) ' Join using $ b = join(a,"$") msgbox("The Join result after using delimiter is : " & b) End Sub When you execute the above function, it produces the following output. The value of b is :Red Blue Yellow The Join result after using delimiter is : Red$Blue$Yellow VBA - Filter Function A Filter Function, which returns a zero-based array that contains a subset of a string array based on a specific filter criteria. Syntax Filter(inputstrings,value[,include[,compare]]) Parameter Description Inputstrings − A required parameter. This parameter corresponds to the array of strings to be searched. Value − A required parameter. This parameter corresponds to the string to search for against the inputstrings parameter. Include − An optional parameter. This is a Boolean value, which indicates whether or not to return the substrings that include or exclude. Compare − An optional parameter. This parameter describes which string comparison method is to be used. 0 = vbBinaryCompare - Performs a binary comparison 1 = vbTextCompare - Performs a textual comparison Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim a,b,c,d as Variant a = array("Red","Blue","Yellow") b = Filter(a,"B") c = Filter(a,"e") d = Filter(a,"Y") For each x in b msgbox("The Filter result 1: " & x) Next For each y in c msgbox("The Filter result 2: " & y) Next For each z in d msgbox("The Filter result 3: " & z) Next End Sub When you execute the above function, it produces the following output. The Filter result 1: Blue The Filter result 2: Red The Filter result 2: Blue The Filter result 2: Yellow The Filter result 3: Yellow VBA - IsArray Function The IsArray Function returns a boolean value that indicates whether or NOT the specified input variable is an array variable. Syntax IsArray(variablename) Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim a,b as Variant a = array("Red","Blue","Yellow") b = "12345" msgbox("The IsArray result 1 : " & IsArray(a)) msgbox("The IsArray result 2 : " & IsArray(b)) End Sub When you execute the above function, it produces the following output. The IsArray result 1 : True The IsArray result 2 : False VBA - Erase Function The Erase Function is used to reset the values of fixed size arrays and free the memory of the dynamic arrays. It behaves depending upon the type of the arrays. Syntax Erase ArrayName Fixed numeric array, each element in an array is reset to Zero. Fixed string array, each element in an array is reset to Zero length " ". Array of objects, each element in an array is reset to special value Nothing. Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim NumArray(3) NumArray(0) = "VBScript" NumArray(1) = 1.05 NumArray(2) = 25 NumArray(3) = #23/04/2013# Dim DynamicArray() ReDim DynamicArray(9) ' Allocate storage space. Erase NumArray ' Each element is reinitialized. Erase DynamicArray ' Free memory used by array. ' All values would be erased. msgbox("The value at Zeroth index of NumArray is " & NumArray(0)) msgbox("The value at First index of NumArray is " & NumArray(1)) msgbox("The value at Second index of NumArray is " & NumArray(2)) msgbox("The value at Third index of NumArray is " & NumArray(3)) End Sub When you execute the above function, it produces the following output. The value at Zeroth index of NumArray is The value at First index of NumArray is The value at Second index of NumArray is The value at Third index of NumArray is @ Shout 4 Education , @ Shout For Education
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s640/VBA_Tutorials_Shout4Education.jpg
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s72-c/VBA_Tutorials_Shout4Education.jpg
Shout4Education - Get Jobs, Tutorials and Notes
https://www.shout4education.com/2020/04/vba-arrays.html
https://www.shout4education.com/
https://www.shout4education.com/
https://www.shout4education.com/2020/04/vba-arrays.html
true
7947974353386595563
UTF-8
Loaded All Posts Not Found Any Posts :( View All Read More Reply Cancel Reply Delete By Home Pages Posts View All Similar Posts Label Archive Search All Posts Not Found Any Post Match with Your Request Sorry !! Search Something Blazing :) Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Just Now 1 Minute Ago $$1$$ minutes ago 1 Hour Ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago More than 5 Weeks Ago Followers Follow :) This Premium Content is LOCKED !!! STEP 1: Share. STEP 2: Click the Link You Shared to Unlock Copy All Code Select All Code All Codes were Copied to Your Clipboard :) Can NOT Copy the Codes / Texts, Please Press [CTRL]+[C] (or CMD+C with Mac) to Copy