Skip to main content

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 – Then
If EmployeeName = “Michael Douglas” Then
EmployeeDesig = “CEO”
End If

If – Then –Else
If EmployeeName = “Michael Douglas” Then
EmployeeDesig = “CEO”
Else
EmployeeDesig = “Associate”
End If

If – Then – ElseIf
If 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

Popular posts from this blog

26 Proven Work at Home Business Ideas You Can Run Part Time

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.

How to Find Niche Website Ideas for Affiliate Blogs

Choosing right niche website ideas is the most critical decision. The topic of your affiliate blog will have huge impact on the outcome of your efforts over several years.

21 Passive Income Ideas to Achieve Financial Independence

Passive income is the key to achieving financial independence.  Earning Passive income requires you to create some form of asset that can generate income. You might still need some effort to maintain the asset, but it would be significantly less than effort required to build the asset. If you are looking for ideas to generate passive income, than below list of options can help you generate it.