Excel VBA?

(Note: I am a learner of VBA and know very little about it, the purpose of this post is to list the things that I have learnt in a sequence for future use/reference) 

Excel VBA?

Excel Visual Basic for Applications (VBA) is the programming language embedded in Excel. We can use it for writing Macros, preparing sophisticated workbooks and applications.

For learning it we just need to be familiar with the normal use of Excel.

Starting Steps

VBA Editor would be already there installed along Excel) you can check it by pressing Alt F11 button

We need to show the Developer Tab if you don’t see it (Excel 2010) then go to the "File tab -> Options (under Help) -> Customize ribbon" and there tick the box of the Developer ribbon

Recording a Macro

In Cell A1, type some text. Then go to Developer tab and click on Record Macro. Now any step you perform will be recorded, till the time you stop recording. So now select the cell A1 and highlight with color and after that stop recording, this option would be available under developer tab.

Run the Macro through button

Under developer tab click on Insert and then under form controls select the first button namely (Button (Form Control)) and place it where you want. You can set the name of button whatever you want like Click here. Now right click on that button and the option of Assign Macro and then select the Macro. Now every time you press the button the Macro will run.

Now to view you recorded Macro’s coding Press Alt F11 or under Developer tab click on Visual Basic or under the same tab click on Macros and select Macro and click on Edit. In the window you can see the coding.

So this is a very helpful tool of recording, while you are writing a coding and are facing difficulties then record it and take help from it or improvise it.

Write and run a code/Macro

Open the VBA Editor and insert a module (Insert > Module). Now start writing the code by writing the word Sub space then name of the Macro and parenthesis i.e. Sub Amiq(), pressing enter will write End Sub as well so now our code would be after the Sub and before End Sub.

Write the below code there and run it through the button (as in the below snapshot). You can run it through command button as well but till the time you are writing and testing along, it’s wise to run the code from VBA Editor.

Sub test()

Range("A1").Value = "Amiq"

End Sub

Running this code will update the Cell A1 value as Amiq.

Selecting Sheet, Cells, Range

While we write a code we need to learn how to select sheet, cells or Range.

Select the Sheet1

Sheets(“Sheet1”).Select

Select a Cell

Range(“A1”).Select

Or we can do it in a different way as well

Cells(1, 1).Select

Here the first 1 is row no. and other one is column no. which is A1 in this case.

Select a range

Range(“A1:B10”).Select

Variables

Let us see how to declare, initialize and display Excel VBA variables.  Variable is used to store a value, it can be of any type.  Like Integer to store whole numbers, double which can store numbers after the comma, string to store text, Booleans to hold true & false and many others.

Example code

Sub Test()

Dim x As Integer

X = 6

Range(“A1”).Value = x

End Sub

Second Example Code showing String manipulations along

Sub Test()

Dim text1 As String, text2 As String
text1 = "Hi "
text2 = "Tim"

Range(“A1”).Value =  text1 & text2

End Sub

Third Example Code showing calculation along

Sub Test()

Dim x As Integer
x = Range("A1").Value

x = x + 1
Range("A1").Value = x

End Sub

Running the above code every time (can be through command button) will increment the Cell A1’s value by 1. It’s a counter that you have createdJ.

If Then Else, Elseif statement

The If then statement allows us to execute certain lines of code when a specific condition is met

Sub Test()

Dim marks As Integer, grade As String
marks = Range("A1").Value

If marks >= 50 Then grade = "passed"

Range("B1").Value = grade

End Sub

In the above code we are first declaring two variables, one is integer and other is string

In the next line we have initialized the variable marks with cell A1’s value

If marks are more than or equal to 50, we assign the word/text passed to the variable grade.

And in the end we have placed the value of the variable grade into cell b1.

Else Statement

If the marks are less than 50 the previous code will empty cell B1. Here we can use the Else statement to assign the text failed to the variable grade if score is less than 50. As we use the Else, we will end the if as well by writing End If before End sub.

Sub Test()

Dim marks As Integer, grade As String
marks = Range("A1").ValueIf marks >= 60 Then
    grade = "passed"Else
    grade = "failed"Range("B1").Value = grade

End If

End Sub

Elseif statement

If we want to use multiple if statements then we can do this through Elseif as well. See below the example. One more thing that logical operators ‘And’ & ‘Or’ can also be used, see below the use of ‘and’.

Sub Test()

Dim marks As Integer, grade As String

marks = Range("A1").Value

If marks > 29 And marks < 60 Then

grade = "Repeat"

ElseIf marks >= 60 Then

grade = "passed"

Else: grade = "failed"

End If

Range("B1").Value = grade

End Sub

Nesting Statements

The nesting of statements is using one statement within the other. See the example below, here we have started another if between.

Sub Test()

If Cells(1, 1).Value >= 50 Then

Cells(1, 2).Value = "Passed"

Else

If Cells(1, 1).Value = Empty Then

Cells(1, 2).Value = "Null"

Else

Cells(1, 2).Value = "Failed"

End If

End If

End Sub

Loop

Loop is a very useful programming statement which is often used in Excel VBA.  See the below example code. Here we have declared ‘I’ as integer whose value is from 1 to 5. In the cell the rows reference is ‘I’ and column is 1 so we are saying that first column A’s 1 to 5 rows. Then the coding is simple some if statement which we have just studied. After Endif there is statement Next I which means once the test is run on the first row then do it on next one and it will last till record no. 5.

Sub Amiq()

Dim i As Integer

For i = 1 To 5

If Cells(i, 1).Value >= 40 Then

Cells(i, 2).Value = "Passed"

ElseIf Cells(i, 1).Value = Empty Then

Cells(i, 2).Value = "Null"

ElseIf Cells(i, 1).Value = 1 Then

Cells(i, 2).Value = "NA"

Else: Cells(i, 2).Value = "Failed"

End If

Next i

End Sub

For Each Loops in Excel VBA

Sub Amiq()

For Each MyCell In Range("A2:A6")

MyCell.Value = Replace(MyCell.Value, "-", "")

Next MyCell

End Sub

The Do While Loop

Sub Amiq()

Dim counter As Integer

counter = 1

Do While counter < 6

Cells(counter, "D").Value = counter

counter = counter + 1

Loop

End Sub

Message Box

The Excel VBA MsgBox is a dialog box that can be used to display information to the users. Like after an action is performed through a macro, we want a message in dialog box for our users then we can do this through msgbox.

Run the below code and see it.

Sub Fun ()

MsgBox “This is Fun”

End Sub

Use Chr(10) to move to next line in msg box, see below the code.

MsgBox "My Name is Khan and" & Chr(10) & "I am Khan"

Yes No Message Box, see how to add a Yes No Box

Sub amiq()

YesNo = MsgBox("Do you love me?", vbYesNo)

Select Case YesNo

Case vbYes

MsgBox "But I don't"

Case vbNo

MsgBox "Ok I also"

End Select

End Sub

Worksheet Functions

We can execute the functions in VBA as well, see below how the Countif function is used to pull the count of failed and passed people in the message box. Have some random values in cells A1 to A5 and run the below code and see how this works. Most of the statements are already discussed only the function is new in it.

Sub Amiq()

Dim i As Integer

For i = 1 To 5

If Cells(i, 1).Value >= 40 Then

Cells(i, 2).Value = "Passed"

ElseIf Cells(i, 1).Value = Empty Then

Cells(i, 2).Value = "Null"

ElseIf Cells(i, 1).Value = 1 Then

Cells(i, 2).Value = "NA"

Else: Cells(i, 2).Value = "Failed"

End If

Next i

MsgBox "Failed = " & WorksheetFunction.CountIf(Range("B1:B4"), "Failed") & " Passed = " & WorksheetFunction.CountIf(Range("B1:B4"), "Passed")

End Sub

Change the string/text data to Lowercase, Uppercase or proper

 See the below code, Loop statement is also used to change the text data which is in cells 1 to 10 of first column (A1:A10). Please note the below code is setting the text to proper and you can modify it by having the right statement mentioned in same code with apostrophe. So there is one more thing to learn that anything with start of apostrophe in the code is for reference and not a part of code.

Sub amiq()

Dim Name As String

Dim i As Integer

For i = 1 To 10

Name = Cells(i, 1)

Cells(i, 1).Value = Application.WorksheetFunction.Proper(Name)

' or it can be

'Cells(i, 1).Value = LCase(Name) or Cells(i, 1).Value = UCase(Name)

Next i

End Sub

Using Select Case

We have already discussed about the conditional statements i.e. If…Then…Else…Elseif but for multiple options, using them might be a bit complex. So for that we have another choice which is Select Case…..End Select decision structure.

  

General Format of Select Case statement

Select Case variable
Case value 1
Statement
Case value 2
Statement
Case value 3
Statement
.
.
.
Case Else

End Select

Below is an example code of Select Case. In the below example you can see how we have used different options. Just review it, the format is easy.

 Sub Amiq()

Dim grade As Integer

grade = Cells(1, 1).Value

Select Case grade

Case 1 To 10

Cells(1, 2).Value = "Passed"

Case Is >= 11

Cells(1, 2).Value = "Distinction"

Case Is = Empty

Cells(1, 2).Value = "Not Appeared"

Case Else

Cells(1, 2).Value = "Failed"

           

            End Select

            End Sub