Objects

Excel has a large number of objects, and the VBA allows us to easily create and manipulate them. We can declare variable as an object by specifying the variable name and the object name as a type. Then, using the Set command, we assign the actual object to a variable. Once this is done, we can change its properties or run methods – subroutines that allow further manipulation of objects.

With structure

When we declare a variable as an object or custom data type we can access its members in many ways. In the opinion of many, the easier way is to individually “frame” the object fields by the With structure. Instead of dividing the variable and field name by the „points“, it allows us to define a block of commands, enter values by specifying field names, and then closing a block that completes the value assignment process.

Custom Data types

VBA language, among other things, allows you to create custom data types. These are structures that allow users to arrange the data in the desired way so that can easily access them, for example, to define records that store person data as a combination of: ID number, name, surname, address, location, phone number and e- mail address. The arranged data thus facilitates batch processing, and are often used in combination with arrays.

Sorting of an array

Sorting of an array is arranging of its members in a ascending or descending order. This can be achieved in many ways, and in the text that follows you will see one of the variants of the algorithm called Bubble Sort. It works by running two loops and then, depending on their index values, corresponding members of the array are compared. If the condition is met they change places. The procedure is repeated until the comparison is completed, and array sorted.

Calling a subroutine

During program development it’s important to notice which of its parts can be reused and, based on that fact, should create subroutines that will be called within the main program. Subroutines can be functions and procedures. We use the functions by specifying the variable name, followed by equality sign, and then the name of the function with the arguments. We invoke the procedures using the Call command, after which we specify the procedure name with the arguments.