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.



81 views8 comments

8 commentaires


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/


J'aime

Python Classes


Best Python Classes in Pune Maharashtra India


# Python Pune


## Python Classes in Pune


Pune is becoming a vibrant hub for learning programming languages, and Python is at the forefront of this trend. Python classes in Pune cater to a diverse audience, from beginners just starting their coding journey to experienced developers looking to enhance their skills.


When a Python class, it’s essential to consider factors such as:


+ **Course Content**: Ensure the curriculum covers fundamental topics, including data types, loops, functions, and libraries like Pandas and NumPy.

+ **Instructor Experience**: A good instructor can make a significant difference. Look for classes led by industry professionals or experienced educators.

+ **Class Size**: Smaller class sizes often mean…


J'aime

FULL STACK

Front-end Development: You’ll dive deeper into creating engaging user interfaces.


Back-end Development: Learn how to manage databases and server-side logic.


Deployment and Maintenance: Understanding the process to put your projects live and keep them running smoothly.


Certification and Placement Assistance


Many institutes in Pune provide certification upon completion of the course, which can be a significant boost to your resume. Additionally, keep an eye out for schools that offer placement assistance. This can be incredibly helpful in securing a position after you graduate.


“Getting a certificate felt like a badge of honor. It symbolized all the hard work I had put in during my training!” – A happy graduate.


In conclusion, finding the right full stack program in…

J'aime

How do I write a comparison and contrast essay?


Writing a comparison and contrast essay involves analyzing two or more subjects to highlight their similarities and differences. Start by choosing subjects that share a connection but also have distinct characteristics. Organize your essay by deciding whether to focus on one subject at a time (block method) or by comparing them side-by-side for each point (point-by-point method). Begin with a clear thesis that outlines your comparison’s purpose. As you write, use transitional phrases to smoothly guide the reader between ideas. For guidance, seek comparative essay writing help from services like BookMyEssay, where professionals can help craft clear, insightful essays.

J'aime

Solve Zone offers PhD thesis writing service and dissertation writing service for all subjects. Check out our Dissertation & Thesis sample today at an affordable price.

J'aime
bottom of page