Excel VBA Functions

Standard

Function:

Function is a type of Procedure or Routine. In VBA, a Function is one which returns a Value or Object and a Procedure is which does the action but not return something.

Functions are of two types:

Pre- defined
User- defined

A Pre- defined function is a name of a set of instructions. You just need to use this function by its name. These functions are written in the libraries of the language so you don’t need to write the code for these functions.

A user- defined function is one which is user’s requirement specific. These type of functions need to be written by the user in the code window.

Pre- defined Functions in VBA:

In VBA, all pre- defined functions are categorized in 10 various categories

String Functions
Numeric/Mathematical Functions
Logical Functions
Information Functions
Date & Time Functions
Lookup / Reference Functions
Data Type Conversion Functions
File/Directory Functions

String Functions

STRCONV()	CURDIR()	CHAR()
FORMAT()	LTRIM()	RTRIM()
MID()	STR()	LCASE()
TRIM()	LEN()	ASC()
INSTR()	SPACE()	VAL()
REPLACE()	LEFT()	RIGHT()
UCASE()	INSTRREV()

Numeric/ Mathematical Function

ABS()	FROMAT()	SGN()
ATN()	INT()	SIN()
COS()	LOG()	TAN()
EXP()	RND()	FIX()
ROUND()

Logical Functions

CASE
IF-THEN-ELSE

Information Functions

ISDATE()	ISNULL()
ISNUMERIC()	ISERROR()

Date and Time Functions

DATE()	DAY(0	NOW()
DATEADD()	FORMAT()	TIMESERIAL()
DATEDIFF()	HOUR()	TIMEVALUE()
DATEPART()	MINUTE()	WEEKDAY()
DATESERIAL()	MONTH()	WEEKDAYNAME()
DATEVALUE()	MONTHNAME()	YEAR()

Lookup and Reference Functions

CHOOSE ()

Data Type Conversion Functions

CBOOL()	CDBL()	CSNG()
CBYTE()	CDEC()	CSTR()
CCUR()	CINT()	CVAR()
CDATE()	CLNG()

File/ Directory Functions

CHDIR()	DIR()	GETATTR()
CHDRIVE()	FILEDATETIME()	MKDIR()
CURDIR()	FILELEN()	SETATTR()

Arrays in VBA

Standard

What is an Array :

Mostly we work with a single variable or element. But sometimes we face a situation where we need a variable to hold more than one value of same data type e.g. all the names from a class. Here, we can declare an array of variables instead of using a variable for each item.

Definition :
An Array is the collection of similar type of data.

Working with Arrays:
Let’s start working with Arrays in VBA. Before using an Array, You need to declare it in the code like:
Dim arrStrNames() as String

Here, we have declared the Array but we can’t assign values to it since we have not mentioned the size (upper and lower indexes) of this Array. To fix this, we will write another line:

ReDim arrStrNames(5)

Now this Array is ready to contain 6 elements. Shocked?

Yes, this Array will contain 6 elements despite the upper bound being 5 as the number you have mentioned in parenthesis is upper index (not the number of elements). And the default lower index is 0 for Arrays in all VBA programs. So, as we have given the value 5 for upper index but not for lower index, this Array will start from 0 to 5 means 6 elements.

You can also set the lower index in your code by using the following line of code as the first line: Option Base 1

Now, if you write
Redim arrStrNames(5) Now “arrStrNames” will contain 5 elements e.g. 1 t o5.

The second way of declaring an Array is:
Dim arrStrNames(5) as String
OR
Dim arrStrNames(1 to 5) as String

By the above line of code, you wouldn’t need to Redim the Array as you already have given the lower and upper indexes.

Apart from the indexes, there is one more difference while declaring an Array.
If you declare an Array like “Dim arrStr() as String”. This would be a dynamic Array. You can give the indexes anywhere in the code and change too.
But if you declare an Array as “Dim arrStrNames(1 to 5) as String”, then it would be a static Array. You can’t change the lower and upper indexes in your whole program.

Now, you are ready to play with the array. Let’s write the following lines of code into the VBA code window:

Option Explicit 
Option Base 1
Sub aa()
 Dim lngCounter                  As Long     
 Dim arrStrStudentNames()        As String     
 Dim arrLngStudentMarks()        As Long       

    ReDim arrLngStudentMarks(10)     
    ReDim arrStrStudentNames(10)         
    For lngCounter = 1 To 10         
       arrStrStudentNames(lngCounter) = Application.InputBox("Please Give the name", "Name", "Useful Gyaan")
       arrLngStudentMarks(lngCounter) = Application.InputBox("Please Give the marks", "Marks", 100)
    Next lngCounter         
    Range("A1").Resize(10, 1).Value = arrStrStudentNames     
    Range("A1").Offset(, 1).Resize(10, 1).Value = arrLngStudentMarks
End Sub

And have fun.See you soon…………..