State about ARRAY

An array is the collection of related variables

How arrow works?

First define the array
Insert the values into array
Retrieve values from the array

How many ways we create Arrays?

We can define array in two ways:

(i) By declaring variable using DIM
(ii) Using Array Function

How to declare an ARRAY

dim Varname(bounds) as type

Varname

Indicates variable name to store the array

Bounds

Denotes about Lower range to Upper limit of array
Also termed as Lower Bound to Upper Bound
While declaring the variable Lower Bound is optional

Type

It denotes about the data type: i.e: Integer,double...

Dim cnt(5) as Variant

Dim cnt(5) as Integer

Dim cnt(5) as String

Defining Arrary Name

Data Type = Variant

Based on mentioned data type we can insert the valeus into array
If we assign STRING: array allows the user to insert only STRINGS
If we assign INTEGER: array allows the user to insert only INTEGERS
If we assign VARIANT: array allows the user to insert any category of data type

Note: If we didn't mention the data type application by default considers VARIANT as Data Type

We can use DIM (DIMENSION) to define size of array
Array begins with the INDEX of 0(Zero)

State about OPTION BASE

OPTION BASE statement denotes whether array's element count starts from ZERO (or) ONE

Option base should mention at the top of the module

Optionbase 0(zero) is default

Create an array using Option base 0 and 1

Optionbase 1 represents to array begings from 1

Download The Workbook

Classification of ARRAYs

VBA allows the used to create upto 60 Dimensions

1)Single Dimensional Array:

Single Dimentional array can be either direction of HORIZONTAL (or) VERTICAL

Single dimensional array is also termed as LISTS

2)Two dimensional Array

It is in two directions(Horizontal + Vertical)

It is termed as TABLE Array

3)Multi Dimensional Array(upto 60)

Classification of ARRAYs based on memory size

Static

Elements of the arry doesn't change, till the annual of variable

Dynamic Array

We use dynamic array, when size of array doesn't know

Size of array changes based on available list

Ex: Sheets in a workbook, files in a folder... all these are unknown unless we manullay count then

Change happens in elemnts it may increase\decrease

Use REDIM to change the size of array

Redim array(1 to 6)

If we change Each and every the elements size, existing elements will nullify

Hence PRESERVE is a keyword veryuseful to retain existing elements

Redim preserve arry(1 to 11)

Array - retrieve vlaues

Private Sub CommandButton1_Click()
Dim j(1 To 7) As Variant
For i = 1 To 7
'first move the values into arrary
j(i) = Cells(i, 1).Value
'retrieve the values from the arrary
Cells(i, 3).Value = j(i)
Next
End Sub

Download The Workbook

Selected file Names

Lists the file names

Private Sub CommandButton1_Click()
'Opens dialogue box
fileopen = Application.GetOpenFilename(MultiSelect:=True)
'Loop runs based on bound values
For i = LBound(fileopen) To UBound(fileopen)
'file name will print in cells
Cells(i, 1).Value = fileopen(i)
Next
End Sub

Download The Workbook

Copy values through Range Bound

Click YES to copy values

Private Sub CommandButton1_Click()
Dim sriguru(1 To 5) As String
sriguru(1) = "apple"
sriguru(2) = "banana"
sriguru(3) = "orange"
sriguru(4) = "Eraser"
sriguru(5) = "Slate"
Dim i As Integer
'For i = 1 To 5
For i = LBound(sriguru) To UBound(sriguru)
output = MsgBox("mention the result", vbYesNoCancel + vbDefaultButton2 + vbQuestion)
If output = vbYes Then
Range("A" & i).Value = sriguru(i)
End If
Next
End Sub

Download The Workbook

Hide multiplesheets using array

Count the Words

Function wordscount() As Long
wordscount = UBound(Split(Range("A1"), " ")) + 1
End Function

Download The Workbook

Copy entire array into worksheet

SH.Range("I5").Resize(UBound(SheetName, 1), 1).Value = SheetName

SheetName = array name