×

Excel VBA Array Function

VBA Array Function: The Array function in VBA generates an array containing the given set of values.

Syntax

Array (Arglist)

Parameter

Arglist (required) – This parameter the list of values that you want to make up the array wherein the values are separated by commas.

Return

This function returns an array containing the given set of values.

Example 1

Sub ArrayFunction_Function1()
 'declaring the array
 Dim names
 'inserting values in the array
 names = Array("Alan", "Christopher", "Elmer", "Frank")
 'running a loop to print the array
 For i = 0 To 3
     Cells(i + 2, 1).Value = names(i)
 Next
 End Sub 

Output

Name
Alan
Christopher
Elmer
Frank
VBA Array Function

Example 2

Sub ArrayFunction_Example2()
 'declaring the array variables
 Dim Index
 Dim names
 'inserting values in the array for column 1
 names = Array("Reema", "Varun", "Amar", "Shukla", "Pradeep", "Govind", "Shradha", "Sourav", "Kumar", "Twinkle")
 'inserting values in the array for column 2
 Index = Array("15023027", "16023014", "15023090", "15023012", "16023021", "16023087", "16023114", "16023123", "15023019", "15023456")
 'running a loop to print the Array
 For i = 0 To 9
     Cells(i + 2, 1).Value = names(i)
     Cells(i + 2, 2).Value = Index(i)
 NextR
 End Sub 

Output

Names Identity Number
Reema 15023027
Varun 16023014
Amar 15023090
Shukla 15023012
Pradeep 16023021
Govind 16023087
Shradha 16023114
Sourav 16023123
Kumar 15023019
Twinkle 15023456
VBA Array Function

Example 3

Sub ArrayFunction_Example3()
 'declaring the array
 Dim arry(5, 2)
 'running the loop to take data from user
 For i = 0 To 5
     IB = InputBox("Enter your First name")
     IB_sales = InputBox("Hey " + IB + "! Enter your sales amount")
     arry(i, 1) = IB
     arry(i, 2) = IB_sales
 Next
 'running a loop to print the array
 For i = 0 To 5
     For j = 1 To 2
         Cells(i + 2, j).Value = arry(i, j)
     Next
 Next
 End Sub 

Output        

VBA Array Function
VBA Array Function
VBA Array Function
VBA Array Function
VBA Array Function
VBA Array Function
Name Sales Amount
Reema 4567
Pradeep 9876
Rahul 12345
Rita 6789
Sunita 9876
Sheena 4356

Related Topics

Excel VBA Fix Function

VBA Fix Function: The Fix function in VBA truncates the given number to an integer and returns the rounded off integer number. This function both positive and negative numbers to zero. Syntax Fix...

1 minute read.

Excel VBA GoTo Statement

GoTo Statement he GoTo statement branches unconditionally to a specified line in a procedure. It is used to transfer the program control to a new statement, which is headed by a label. It sends...

2 minutes read.

Excel VBA Exp Function

VBA Exp Function: The Exp function in VBA returns the value of the exponential function ex (mathematical constant ‘e’ raised to specified power) for the given value of x. Syntax Exp (Number) Parameter Number (required) –This parameter represents...

1 minute read.

Excel VBA Tutorial

What is VBA? Introduction to Excel VBA: Visual Basic for Applications (VBA) is a programming language developed by Microsoft to automate operations in applications, such as Excel, Word, PowerPoint, etc. It...

5 minutes read.

VBA Pivot Table Grouping

VBA- Pivot Table Grouping For an instance, if in our pivot table, we have 11 different age groups from 20 to 30 -  but there might be a possibility that we...

2 minutes read.

UserForm and its Properties Excel VBA

UserForm and its Properties Userform has certain properties that can be viewed as category wise (based on appearance, behavior, font) or in an alphabetic manner. The property window is used to set or...

11 minutes read.

Excel VBA FormatCurrency Function

VBA FormatCurrency Function: The FormatCurrency function in VBA is used to apply a currency format to a numeric expression and returns the result as a string. Syntax FormatCurrency (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.

Excel VBA Time Function

Excel VBA Time Function: The Time function in VBA returns the current time. Syntax Time () Parameter NA Return This function returns the current time.  Example 1 Sub TimeFunction_Example1() ' returns the current time Dim time_val...

1 minute read.

Excel VBA Cos Function

VBA Cos Function: The Cos function in VBA returns the cosine value for a supplied angle. Syntax Cos (Number) Parameter Number (required) –This parameter represents the number that you want the absolute value of. Return This function...

1 minute read.

VBA UBound

VBA UBound  The UBound or Upper Bound function in VBA is used to specify the length of an array and returns the highest subscript for a dimension for the specified array. It is...

3 minutes read.

Basics of Userform

A User Form is a built-in customized dialog box that fetches data from the user through a user-friendly dialog box or window that makes up part of an application's user interface. It...

9 minutes read.

VBA ActiveSheet

What is VBA Activesheet Property? The active sheet means the current worksheet which you are working on and viewing. The ActiveSheet object signifies the worksheet tab that is selected before running the VBA...

5 minutes read.

Excel Objects in VBA

What are Excel Objects? The Excel objects belong to the entities that make up an Excel Workbook, Worksheets, Columns, Rows, Cell Ranges, etc. Each object in Excel has loads of Properties that are...

6 minutes read.

Excel VBA StrReverse Function

The StrReverse function returns a String after reversing the given String. Syntax StrReverse (Expression) Parameter Expression (required) – This parameter represents the String that you want to reverse. Return This function returns a String after reversing the given String. Example...

1 minute read.

Excel VBA Rnd Function

VBA Rnd Function: The Rnd function in VBA returns a random number that is greater than or equal to (>=) 0 and is less than (<) 1. Syntax Rnd ([Number]) Parameter Number (optional) –This...

1 minute read.

VBA Option Explicit

VBA Option Explicit The Option Explicit in VBA is used to declare the variables at the top of your macro code. It is the most secure and easy option to maintain your variables....

5 minutes read.

How to save Macro Workbook?

Saving Macro Workbook Default Excel File Extension The default excel file extension is “.xlsx”. But the standard file extension “.xlsx” cannot contains macros. So, the workbook contains macro when save din.xlsx file, all VBA...

1 minute read.

Excel VBA Filter Function

VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria. Syntax Filter (SourceArray, Match, [Include], [Compare]) Parameter SourceArray (required) – This parameter the array of Strings that you...

2 minutes read.

Excel VBA CStr Function

VBA CStr Function: The CStr function in VBA converts an expression into a string data type. Syntax CStr (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

Excel VBA Array Function

VBA Array Function: The Array function in VBA generates an array containing the given set of values. Syntax Array (Arglist) Parameter Arglist (required) – This parameter the list of values that you want to make...

2 minutes read.