EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home VBA VBA Resources VBA String and Text Functions VBA String Array

VBA String Array

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA String Array

Excel VBA String Array

When we have multiple variables to be declared in a VBA Code, we can declare the exact number of a variable with data type we want. But this process is quite lengthy when the variable count goes above 5. Why declare variables multiple times when we can frame that into String Array. A VBA String Array is used when we need to hold more than one string value with a string variable. This looks tricky but in reality, it is very easy to implement. We don’t have to declare one type of variable multiple times if each variable store’s different values. This slash in a huge VBA Code is done using Excel VBA String Array.

How to Use VBA String Array?

To use VBA String Array in any code, it is very simple. For this, we just need to define how many variables we will require. This will be first done using DIM. Suppose, if we want 10 variables of any data type so that could be done as shown below.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Code:

Sub VBA_StringArray()

Dim NameOfVariable(1 To 10) As DataType

End Sub

VBA String Array Examples

We can choose any name in place Name Of Variable and any data type in Data Type box as highlighted above.

Examples of String Array in Excel VBA

Below are the examples of an excel VBA string array.

You can download this VBA String Array Excel Template here – VBA String Array Excel Template

Example #1

In this example, we will see how to use String Array in VBA without any limit in variables. Here, we will not define the length of variables, instead, we will directly create string array and use the number of variables as we our need. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: Define the subprocedure preferably in the name of VBA String Array or we can choose any name as per our choice.

Code:

Sub VBA_StringArray1()

End Sub

VBA String Array Examples 1-1

Step 3: Now we will be using Employee names for creating the array. For this declare a variable in a similar name and put brackets “( )” after that. And choose any data type. We can choose String or Integer or Variant. But as the data may vary so using Variant will be good.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant

End Sub

Variant Examples 1-2

Step 4: Now use the same variable which we declared above and use Array function.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array(

End Sub

VBA String Array Examples 1-3

As we can see, as per the syntax of Array, it only allows Variant data type and Argument List (). The reason for seeing the Variant data type is because we can store any type of value in it.

Step 5: Now consider the names of employees which we will be using here. We have Anand, Shraddha, Aniket, Ashwani, and Deepinder as Employee names. And it should be in the way as we do concatenation.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array("Anand", "Shraddha", "Aniket", "Ashwani", "Deepinder")

End Sub

Employee names Examples 1-4

Step 6: And to print the values stored in the Employee Data array we will use MsgBox. And array will be in the sequence of numbers at which we have defined.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array("Anand", "Shraddha", "Aniket", "Ashwani", "Deepinder")
MsgBox EmployeeData(0) & ", " & EmployeeData(1) & ", " & EmployeeData(3) & ", " & EmployeeData(4)

End Sub

VBA String Array Examples 1-6

Step 7: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

Run button Example

Step 8: We will get the message box with all the employee names in the sequence we put it.

VBA String Array Examples 1-8

Step 9: Let’s try to change the sequence of Employee Data array. Here we have exchanged 0 and 4 with each other.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array("Anand", "Shraddha", "Aniket", "Ashwani", "Deepinder")
MsgBox EmployeeData(4) & ", " & EmployeeData(1) & ", " & EmployeeData(3) & ", " & EmployeeData(0)

End Sub

Employee names Examples 1-9

Step 10: Let’s run this code again. We will notice, Employee Data name Deepinder is now moved to first place and Anand is at 4th place.

VBA String Array Examples 1-6

Example #2

In this example, we will set the position of cells in the array and get the combination of output by that. For this, we will use the same employee names which we have seen in. For this, follow the below steps:

Step 1: Write the subprocedure.

Code:

Sub VBA_StringEmployeDataay2()

End Sub

VBA String Array Examples 2-1

Step 2: Define a variable as Variant with cell positioning as (1, 3) Which 1 shows the 2nd position.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant

End Sub

VBA String Array Examples 2-2

Step 3: Now we will assign each employee name to different co-ordinates. Such as, at 1st row, 2nd column we have set employee Anand.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"

End Sub

VBA String Array Examples 2-3

Step 4: Similarly we will choose different co-ordinates from (1, 3) position and give each employee name in a different position.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"

End Sub

VBA String Array Examples 2-4

Now to get the output from the defined array, we will use the message box.

Step 5: We have used the position of co-ordinates. Such as for (0, 1).

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"
MsgBox ("EmployeData In Index 0,1 : " & EmployeData(0, 1))

End Sub

VBA String Array Examples 2-5

Step 6: Similarly, another message box to see other values stored in different co-ordinates.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"
MsgBox ("EmployeData In Index 0,1 : " & EmployeData(0, 1))
MsgBox ("EmployeData In Index 1,2 : " & EmployeData(1, 2))

End Sub

VBA String Array Examples 2-6

Step 7: Once done, compile the code by hitting the F8 or Run button. We will see, the values stored in the array (0, 1) is Anand.

Message Box Examples 2-7

Step 8: And the second array (1, 2) stores the value as Aniket.

Message Box Examples 2-8

This is how co-ordinating in String array works.

Step 9: What if we change the array co-ordinates for the second message box from (1, 2) to (2, 2).

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"
MsgBox ("EmployeData In Index 0,1 : " & EmployeData(0, 1))
MsgBox ("EmployeData In Index 1,2 : " & EmployeData(2, 2))

End Sub

VBA String Array Examples 2-9

Step 10: We will see, once the first array message box shows the value, the second message box will give the error, as Subscript Out Of range. Which means, we have selected the range which either incorrect or not exists.

VBA String Array Examples 2-10

Pros of VBA String Array:

  • VBA String Array can hold any type of value.
  • There is no limit to storage capacity in String Array.

Things to Remember

  • We can create 2D and 3D String array both.
  • Called value of array should be in the range of defined values.
  • It is not advised to fix the length of the array. Because if the value of an array is out of range, then we will end up getting the error.
  • Also, save the file in macro enable excel format to preserve the written VBA Code.
  • We can use different functions such as LBOUND, UBOUND, Split, Join, Filter, etc. in String Arrays using Loops.

Recommended Articles

This is a guide to the VBA String Array. Here we discuss how to use String Array in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA SendKeys
  2. VBA Name Worksheet
  3. VBA CInt
  4. VBA SubString
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA String Array Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW