Excel Made Easy

VBA advanced array functions in Excel

Arrays have some predefined functions which are very much useful while working with.

The most often used are as detailed below

a) Split()

This method is mostly used to split a string into array of strings based on a delimiter

This method always returns an array of values and should be stored in a array variable

The following example splits a string into array of strings with "@" as Delimiter

The following illustrates with output

The output is as shown below

b) Lbound and Ubound

As the name indicates, the Lbound method returns an integer that specifies the lowest bound or the starting index of the Array

Similarly, The Ubound method returns the upper bound of the array

Thses methods are useful when working with Arrays of unknown size resulting from Split()

The following example illustrates this.

The Output shows the Lower and Upper Bound and thereby, the size of the array

You can find similar Excel Questions and Answer hereunder

1) How can I declare a variable in VBA, what is important to declare

2) How to modify strings in VBA, what are the functions you can manipulate strings with

3) What is a function in VBA. How to create a function and how to call a function

4) What are the date and time function in Excel VBA

5) How to convert a number or string into a data with the cdate function

6) Filtering the value field in a pivot table in Excel

7) How to format variable with VBA with the very useful format function. Define the number of decimal, the way a date is formated

8) Vba length of an array in Excel

9) How can worksheet functions be accessed in VBA?

10) What is variable scope in VBA, how do variable can be reach from other function or routines in VBA