Oct 022014
 

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)
  • Sing le –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

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

  • 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.

 October 2, 2014  , , , ,  Add comments

Leave a Reply