Excel VBA -Visual Basic for Application

 

Welcome to the world of VBA Programming using Excel.

I wish to extend the wise words of Steve Jobs and say everyone in the world should learn how to program a computer. You may not necessary end up working as a programmer or writing programs at all but it will teach you how to think or how to use the power of computer to your advantage.

What is Excel VBA ?

• VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs. VBA is an event-driven programming language implemented by Microsoft to develop Office applications.

• Excel VBA, short for Visual Basic for Applications, is a programming language that empowers users to automate tasks and create personalized solutions within Microsoft Excel. It enables users to create macros, which are instructions that automatically perform repetitive tasks.

• VBA stands for Visual Basic Analysis also. Excel VBA is Microsoft’s programming language for Office applications such as MS-Excel, MS-Word, and MS-Access. 

• Visual Basic for Application is a human-readable and editable programming code that gets generated when you record a macro. Today, it is widely-used with other Microsoft Office applications such as MS-Word, MS-Excel, and MS-Access.

•  You can use it for simple macros that will automate most of your routine tasks. For business use, you can create complete powerful programs powered by excel and VBA.

 

• Before we go into further details, let’s look at what computer programming is in a layman’s language. Assume you have a maid. If you want the maid to clean the house and do the laundry. You tell her what to do and she does the work for you. As you work with a computer, you will want to perform certain tasks. Just like you told the maid to do the house chores, you can also tell the computer to do the tasks for you.

• The process of telling the computer what you want it to do for you is what is known as computer programming. Just as you used English to tell the maid what to do, you can also use English like statements to tell the computer what to do. The English like statements fall in the category of high level languages. VBA is a high level language that you can use to bend excel to your all powerful will.

• VBA is actually a sub set of Visual Basic 6.0 BASIC stands for Beginners All-Purpose Symbolic Instruction Code.

What is Macros and VBA ?

1. A Macro is a piece of code.
2. Macros are written in programming language like VBA
3. VBA is visual basic for applications (like word , excel , Powerpoint)
4. VBA is editor in Word and  Excel .

VB , VBA and VBS difference ?

• VB is stand alone program that runs independently

• VBA is part of Excel  program and cannot work alone.

• VBS -Visual basic script is one of the variant of Visual basic language used for internet applications.

Why Macros is  required?

1. Repetitive task

2. Develop new formulas

3. Complex task.

Synonyms for Macros : Subroutine, Procedure or Program.

About Macros : Macro is object oriented program. In Excel anything and everything is an object for example workbook, sheet, cells, chart etc.

Object model for Excel

Application of VBA

You might wonder why to use VBA in Excel as MS-Excel itself provides loads of inbuilt functions. MS-Excel provides only basic inbuilt functions which might not be sufficient to perform complex calculations. Under such circumstances, VBA becomes the most obvious solution.

For example, it is very hard to calculate the monthly repayment of a loan using Excel's built-in formulas. Rather, it is easy to program a VBA for such a calculation.

Introduction to Visual Basic for Applications

Before we can write any code, we need to know the basics first. The following basics will help you get started.

Variable – in high school we learnt about algebra. Find (x + 2y) where x = 1 and y = 3. In this expression, x and y are variables. They can be assigned any numbers i.e. 1 and 3 respective as in this example. They can also be changed to say 4 and 2 respectively. Variables in short are memory locations.

 As you work with VBA Excel, you will be required to declare variables too just like in algebra classes

Rules for creating variables

Don’t use reserved words – if you work as a student, you cannot use the title lecturer or principal. These titles are reserved for the lecturers and the school authority. Reserved words are those words that have special meaning in Excel VBA and as such, you cannot use them as variable names.

Variable names cannot contain spaces – you cannot define a variable named first number. You can use firstNumber or first_number.

Use descriptive names – it’s very tempting to name a variable after yourself but avoid this. Use descriptive names i.e. quantity, price, subtotal etc. this will make your Excel VBA code easy to read

Arithmetic operators – The rules of Brackets of Division Multiplication Addition and Subtraction (BODMAS) apply so remember to apply them when working with expressions that use multiple different arithmetic operators. Just like in excel, you can use

+ for addition

– for subtraction

* for multiplication

/ for division.

Logical operators – The concept of logical operators covered in the earlier tutorials also apply when working with VBA. These include

If statements

OR

NOT

AND

TRUE

FALSE

How to Enable the Developer Tab

Below is the step by step process on how to enable the developer tab in Excel:

Create a new workbook

Click on the ribbon start button

Select options

Click on customize ribbon

Select the developer checkbox as shown in the image below

Click OK

or simply use the shortcut key : ALT + F11 ==> VBA Window will appear.

Popular shortcuts to be used while working in EXCEL VBA

1)F5 ==> To Run a Macro 

2) Alt + F11 ==> to open VBA window

3) F8 ==> For entering into step into process.

Let's start VBA Coding Now!..

Program #1. 

Sub display_name()

Dim name As String

name = InputBox("Enter your name")

MsgBox "Hello " + name

End Sub

Program #2. 

Sub input_value()

ActiveCell.Value = "The Petroleum and Natural Gas Regulatory Board (PNGRB) "

ActiveCell.Value = 2006

[b5] = 900

[c1:c10] = "PNGRB protect the interests of consumers"

Cells(18, 2).Value = "Petroleum"

Range("a1").Value = "Natural Gas"

Range("a2:a10").Value = "Regulatory Board"

Range("a11:a20").Value = "Chandrayaan-3 is successfully inserted into the lunar orbit"

Range("a25").Value = "PNGRB regulates the refining, processing, storage, transportation, distribution, marketing and sale of petroleum, petroleum products and natural gas excluding production of crude oil and natural gas so as and to ensure uninterrupted and adequate supply of petroleum, petroleum products and natural gas in all parts of the country."

cells (30,1).value="ORGANISATION STRUCTURE"

Range("a11:a20").Value = " Chandrayaan-3 is a follow-on mission to Chandrayaan-2 "

Program #3. 

sub copy_paste()

Range("d1:d100") = "Bank Details Benificiary Name Petroleum and Natural Gas Regulatory Board Bank Name State Bank of India Branch CAG - II, New Delhi Bank Address  4th & 5th Floor Redfort Capital Parsvnath Towers, New Delhi Current Account Number  37701381383 IFSC Code   SBIN0017313 MICR No 110002562"

'First method

range("b1:b2")=range("a1:a10")

'Second method

Sub copy_paste()

Range("a1:a10").Copy

Range("b1:b10").PasteSpecial

Application.CutCopyMode = False

End Sub

Program #4. 

Sub font()

Range("a1:a10") = "Natural Gas"

Range("b1:b10").font.name = "Algerian"

Range("b1:b10").font.Bold = True

End Sub

Program #5. Create your own function in Excel using Function keyword

 

Function add2numbers(x As Integer, y As Integer)

add2numbers = x + y

End Function

Program #6.  Usage of For loop for the repeating of a value  .

Sub for_loop()

Dim x As Integer

For x = 1 To 10 

Cells(x, 1).Value = "PNGRB"

Next

End sub

Or

Sub for_loop1()

Dim x As Integer

For x = 1 To 10 Step 2

Cells(x, 1).Value = 10

Next

End Sub

or

Sub for_loop2()

Dim x As Integer

For x = 1 To 10

MsgBox x

Next

End Sub

Try this one also to create a rainbow of 56 colors

Sub for_loop3()

Dim x As Integer

For x = 1 To 56

Cells(x, 1).Value = x

Cells(x, 1).Interior.ColorIndex = x

Next

End Sub

Program #7.  Play with font color

' Please  note : Font colors are 8 while colorindex supports 56 colors ranging from 1 to 56 and if you will use 57 program will reflect you an error message 

Sub font_color()

Range("a1:a10").Font.Color = vbGreen

 

Range("a1:a10").Font.Color = vbWhite

 

 Range("a1:a10").Font.Color = vbYellow

 

 Range("a1:a10").Font.Color = vbRed

 

 Range("a1:a10").Font.Color = vbBlue

 

 Range("a1:a10").Font.Color = vbCyan

 Range("a1:a10").Font.Color = vbMagenta

 

 Range("a1:a10").Font.ColorIndex = 1

 

 Range("a1:a10").Font.ColorIndex = 10

 Range("a1:a10").Font.ColorIndex = 50

End Sub

 

Program #8.  Change the background color of a Cell

Sub Cell_background_color()

Range("a1:a10").Interior.Color = vbGreen

 

Range("a1:a10").Interior.Color = vbWhite

 

 Range("a1:a10").Interior.Color = vbYellow

 

 Range("a1:a10").Interior.Color = vbRed

 

 Range("a1:a10").Interior.Color = vbBlue

 

 Range("a1:a10").Interior.Color = vbCyan

 Range("a1:a10").Interior.Color = vbMagenta

 

 Range("a1:a10").Interior.ColorIndex = 1

 

 Range("a1:a10").Interior.ColorIndex = 10

 Range("a1:a10").Font.ColorIndex = 50

 

End Sub

Program #9.  Apply paste special using VBA

Sub Paste_special()

Range("a1:a10").Copy

Range("b1:b10").Pastespecial xlPasteFormats

Range("c1:c10").Pastespecial xlPasteColumnWidths

Range("d1:d10").Pastespecial xlPasteValues

Application.CutCopyMode = False

End Sub

Program #10.  Different orientations using Excel VBA

Sub orientations()

Range("a1").Orientation = 0

Range("a1").Orientation = 10

Range("a1").Orientation = 20

Range("a1").Orientation = 30

Range("a1").Orientation = 40

Range("a1").Orientation = 50

Range("a1").Orientation = 60

Range("a1").Orientation = 70

Range("a1").Orientation = 80

Range("a1").Orientation = 90

End Sub

Program #10. Sub wraptext()

Range("a1").wraptext = True

Range("a1:c1").wraptext = True

End Sub

Sub merge_unmerge2()

Range("a1:c1").Merge

Range("a1:c1").UnMerge

End Sub

Sub delete()

Range("a1").delete

Range("a2:a5").delete

End Sub

Sub delete()

Range("a1").EntireRow.delete

Range("c1").EntireColumn.delete

End Sub

Resources : 

VBA Coding reference book 

VBA in Excel: What is Visual Basic for Applications, How to Use (guru99.com)