Open In App

How to Declare and Initialize String Array in Excel VBA?

Last Updated: 11 Nov, 2022

A

Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

A string array is an array where we can store only string values in the array, with the help of a string array, we can store more than one string value. We can declare the string array in many ways  like declaring a static string array, declaring a variant size array of string using the Array function, and a string array using the split function which we will discuss in this article

Declaring a Static String Array

A static Array is an array whose size is fixed and it can be declared in two ways one is declared implicitly and another one is explicit.

  • The following code is to declare a string array implicitly.
Declaring-a-string-array-implicitly

 

  • The following code is to declare a string array explicitly.
Declaring-a-string-array-explicitly

 

Declaring a Variant Size Array of String using Array Function

In the following code an array is declared with variant size and string values are initialized using the array function:

Array-declaring-with-variant-size

 

If we want to access the strings in the array then we have to write,

Accessing-the-strings-in-array

 

Declaring a String Array using Split Function

The following code is to declare an array without any fixed size and a split function is used to assign the string values.

Declaring-array-without-fixed-size

 

If we want to access the strings in the array then we have to write,

Accessing-the-strings-in-array

 

By default, the lower bound of an array is 0, and the upper bound of an array is n.

Where,

  • lower bound is the lowest index of the array,
  • upper bound is the highest index of the array.


A

News
Improve
Discuss
Do you want to advertise with us?Click here to know more

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword whic
Read More

How to Get Length of Array in Excel VBA?

article_img
We use UBound and LBound functions to get the length of an Array in Excel VBA. In this article, we will discuss them in detail. Syntax: UBound() function UBound (arrayname, [ dimension ]) Parameters: arrayname: required. Array variable namedimension: optional Returns: Return upper limit of an array dimension. Syntax: LBound() Function LBound (arrayname, [ dimension ]) Parameters: arrayname : required. Array variable namedimension : optional Returns: Return lower limit of an array dimension Sample Data: VBA Code to get the length of Array (one-dimensional array): Declare Variables: Declaring a customer array with the size of 10. Sub oneDimArrayLength() ' Array variable Declaration Dim customer (1 To 10) As String Assign values to array elements customer(1) = "ANTON" customer(2) = "BERGS" customer(3) = "BOLID" customer(4) = "KOENE" customer(5) = "FRANS" Use UBound function to get the size of an array and Message box to display the result 'Message box to popup length of 1D array MsgBox "Array has " & UBound(customer) & " element(s)." End Sub To Run VBA Code Press Alt+F8 to popup macro window. Select " oneDimArrayLength" and Click Run button. Output VBA Code to get the length
Read More

How to Remove Duplicates From Array Using VBA in Excel?

article_img
Excel VBA code to remove duplicates from a given range of cells. In the below data set we have given a list of 15 numbers in “Column A” range A1:A15. Need to remove duplicates and place unique numbers in column B. Sample Data: Cells A1:A15 Sample Data Final Output: VBA Code to remove duplicates and place into next column (B) Declare Variables: VariablesData TypeCommentsnonDuplicateBooleanIt is a Boolean value (True/False).uNoIntegerCount no of Unique items in column BcolAIntegerIteration column A cellscolBIntegerIteration column B cells'Variable Declarations Dim nonDuplicate As Boolean, uNo As Integer, colA As Integer, colB As Integer Always first value will be unique, So A1 place to cell B1 'Place first value to B1 Cells(1, 2).Value = Cells(1, 1).Value Initialize variables: 'Initialize uNo = 1 since first number is already placed in column B; Assign True to the variable nonDuplicate uNo = 1 nonDuplicate= True Since the first number is already placed in cell B1, Loop starts from A2 to A15. Take each number from Column A and check with Column B (unique range) 'Use for loop to check each number from A2 to A15 For colA = 2 To 15 For colB = 1 To uNo if the number is already placed in
Read More

How to Convert VBA Collections to Array in Excel?

article_img
An object that can store a number of values whether it can be of string data type or integer which can be easily manipulated or iterated is called Collection. On the other hand, Array is also used to store the data but it is multidimensional but collections are single dimensions. Now, we will see how to convert the collection to an array for that we have to follow further steps: Converting Excel VBA Collection to Array Step 1: Press Alt + F11 to get the VBA box and select Insert and Module to write the code. Step 2: Define a sub-procedure in VBE. Step 3: Declare two variables one with the name "collection" as New Collection and another one with the name "arr" of size 3 type String. Step 4: Now, we will add items to the collection with the help of the "Add" keyword. Step 5: Adding items to the array from the collection using each loop. Step 6: Use MsgBox to print the elements in the array. Step 7: Press F5 to see the output.
Read More

Variables and Data Types in VBA Excel

article_img
In a computer system, variables and data types are almost used in every program to store and represent data. Similarly, Excel VBA also has variables and data types to store and represent data and its type. In this article, we will learn about VBA variables, their scope, data types, and much more. VBA Variables VBA(Visual Basic for Application) variables are similar to other programming languages variables, they act as a container that is used to store data(integer, string, floats, etc). We can use the variables in the code at multiple places and executer the programs. Defining Variables In VBA VBA gives permission to define variables in two ways: Implicitly - In VBA, we can implicitly declare variables using the assignment(=) operator. All the variables that are implicitly declared in VBA are of type "Variant". The variant type variables required more memory space than usual variables. Example: label="gfg"Explicitly - Explicitly we can declare variables using "Dim" keyword. Explicit variable also reduces the naming conflicts and spelling mistakes. Example: Num as password Syntax For VBA Variables // macro definition Sub VBA_Variable_Example () Dim <name> End Sub VBA V
Read More

Function and Sub in Excel VBA

article_img
In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-procedure generally does not return a result whereas functions tend to return a result. Hence if there is a need for having a value post execution of tasks, then place the VBA code under a function or otherwise place the code under a sub-procedure. In Excel, there is the availability of large numbers of VBA functions that could be utilized in the development of new VBA codes. Such functions are referred to as Built-in functions. With the increase in the size of a VBA program, both Functions and Sub-procedures play a crucial role in the management and performance of VBA code. Functions in VBA A function in VBA can be defined as a procedure that executes a piece of code or instructions and post-execution, it returns the value of the tasks performed. A function is hence invoked using a variable. Functions are directly called in the spreadsheets by using excel based formulas. An excel VBA functi
Read More

VBA Date and Time Functions in Excel

article_img
Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can manipulate it according to the need of the user. Date and Time functions are used to interconvert date and time in different formats. In this article, we will learn about the most commonly used date and time functions. VBA Date Functions There are fifteen-plus different date functions in VBA, but here we will talk about some of the most commonly used date functions. VBA Date Function The Date() function returns the current date. The Date() function does not require any arguments. For example, declare a variable name date_1 of Date data type, call the Date() function, and store the return value in date_1, then print the date_1 in the console. Syntax of the function: Date() VBA DateAdd Function The DateAdd() function is used to add an interval of date/time to the respective date or time. The function will return the resulting date or time. The function takes three arguments, Interval, Nu
Read More

How to Insert and Run VBA Code in Excel?

article_img
In Excel VBA stands for (Visual Basic for Application Code) where we can automate our task with help of codes and codes that will manipulate(like inserting, creating, or deleting a row, column, or graph) the data in a worksheet or workbook. With the help of VBA, we can also automate the task in excel to perform all these tasks we need to insert and run the VBA code properly which we will discuss in this article. Steps to Insert and Run VBA Code in Excel To use the VBA code properly in Excel we need to change the default macro security settings of excel for that we need to follow further steps Step 1: Click on the "File" menu at the left top of the excel tab. Step 2: Select "Options" to get the "Excel Options" window. Step 3: Select "Customized Ribbon" in the "Excel Options" Window and then select the "Developer" check box in the "Main Tabs". Step 4: Then return to the main Excel window to select the "Developer" ribbon and then click on "Macro Security" in the "Code" group. Step 5: Click on "Macro Settings" to select "Disable all macros except digitally signed macros". Now, to insert and run the VBA in Excel so that we can write codes we need to follow further steps: Ste
Read More

How to Find the Last Used Row and Column in Excel VBA?

article_img
We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Sample Data Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address VBA Code: Declaring Variables: VariableData TypeCommentsLastRowLongFind and store last used rowLastColLongstore last used columnLastCellStringstore last used cell address'Variable Declaration Dim LastRow As Long, LastCol As Long, LastCell As String Use SpecialCells function to find last used row/column/cell 'Find Last Used Row LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row 'Find Last Used Column LastCol = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column 'Find Last Used Cell LastCell = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10). Show the final output in an Excel Message box. 'Display the last used row/column/cell MsgBox "Last Used Row : " & LastRow & Chr(10) & "Last Used Column :
Read More

How to Create Charts in Excel Using Worksheet Data and VBA?

article_img
Excel is an important software provided by Microsoft Corporation. This software belongs to one of the major software suites Office 365. In this software suite, there are other software are present like Word, PowerPoint, etc. They are called Office 365, as this software are mostly used for office purpose. But now the world has changed a lot. After the Corona Pandemic, the world knows the positivity of using digital tools. Office 365 was not different from that. As a part of the software suite, Excel software also gains some importance from the users. They are not only used for official purposes. But they can also be used for school purposes. Excel is software that can able to store data in an effective form. So, searching for the data becomes more manageable in this software. Excel has another great feature. It can be used to derive the charts from the provided data. The charts are helpful for analyzing any growth of the data. If there are thousands of data present, it is a difficult task to extract some analysis from that data. But if those data are converted to charts, then it will be easy to analyze those data. Excel sheet helps to do the same. Charts can be prepared whatever the
Read More
three90RightbarBannerImg