Subscribe to this blog

Follow by Email

Search This Blog

How to Use Visual Basic for Application (VBA) in MS Excel

Microsoft Office documents (Excel, Word etc) are shipped with extensive functionality which is more than enough for most users. But there may be a need for enhancement to these applications or automate certain tasks that standard versions of these software are not equipped with. For this purpose, Microsoft applications include a programming environment to add new functions to standard application.
Visual Basic for Applications is programming language based on Visual Basic Programming language to enable advanced users to enhance standard applications (such as Word, Excel).

Getting started with VBA Editor

You can use Alt+F11 to open and access VBA editor.

You can add, delete and move windows. When you open, you should see Project window in top left frame. Project window lists areas where you can write code. In bottom left corner, you should see property window. This window shows properties of selected object. You can change properties to control object. Right window is the main window and is used to write code. Main window will be grey initially; it will turn white and editable when you insert some code. You can add more windows as required by going to Menu ->View.

Macros –Macros are used to automate tasks.

User Defined Functions –Excel comes with huge number of functions to use. You can add more functions by writing code in VBA.

Module –Module is a placeholder for VBA code and can be used to group functions and procedures.

Procedure –Procedure is a piece of VBA code to perform a specific task.

Key Data Types in VBA

A data type communicates to program what kind of variable is needed in operation.

Here is a quick list of data types.

String –Combination of alphanumeric characters.

Byte –A whole number between 0 and 255 (1 byte).

Integer –A whole number between -32768 and 32768 (2 bytes)

Long –A whole number outside the range of integer data type (4 bytes)

Single –To store a decimal number (not commonly used) (4 bytes)

Double –To store a decimal number with higher decimals (8 bytes)

Currency –Money values (8 bytes)

Date –Stores date and time (8 bytes)

Types of Loops in VBA

Loops are used to repeat actions.

Do While–Loop –Examines if the condition is true before performing action. It would continue to loop while the condition is true.

Do-Loop While –Examines if the condition is true after performing action. It would perform the action at least once.

Do Until-Loop -Examines if the condition is false before performing action. It would continue to loop while the condition is false.

Do-Loop Until -Examines if the condition is false after performing action. It would continue to loop while the condition is false. It would perform the action at least once.

For-To-Next –Used for the finite looping. The loop will work from start value to end value by incrementing values.

For Each-Next –Used for performing actions for each item in a group or range.

Types of If statement in VBA

Here is a list of If Statement for Visual Basic for Application.

If – ThenIf EmployeeName = “Michael Douglas” Then

EmployeeDesig = “CEO”

End If

If – Then –ElseIf EmployeeName = “Michael Douglas” Then

EmployeeDesig = “CEO”

Else

EmployeeDesig = “Associate”

End If

If – Then – ElseIfIf EmployeeName = “Michael Douglas” Then

EmployeeDesig = “CEO”

ElseIf EmployeeName = “Tina Wales” Then

EmployeeDesig = “CFO”

Else

EmployeeDesig = “Associate”

End If

Using Select Case Statement in VBA

If you have large number of conditions to verify, Select Case statement provides a way to check these conditions easily and in readable manner.

Select Case EmployeeName

Case “Michael Douglas”

EmployeeDesig = “CEO”

Case “Tina Wales”

EmployeeDesig = “CFO”

Case “Gina White”

EmployeeDesig = “CIO”

Case “Tim White”

EmployeeDesig = “COO”

Case Else

EmployeeDesig = “Associate”End Select

Application Object and Important Properties in VBA for Excel

Application Object is the highest object in Excel, followed by Workbook, followed by Worksheet and followed by Column, Row and cell. You can select object by going from highest to lowest in Excel.

Application.WorkBooks(2).WorkSheest(3).Rows(5).select

Application.WorkBooks(2).WorkSheest(3).Cell(2.1).select

Properties

Here are the key properties

ActiveCell – Returns the currently active cell (the one with cursor)

ActiveSheet – Returns the current sheet (the one with the cursor)

ActiveWorkbook – Returns the currently active workbook

RangeSelection – Returns selected cell range.

ActiveWindow – Returns active window object.

ThisWorkbook – Returns workbokok where macro is running

Method

InputBox - display as box which promots user for a value

MsgBox – is used to display message

Offset – is used to access a cell based on row and clumn index

Run – will run a macro

Move/Create/Add/Delete – Used to move/Create/add/delete object

Important Windows Control used to design Form in Excel

Form is the graphical container where other controls can be added to make user input logical and meaningful in Excel.

Here are the key controls

Label – To add label to the form

Textbox – To receive text input from user

ComboBox – Combination of Listbox and Textbox

ListBox – Let user pick an option from a list.

Checkbox – Receives input in check click

Optionbutton – Radio button input

ToggleButton – Let user enter Yes/No, True/False etc.

Frame – Can be used to make Optionbutton mutually exclusive

There are many more Controls. You can also add more controls to control toolbox. Each control is equipped with large number of properties to change look and feel of the control.

How to add a control multiple times in Excel VBA Forms

You can drag a control to place it on the form. If you want to place again, you need to drag it again.

Alternatively, you can double click on the control to select and keep clicking on the form to place the control on the form as many times as you want. Double click on the control again once you are done to deselect it.

Comments

Post a Comment

Popular posts from this blog

Is Bitcoin revolution real or fad? What did 2017 brought to
India in terms of Bitcoin and Blockchain adoption? How did Air Canada analyze
and revamped customer service? What is growth rate of digital transformation
spending? What can companies learn from GE’s digital transformation journey? –
We look at answers to these questions and much more in the monthly coverage of
enterprise digital transformation posts.

If you are looking for work at home business ideas, then this list can help you. All of these can be done as part-time business, so you don’t have to leave your job until you are confident of making full time income from your Work at home business.

What are some of the key trends to watch out for in automotive
industry? Will fake news increase or decrease in future? Can AI replace humans
in running governments? Can Disney disrupt itself and compete with Netflix and
Amazon? – We look at answers to these questions and much more in this week’s
coverage of digital transformation posts.