VBA in Excel

What Is VBA in Excel?

VBA is also known as Visual Basic for Applications. Microsoft developed a programming language for Microsoft Office applications such as MSExcel, MSWord, and MSAccess.

Though MSExcel provides many inbuilt functions, various complex calculations are needed that in-built functions cannot perform; hence, we require VBA. 

To access VBA editor in MSExcel, press “ALT+F11”. 

Let us understand the common terminologies associated with VBA:

1. Modules: Modules are the VBA files where code is written. Each module has its code block. Code can be written in each Module window and can be deleted, backed up, or can be imported to other code windows.

2. Sub-Procedures: These are similar to functions but do not return a value. The statements are written between Sub and End Sub. Sub-procedure can be called without the call keyword.

1. Function: A function is a reusable code called in other codes. This way, each code can be divided into smaller parts. For example, the statements/code is written between Function and End Function.

2. Sub-Procedures: These are similar to functions but do not return a value. The statements are written between Sub and End Sub. Sub-procedure can be called without the call keyword.

How VBA In Excel Used?

It has been developed by the company called, Microsoft and its name is VBA, or visual basic for applications. It mainly serves to automate routine processes while creating special Excel commands.

VBA provides a means by which people can create macros for tasks automation and improvement of Excel’s functioning capability. The following are the different parties that use VBA:

General users

MS office with programs like excel is commonly utilized by many people each day. In addition, VBA language can be freely accessed for free with MS Office product.

In addition, spreadsheets can be automated using VBA and executed for numerous purposes aside from spreadsheet creation and organization.

One example can be that users might require the automatic operation of certain features of Excel, some repeated works, a number of tasks which may include report generation etc. 

A VBA macro within Excel creates, formats and prints monthly sales graph-based report including bar charts. The only thing left for them is just clicking the mouse once, as Excel easily makes reports in accordance with company‘s needs.

Computer professionals

Computer professionals can do more complex jobs with VBAs which they would normally spend more time and other resources to finish. 

For instance, they can use VBAs to build custom Excel add-ons that increase application capacity through the addition of new functions which are absent in Excel.

Computer professionals are also aided by VBA such that it simplifies complex tasks that would require large volumes of code to be copied.

It has an additional advantage in creating languages that exist within MS office applications using a combination of the features and capability of two separate systems.

Corporate users

Besides being useful to individuals, VBA is also good for business. A business may use the VBA programming language to automate key business operations and organizational procedures. 

The application of VBA includes accounting processes, minuted tracking, immediate sales processing order, complicated data calculation, among others.
The aforementioned operations can be automated with VBA so as to enhance the efficiency of internal corporate processes.

In addition, it offers businesses a solution that helps them pool all their data on the cloud and access it anywhere around the globe.

VBA In-built Functions

Similar to all the programming languages, VBA also allows for Comments. Comments along with the logic flow also contain developed, modified, etc. To comment out a statement, we use Single Quote or can start with the keyword "REM."

MsgBox function: This function displays a message box with buttons that can perform various functions. 

Syntax: MsgBox(prompt[,buttons][,title][,helpfile,context])

Parameters:

Prompt - It contains a string to be displayed in the message box

Buttons - This parameter takes a certain alpha-numeric string that specifies the type of button, its identity, etc.

Title - This string is displayed in the title bar of the message box.

InputBox function: Asks for an input value; after entering the value, the user can press OK or Cancel. 

Syntax:

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context]

Parameters:

Prompt - Prompt string is displayed on the message box

Default - This is the text user would like to display

Xpos - The x position specifies the horizontal distance of the prompt from the left side

Ypso - The y position determines the bi-vertical distance of the prompt from the left side.

Variables:

Variables are unknown character/name that has a specified memory location attached to them. The variable's name is supposed to start with a letter as the first character. One cannot use special characters or space while naming a variable. 

Like all programming languages, VBA also has a set of keywords that can't be used for naming variables. 

Syntax:

Dim <<variable_name>> As <<variable_type>>

Data Types:

1. Numeric Data Types:

• Byte
• Integer
• Long
• Single
• Double
• Currency
• Decimal

2. Non-Numeric Data Types:

• String (fixed-length) -1 to 65,400 characters
• String (variable length) - 0 to 2 billion characters
• Date -    January 1, 100 to December 31, 9999
• Boolean -   True or False
• Object - Any embedded object
• Variant (numeric) -  Any value as large as double
• Variant (text) - Same as variable-length string

Constant:

Constant is a memory location that holds a value. It is different from a variable because a constant cannot be changed. Like variables, the constant's name should start with a letter and special characters, and space cannot be used.

Operators:

Operators are the mathematical operators used in VBA statements. Some of the operators are:

1. Arithmetic Operators: These operators work only on numeric data. 

• +

• -

• *

• /

• %

• ^

2. Comparison Operator: These operators compare values and return if the statement is true or false

• =

• <>

• >

• <

• >=

• <=

3. Logical Operator: This boolean operator works on binary inputs.

• AND

• OR

• NOT

• XOR

4. Concatenation Operator: Concatenation Operator works on numeric and strings data. However, the data type of both operands should be the same.

• +

• &

Decisions:

Several decision-making statements help users control the flow of the logic. For example, if, else, if, and else statements control the logic flow. 

Aside from combinations of if and else statements, there is a switch statement. A switch statement tests a variable against a list of values. It returns the value which matches the variable. 

Loops: 

Loops are used to execute similar statements repetitively. Following are various loop types and their description:

1. For loop: executes statements repetitively

2. For ..each loop: 

3. While ..wend loop

4. Do ..while loop

5. Do ..until loop:

Strings functions in VBA

Strings are a sequence of characters that contain all types of data, alphabets, numbers, special characters, etc. However, each of these data types can be read and operated only as a string datatype. Let us look at some predefined string functions in VBA.

1. InStr: Helps find the first place where the substring is found. Substring is an input for InStr. 

Syntax: InStr([start,]string1,string2[,compare]) where start specifies the start position of the search. 

String1 is the string to be searched, and string2 is the substring searched for in string1.

2. InstrRev: InstrRev is Instring Reverse. This function also searches for a substring in a string; however, the search is from right to left. 

Syntax: InStrRev(string1, string2[,start,[compare]]). 

String1 is the string to be searched, and string2 is the substring to be searched in string1. The start is the start position of the search.

3. Lcase: Returns a whole string in lowercase format.

4. Ucase: returns the whole string in uppercase format.

5. Left: Has parameters string and a number. It returns the first given number of characters from the left position.

6. Right: Has parameters string and a number. It returns the first given number of characters from the right position.

7. Mid: Mid function returns characters from a specified starting position in the string. 

Syntax: Mid(String, start[,length])

8. Ltrim: Ltrim function removes spaces in a string from the left side

9. Rtrim: Rtrim function removes spaces in a string from the right side

10. Trim: Trim function removes spaces from the left as well as the right side

11. Len: returns the length of a given string

12. Replace: Replace function finds the substring in the given string and replaces it with a new substring. 

Syntax: Replace(string, find, replacewith[,start[,count[,compare]]])

Find the substring to be replaced, replace with the substring to replace the old substring

13. Space: The space function fills the given string with a specified number of spaces. 

Syntax: space(number)

14. StrComp: StrComp compares two strings; the comparison is of two types - Binary comparison and Text Comparison. Based on which string is bigger, the function returns values -1, 0, and 1.

15. String: The string function is used to create a string of repetitive characters. 

Syntax: String(number, character). Its output will be character repeated n number of times. 

16. StrReverse: StrReverse function reverses the given string. 

Syntax: StrReverse(string)

Date-Time Function in vBA

Date and Time functions help convert date and time into the desired format.

Date functions

1. Date: The date function returns the current date. 

Syntax - date()

2. CDate: CDate function converts a given input to date format. 

Syntax - cdate(date)

3. DateAdd: DateAdd function is used for arithmetic operation on the Date format. The Interval parameter is used to specify the format of the number inserted, if it is in terms of years, months, weeks, days, etc. The interval values are yyyy - Year, q - Quarter, m- month, y- Day of the year, d-day, w - Weekday, ww-Week, h-hour,n-minute, s-second.

Syntax - DateAdd(interval, number, date)

The number is the operand to be taken into consideration for operation. The date is the other operand on which the operation takes place. It is to be noted that the number parameter can take positive as well as negative values. 

4. DateDiff: The dateDiff function is used to find the difference between two dates.

Syntax - DateDiff(interval, date1, date2 [,firstdayofweek[,firstweekofyear]]). 

Interval is used to find the output's date format, whether it should display the number of years, weeks, days, etc. 

5. DatePart: DatePart function returns a part of date. 

Syntax- DatePart(interval,date[,firstdayofweek[,firstweekofyear]]) 

Interval specifies the part of the date to be displayed. 

6. DateSerial: DateSerial returns input in date format. However, it is different from CDate in that DateSerial has a specific format of Date input.

Syntax - DateSerial(year,month,day)

The output format is Date/month/year.

7. FormatDateTime: FormatDateTime function is used to change the date format.

 Syntax - FormatDateTime(date,format)

Format parameter value varies from 0-4, each specifying different date formats.

8. IsDate: This function is used to check for data type if it is a date.

9. Day: The day function returns the day of the given date. It returns values between 1 and 31.

 Syntax - Day(date)

10. Month: The month function returns the month of the given date. It returns values between 1 and 12.

 Syntax - Month(date)

11. Year: The year function returns the year of the given date.

Syntax - Year(date)

12. MonthName: MonthName function returns the name of the month instead of integral values from 1 to 12. 

Syntax - MonthName(date)

13. WeekDay: The weekDay function returns the day of the week. Output values are integral, i.e., values are from 1 to 7. 

Syntax - Weekday(date[,firstdayofweek])

14. WeekDayName: WeekDayName, similar to the WeekDay function, returns the day of the week. However, instead of returning integral values, it returns strings Sunday,....., Saturday. 

Time Functions

1. Now: Now function returns the current date and time. 

Syntax - Now()

2. Hour: The hour function returns the hour of the day of the given time stamp. The value is numerical between 0 to 23. 

Syntax - Hour(time)

3. Minute: The minute function specifies the minute of the recorded timestamp. It returns values between 0 and 59. This function can be used to return a value or feed the value into another function, such as the Time function.

Syntax - Minute(time)

4. Second: The second function specifies the second of the recorded timestamp. It returns values between 0 and 59. 

Syntax - Second(time)

5. Time: The time function returns the current time. The difference between the Now and Time function is Now function displays the date as well. 

Syntax - Time()

6. Timer: The timer function returns the number of seconds and milliseconds between the time stamp and 12:00 AM. 

Syntax - Timer()

7. TimeSerial: The timeserial function returns time in a specified format. The input is time. 

Syntax - TimeSerial(hour,minute,second)

8. TimeValue: The timeValue function converts input into the desired time format. This is similar to the CDate function for dates. 

Syntax - TimeValue(StringTime)

VBA Complex data operations

Some of the complex data operations include:

Arrays: As we all know, variables are used to store a value. Sometimes, one needs to store multiple values under one name. This variable that stores multiple values is called an array.

Arrays are declared the same way variables are declared. 

The only difference is the parenthesis used in the declaration. The size of the array can be mentioned beforehand. 

Syntax - Dim arr(size)

The actual size of the array is the size mentioned + 1, as the array starts from 0. There is no fixed datatype of an array; elements of an array can be of different data types. Though arrays can have several dimensions, it is limited to 60 dimensions. 

Declaration of a multi-dimensional array - Dim arr(n,m,.....,k)

The declaration, as mentioned above, was for static arrays. To declare dynamic arrays, we use the ReDim statement. 

Syntax - ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

The preserve parameter is used if a data part is preserved. 

The varname parameter denotes the name of the variable.

Subscripts indicate the size of the array.

Array Methods:

1. LBound

2. UBound

3. Split

4. Join

5. Filter

6. IsArray

7. Erase

Function: A function is a set of statements that can be called in other codes. Most functions return a value; however, it is not mandatory. If a function wants to return multiple values, it will return in the form of an array. 

Syntax - 

Function Functionname(parameters)

Series of statements

End Function

Sub-Procedures:

Sub-procedures, like functions, is a series of statements; however, they do not return any value. Also, Su procedures can be called without a keyword. 

Syntax -

Public Sub Subprocedurename(Parameters)

MsgBox statements

End Sub

A sub procedure will be executed whenever these parameters are called in a function. However, it is to be noted that the sub-procedure can not be called. 

Objects:

Here are a few objects one comes across while dealing in VBA:

1. Application Objects:

These types of objects consist of the following:

• Application wide settings

• Return objects such as ActiveCell, ActiveSheet, and so on

2. Workbook Objects:

This object consists of all the workbook objects open in Excel.

3. Worksheet Objects:

Worksheet Objects consists of all the objects in a workbook

4. Range Objects:

Range objects consist of cells, rows, or columns containing one or more continuous blocks of cells.

Researched and authored by Punit Manjani | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AVERAGEIF Function
• Basic Excel Formulas
• MOD Function
• REPLACE Function
• Scatter Plot