top of page

How to "VBA Array"?


vba multidimensional arrays big data

Arrays are variables where you can store multiple items into one variable, where items could be of the same type (most common) such as strings, integers, doubles...etc. However, you could also have a variant array that stores variants or undefined variables of any type. Below we will go through how to work with arrays and to read data collection, tables from excel into a vba variable in the different ways possible.


Array Basics

Let's start with some variables to kick this off.

Dim MyArray(1 To 5) As String 'declaration

MyArray(1) = "Please"
MyArray(2) = "Analyze"
MyArray(3) = "The"
MyArray(4) = "Attached"
MyArray(5) = "Thx."

'To display the second item ("Analyze")
MsgBox MyArray(2)

We could also create an array in one line which could be very convenient with the Array syntax,

MyArray = Array("Please", "Analyze", "The", "Attached", "Thx.")

'This will display item "The", which is our 3rd item as the array 
 starts from 0.
MsgBox MyArray(2)

Arrays normally starts with index 0 unless defined like the first example above (1 to 5), or unless you have changed the so called "Option base" to 1 instead of 0, which is the default. Option base is a way to change the starting index, and it can be done once per module level. Just type: "Option Base 1" at the very top of the module to change the default setting. If I had done that, then both of my previous examples would have shown "Analyze" in the message box.

Examples with option base 0 - default.

'Contains 11 items.
Dim MyArray(10) As String

'Contains 11 items
Dim MyArray(0 To 10) As String 'exactly the same as above

'Contains 10 items
Dim MyArray(1 To 10) As String

'Contains 6 items
'Dim MyArray(5 To 10) As String

Read data from worksheet

For reading smaller data collections to an array variable we can use a loop to get the data in.

Sub Array_From_Worksheet()

'Create a 2-dimensional array. This will create a 4 by 2 array matrix.
Dim MyArray(1 To 4, 1 To 2) As String


'The below provides the specification for MyArray (4,2) if we had not know the demensions.
Debug.Print UBound(MyArray, 1)
Debug.Print UBound(MyArray, 2)

'Create our looping integers
Dim i As Integer, j As Integer


For i = 1 To 4
    For j = 1 To 2
 'Since I have a header I am adding +1 for the Cells syntax.
        MyArray(i, j) = Cells(i + 1, j).Value
    Next j
Next i

'To Display "Bond".
MsgBox myarray(1, 2)

End Sub
VBA read array from worksheet









Read bigger data from worksheet

When we however work with bigger data collections. We might want to consider not using a loop and start specifying the range to read in directly, as that would run considerably faster than iterating through an entire dataset one by one.

Sub Array_From_WorksheetBig()

'Looping in info take a lot of time.
'If you work with big data, just assign a range.
'1 action, 1 line instead of millions of actions.

StartTime = Timer 'Start of timer

'Create a 2-dimensional array.
Dim MyArray(1 To 10000, 1 To 28) As String

'Create our looping integers
Dim i As Integer, j As Integer

For i = 1 To UBound(MyArray, 1)  'using Ubound syntax instead of specifying size.
    For j = 1 To UBound(MyArray, 2)
 'Since I no longer have a header I don't have to add +1 for row.
        MyArray(i, j) = Cells(i, j).Value
    Next j
Next i

Debug.Print Round(Timer - StartTime, 2) 'Timer stops

End Sub
VBA read big data array from worksheet faster

Alternative way to read in bigger data

Sub ReadFromWorksheet()

'Key note to highlight is that this will read it as a variant and multidimensional.

'Even if only 1 column then syntax for extracting information will be:
'First row
'(1,1),
'Second row etc..
'(2,1)

StartTime = Timer 'Start of timer
'Dim myarray As Variant
MyArray = Range("A1:AB10000").Value '1 line, 1 operation to read in the data.

Debug.Print Round(Timer - StartTime, 2) 'Timer stops

End Sub

When I compare this time vs loop above this is 20k times faster. Yes this is faster by a factor of 20,000 which is why it is key to consider how you write your VBA scripts and the number of operations that you do.



Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python


If you have found this article or website helpful. Please show your support by visiting the shop below.



12 commenti


jack owen
jack owen
07 giu

Seek out a respectable business that provides the best Online Assignment Helper writing service with its skilled authors, prompt delivery, emphasis on original work, robust customer support, and a variety of services. Additionally, it ought to provide assurances like satisfaction, secrecy, and revisions. Irish students can rely on Assignments Help Ireland to deliver excellent, grade-winning assignments. These writers have years of expertise and are qualified to meet the needs of students who might be unsure of their ability to create assignments. Students can obtain reasonably priced and superior online assignment help writing services by requesting assistance from these professionals.

Mi piace

For expert legal support in divorce and family matters, trust the top legal services offered by Advocate Rajkumar Solanki. Specializing in divorce, child custody, alimony, property division, and other family law issues, Advocate Solanki provides personalized, compassionate, and strategic legal representation. With years of experience in handling complex matrimonial cases, he is committed to delivering fair, efficient, and stress-free solutions. Whether you are going through a contested divorce or seeking an amicable settlement, Advocate Rajkumar Solanki works tirelessly to protect your rights and secure the best possible outcome. His client-centered approach ensures that you are informed and supported throughout the entire legal process.

Know More

Mi piace

Finding the right recruitment agency is pivotal for nurses in Kerala aiming to work in New Zealand. Trusted agencies act as reliable bridges, guiding candidates through every step of the migration process. They assist with essential tasks such as securing registration with the New Zealand Nursing Council, managing documentation, and preparing for interviews. Experienced agencies also provide orientation sessions to familiarize nurses with the healthcare system and workplace culture in New Zealand. Look for agencies with a proven track record, official accreditations, and positive testimonials from successful candidates. By choosing the right partner, nurses can ensure a smooth and efficient transition, paving the way for a rewarding career in New Zealand’s esteemed healthcare industry.

See more

Mi piace

Matkabookapp
24 dic 2024

Take Advantage of Matka Chart and Win Online Matka Games

Online Matka games offer a thrilling blend of chance and skill. To gain an edge, understanding and using a Matka Chart effectively is essential. This detailed guide explores how Matka Charts work, how to utilize them for better outcomes, and how to find them online.

What is a Matka Chart?

A Matka Chart is a crucial resource in online Matka gaming, used to analyze past results and predict future outcomes. This chart compiles historical data on Matka games, providing players with insights into numbers, patterns, and trends.

Components of a Matka Chart

  • Historical Results: Displays past winning numbers and their frequencies.

  • Patterns and Sequences: Shows recurring patterns and number sequences.

  • Frequency Analysis: Highlights which numbers are…

Mi piace

Anamika Irani
Anamika Irani
14 nov 2024

Minoxidil for women in Australia is an effective treatment for hair thinning and hair loss. It works by stimulating hair follicles and improving blood flow to the scalp, helping to promote new hair growth. Available in both foam and liquid formulations, Minoxidil is easy to apply directly to the scalp. With regular use, many women notice thicker, fuller hair, though individual results may vary. This over-the-counter solution offers a non-invasive and convenient approach to addressing hair loss. For the best results, consistency in application is essential. It’s advisable to consult a healthcare professional before starting treatment to ensure it’s suitable for your specific needs.

https://generichealth.com.au/minoxidil-hair-loss/


Mi piace
bottom of page