Arrays in Python and Excel VBA

Learning about arrays through simple examples

As someone without a formal education in programming, my journey has been shaped by self-learning. Recognizing the significance of revisiting basic programming concepts, I have found that a solid foundation enhances the overall programming experience. In this tutorial, we will delve into one such fundamental concept — arrays. Specifically, we’ll explore the concept of arrays in both Python and Excel VBA through simple examples. Let’s get started.

1. Arrays in Python

An array is a special variable that can hold one or multiple values of any data type. In Python, there is no built-in support for arrays, unlike similar data types such as lists. However, one can create arrays using the array module of the numpy package. The index of a numpy array object always starts with a 0. The last item inside a numpy array can be accessed by referring to -1. A numpy array can hold variables of a particular data type or multiple data types.

This is shown in the code snippet below. The snippet also shows how the shape (dimensions i.e., rows, columns), size (number of elements) and length (number of items in a container i.e., rows) can be accessed from a numpy array.

import numpy as np

simple_array = np.array([1, 2, 3])
mixed_array = np.array([1, 2, 3, "a", "b", "c", 4.5])
print ("Simple array: ", simple_array)
print ("First element of simple_array: ", simple_array[0])
print ("Shape of simple_array: ", simple_array.shape)
print ("Size of simple_array; ", simple_array.size)
print ("n")
print ("Mixed array: ", mixed_array)
print ("Last element of mixed_array: ", mixed_array[-1])
print ("Length of mixed_array: ", len(mixed_array))

1.1 Using numpy arrays for algebraic matrix operations

Because of their flexible structure, numpy arrays are very handy in creating matrix objects of different dimensions and performing operations on them. The screenshot above has the examples of 1-dimensional array objects.

Below, I have created two array objects a and b both of which are 2-dimensional arrays. They can be considered as 2*2 matrices. Performing the dot product of the two matrices is as simple as doing just np.dot(a, b). In dot product, a and b are regarded as vectors (objects having both magnitude and direction). In matrix multiplication, each element in matrix a is multiplied with the corresponding element in matrix b. For example, a11 (first row, first column item) is multiplied by b11, and so on.

a = np.array([[0, 1],[2,3]])
b = np.array([[3,4],[5,6]])
print ("Dot Product of a and b: n", np.dot(a,b))
print ("Matrix multiplication of a and b n",a*b)

Furthermore, one can perform other matrix operations such as addition, subtraction, and transpose. To get the determinant of the matrix, one can use np.linalg.det(a). To get the multiplicative inverse of a matrix, one can use np.linalg.inv(a).

print (“Addition of a and b:n”, np.add(a, b))
print ("Also addition of a and b:n", a+b)
print ("Transpose of a:n", a.T)
print ("Determinant of a:n", np.linalg.det(a))
print ("Inverse of a:n", np.linalg.inv(a))

1.2 Creating a m*n shape numpy array from list objects

I have two lists called countries_lived and capitals which contain the list of countries I have lived in and their corresponding capitals.

countries_lived = [“Nepal”,”India”,”Germany”,”Netherlands”]
capitals = [“Kathmandu”,”New Delhi”,”Berlin”,”Amsterdam”]

To create an array containing these list objects, I can use np.array([countries_lived, capitals]). This will return me an array of shape 2*4 (i.e., 2 rows and 4 columns). If I want to have a single country and its corresponding capital in the same row, I can just transpose the same array.

array1 = np.array([countries_lived, capitals])
print ("array1:n", array1)
print ("Shape of array1:n", array1.shape)
print ("Size of array1:n", array1.size)

array2 = np.array([countries_lived, capitals]).T
print ("array2:n", array2)
print ("Shape of array2:n", array2.shape)
print ("Size of array2:n", array2.size)

1.3 Appending an item to a numpy array and creating a dataframe

Say I want to append an item France and Paris to array2 as an additional row, this can be done using the syntax np.append(arr, values, axis = None). The values must be of the same shape as arr, excluding the axis. If the axis is not given, both arr and values are flattened before use.

As shown below, I appended the new item as a new row to the array. Finally, the array2 of shape (5,2) is used to create a dataframe object df with Country and Capital columns.

array2 = np.append(array2, [[“France”,”Paris”]], axis = 0)
print ("array2 after appening new row: n", array2)

import pandas as pd

df = pd.DataFrame(array2,
                 columns = ["Country", "Capital"])

df

2. Arrays in Excel VBA

Similar to Python, arrays are also a collection of variables in Excel VBA. The lower bound for arrays can start from either 0 or 1 in Excel VBA. The default lower bound is 0. However, the lower bounds for arrays can be specified by stating Option Base 0 or Option Base 1 on the top of each module.

To detect the lower bound and upper bound used for an array, one can use Lbound(array_name) and Ubound(array_name) respectively.

2.1 Declaring an array

Arrays can be declared publicly (i.e. globally) by using the Public keyword. Declaring an array or any other variable publicly in Excel VBA allows it to be used in any module or subroutine without declaring again.

Public countries(1 to 4) as String
Public capitals(4) as String
Public countries_visited() as String

Alternatively, arrays can be declared locally inside a subroutine simply using the Dim keyword. These arrays can then be used only inside the specific subroutine.

Dim countries(1 to 4) as String
Dim capitals(4) as String

In the above examples, the size of the arrays is also specified. Specifying 1 to 4 or only 4 both imply the array of size 4.

2.2 One-dimensional array

A one-dimensional array is assigned by declaring the number of rows (e.g., 1 to 5) i.e., the number of elements to be contained by an array. An example of creating a 1-dimensional array of the four countries I have lived in is given below. It will print the name of these countries in column A in the worksheet of the Excel file.

Option Base 1

Sub array_1d()

countries(1) = "Nepal"
countries(2) = "India"
countries(3) = "Germany"
countries(4) = "Netherlands"

Dim i As Integer
Range("A1").Value = "Country"

For i = 1 To 4
    Range("A" & i + 1).Value = countries(i)
Next i

End Sub

The output of the running the array_1d subroutine is as follows:

2.2 2-dimensional array

Two-dimensional arrays are defined by declaring the number of rows and columns. In the following example, I declare a 2-dimensional array called country_capital. The first element in each row corresponds to the element of the countriesarray declared in the previous section. The second element in each row corresponds to their capital cities which have been declared individually in the code below.

Sub array_2d()

Dim country_capital(4, 2) As String


For i = 1 To 4
    country_capital(i, 1) = countries(i)
Next i

country_capital(1, 2) = "Kathmandu"
country_capital(2, 2) = "New Delhi"
country_capital(3, 2) = "Berlin"
country_capital(4, 2) = "Amsterdam"

Range("B1").Value = "Capital"

For i = 1 To 4
    Range("A" & i + 1).Value = country_capital(i, 1)
    Range("B" & i + 1).Value = country_capital(i, 2)
    
Next i

End Sub

Running this sub-routine returns the following:

2.3 Dynamic arrays

Dynamic arrays are useful in cases when one is not certain about the size of the array and the size of the array can change in the future. In the code below, I specify two arrays countries_visited and population without specifying the size of the arrays. Inside the dynamic_array subroutine, I specify the size of both of these arrays as 4 by using the ReDim statement. Next, I specify each element of the array individually based on the four countries I have visited and their populations.

Option Base 1

Public countries_visited() As String
Public population() As Long

Sub dynamic_array()

Dim wb As Workbook
Dim ws2 As Worksheet
Set wb = ThisWorkbook
Set ws2 = wb.Worksheets("Sheet2")

ReDim countries_visisted(4)
ReDim population(4)

countries_visited(1) = "France"
population(1) = 68

countries_visited(2) = "Spain"
population(2) = 48

countries_visited(3) = "Iran"
population(3) = 88

countries_visited(4) = "Indonesia"
population(4) = 274

End Sub

After a while, I realize that I have also visited one more country (Portugal). I redefine the size of the array while preserving the original contents/elements in these arrays. I increase the size of these arrays by 1. For this, I use the ReDim Preserve statement as shown below.

ReDim Preserve countries_visited(1 to 5)
ReDim Preserve population(1 to 5)

The full code is given below:

Option Base 1

Public countries_visited() As String
Public population() As Long

Sub dynamic_array()

Dim wb As Workbook
Dim ws2 As Worksheet
Set wb = ThisWorkbook
Set ws2 = wb.Worksheets("Sheet2")

ReDim countries_visisted(4)
ReDim population(4)

countries_visited(1) = "France"
population(1) = 68

countries_visited(2) = "Spain"
population(2) = 48

countries_visited(3) = "Iran"
population(3) = 88

countries_visited(4) = "Indonesia"
population(4) = 274

ws2.Range("A1").Value = "Countries visited"
ws2.Range("B1").Value = "Population (million)"

ReDim Preserve countries_visited(5)
ReDim Preserve population(5)

countries_visited(5) = "Portugal"
population(5) = 10

Dim i As Integer
For i = 2 To 6
    Range("A" & i).Value = countries_visited(i - 1)
    Range("B" & i).Value = population(i - 1)

Next i

End Sub

The output of the above code is as shown:

2.4 Declaring arrays to store variables of different data types

In the section above, the countries_visited array is declared to store the variables of the String data type and the population array is declared to store the variables of the Long data type. Similar to Python numpy arrays, it is also possible to store variables of different data types in arrays in Excel VBA. In that case, the array has be to declared as a Variant type.

In the example below, an array test is declared as a Variant. Its size is specified as 3 using the ReDim statement. The three elements of types String, Integer, and Date are specified inside the test. The data types can be identified by passing the variable inside the TypeName() function.

Option Base 0

Sub variant_test()

Dim test() As Variant
ReDim test(3)

test = Array("Germany population in million: ", 83, Date)

Dim i As Integer
For i = 0 To 2 
     Debug.Print "Element " & i & " of test array is: " & test(i) & " of type " & TypeName(test(i))
Next i

End Sub

The output is as shown below:

Conclusion

Arrays are a collection of values/variables of one or more data types. Each variable is associated with a particular index number in an array. Arrays can be of one-dimension, two-dimensions, or multiple dimensions. In Python, there is no built-in support for arrays, but one can create arrays using the numpy package. Besides storing the values, the numpy arrays are also very useful in performing matrix operations. In Excel VBA, arrays are very useful in working with large databases of elements. In Excel VBA, an array can be static where the size of the array is pre-defined. Or array can be dynamic where the size of the array is not pre-defined, but it can be specified as we move along and even resized while preserving the elements already stored in the array.

The Python notebook, Excel workbook along with VBA scripts are available in this GitHub repository. Thank you for reading!

Arrays in Python and Excel VBA was originally published in Towards Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.