If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Wednesday, November 20, 2013

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").Value
If 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