VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft's event-driven programming language Visual Basic with Microsoft Office applications such as Microsoft Excel. By running VBA within the Microsoft Office applications, you can build customized solutions and programs to enhance the capabilities of those applications. A lot of people might not realize that they can actually learn the fundamentals of Visual Basic programming without having a copy of Visual Basic professional. Why? Because there is a built-in Visual Basic Editor in Microsoft Excel, and you can use it to customize and extend the capabilities of MS Excel. The applications you build with MS Excel is called Visual Basic for Applications, or simply VBA.
There are two ways which you could program a VBA, one is to place a command button on the spreadsheet and start programming by clicking the command button, another one is to write Visual Basic functions inside the VB Editor. Let's start with the command button first. In order to place a command button on the spreadsheet, you need to click View on the MS Excel menu bar and then click on the toolbar and finally select the Control Toolbox to launch the control toolbox bar . The control toolbox comprises various controls. In this chapter, you only use the command button. Now click on the command button and draw it on the spreadsheet.
Next, you click on the command button and the Visual Basic Editor will appear. Then you enter the statement as shown in the figure. The first statement will fill up cell A1 to cell A10 with the phrase "Visual Basic" while the second statement add the value in cell A11 and cell B11 and then show the sum in cell C11. It is that simple.
OUTPUT
2.1 The Concept of Variables
Variables are like mail boxes in the post office. The contents of the variables changes every now and then, just like the mail boxes. In VBA, variables are areas allocated by the computer memory to hold data. Like the mail boxes, each variable must be given a name. To name a variable in VBA, you have to follow a set of rules, as follows:
a) Variable Names
The following are the rules when naming the variables in VBA
Examples of valid and invalid variable names are displayed in Table 2.1
|
Table 2.1 : Example of valid and invalid variable names
b) Declaring Variables
In VBA, one needs to declare the variables before using them by assigning names and data types. There are many VBA data types, which can be grossly divided into two types, namely the numeric data types and non-numeric data types
i) Numeric Data Types
Numeric data types are types of data that consist of numbers, which can be computed mathematically with various standard operators such as add, minus, multiply, divide and so on. In VBA, the numeric data are divided into 7 types, which are summarized in Table 2.2
Table 2.2: Numeric Data Typesii) Non-numeric Data TypesThe nonnumeric data types are summarized in Table 2.3
Table 2.3: Nonnumeric Data Types
You can declare the variables implicitly or explicitly. For example, sum=text1.text means that the variable sum is declared implicitly and ready to receive the input in Text1 textbox. Other examples of implicit declaration are volume=8 and label=”Welcome”. On the other hand, for explicit declaration, variables are normally declared in the general section of the codes' windows using the Dim statement. The format is as follows:
|
Dim variableName as DataType
Example 2.1
Dim password As String
Dim yourName As String
Dim firstnum As Integer
Dim secondnum As Integer
Dim total As Integer
Dim BirthDay As Date
Dim yourName As String
Dim firstnum As Integer
Dim secondnum As Integer
Dim total As Integer
Dim BirthDay As Date
You may also combine them in one line, separating each variable with a comma, as follows:
Dim password As String, yourName As String, firstnum As Integer.
If the data type is not specified, VB will automatically declare the variable as a Variant. For string declaration, there are two possible formats, one for the variable-length string and another for the fixed-length string. For the variable-length string, just use the same format as Example 2.1 above. However, for the fixed-length string, you have to use the format as shown below:
Dim VariableName as String * n
where n defines the number of characters the string can hold. For example, Dim yourName as String * 10 mean yourName can hold no more than 10 Characters.
Example 2.2
In this example, we declared three types of variables, namely the string, date and currency.
End Sub
|
The output screen of Example 2.2
 |
A message box normally act as a dialog box where users can interact with the computer, it is able to perform certain actions in response to what the user clicks or selects. The format for a message box is as follows:
message=MsgBox(Prompt, Style Value,Title)
The first argument, Prompt, will display the message in the message box. The Style Value determines what type of command button will appear in the message box. . The Title argument will display the title of the message board. message is a variable that holds values that are returned by the MsgBox ( ) function. The values are determined by the type of buttons being clicked by the users. It has to be declared as Integer data type in the procedure or in the general declaration section.
This is the message box displayed by clicking the first message box
The codes are as follows:
Private Sub CommandButton1_Click()
MsgBox ("Welcome to VBA Programming")
End Sub
The code for the second button
Private Sub CommandButton1_Click()
Dim message As Integer
message = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
If message = 6 Then
Range("A1").Value = "You may proceed"
ActiveWorkbook.Activate
ElseIf message = 7 Then
ActiveWorkbook.Close
End If
End Sub
Dim message As Integer
message = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
If message = 6 Then
Range("A1").Value = "You may proceed"
ActiveWorkbook.Activate
ElseIf message = 7 Then
ActiveWorkbook.Close
End If
End Sub
The message box displays Yes, No and Cancel buttons.
The code for the third button:
Private Sub CommandButton3_Click()
Dim message As Integer
message = MsgBox("Click Yes to Proceed, No to stop", vbYesNo, "Login")
If message = 6 Then
Range("A1").Value = "You may proceed"
ActiveWorkbook.Activate
ElseIf message = 7 Then
ActiveWorkbook.Close
End If
End SubThe nessage box displayes Yes and No buttons
In this program, you place the command button1 on the MS Excel spreadsheet and go into the VB editor by clicking on the button. At the VB editor, key in the program codes as shown on the left.
I use randomize timer and the RND function to generate random numbers. In order to generate random integers between 0 and 100, I combined the syntax Int(Rnd*100). For example, when Rnd=0.6543, then Rnd*100=65.43, and Int(65.43)=65. Using the statement cells(1,1).Value=mark will place the value of 65 into cell(1,1).
Now, based on the mark in cells(1,1), I use the If.......Then....Elseif statements to put the corresponding grade in cells(2,1). So, when you click on command button 1, it will put a random number between 1 and 100 in cells(1,1) and the corresponding grade in cells(2,1).
The Interface
 | The Code |
Looping is a procedure in Excel VBA that performs repetitive tasks. There are two kinds of loops in Excel VBA, the For.......Next loop and the Do...Loop . To demonstrate the For....Next loop in Excel VBA, here are two examples:
|
Example 1:
Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next
End Sub
In this VBA program, you place the command button 1 on the spreadsheet then click on it to go into the Visual Basic editor. When you click on the button , the VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3......until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents row and j represent column.
In example 2,we use the nested loop to put the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) ..........until cells(10,5). The code and output are shown below.
Example 1
 |
Example 2
 |
Example 2
Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 1 To 10
For j = 1 To 5
Cells(i, j).Value = i + j
Next j
Next i
End Sub
There are four ways you can use the Do Loop as show below.
i) Do...........Loop While
(ii) Do until.............Loop
(iii) Do while............Loop
(iv) Do............Loop until
Example 1:
Arranging numbers in ascending order
Private Sub CommandButton1_Click()
Dim counter As Integer Do counter = counter + 1 Cells(counter, 1) = counter Loop While counter < 10 End Sub
In this example, the program will keep on adding 1 to the preceding counter value as long as the counter value is less than 10. It displays 1 in cells(1,1), 2 in cells(2,1)….. until 10 in cells (10,1).
|
Example 2:
Arranging numbers in descending order
Private Sub CommandButton1_Click()
Dim counter As Integer Do Until counter = 10 counter = counter + 1 Cells(counter, 1) = 11 - counter Loop End Sub
In this example, the program will keep on adding 1 to the preceding counter value until the counter value reaches 10. It displays 10 in cells(1,1), 9 in cells(2,1)….. until 1 in cells (10,1).
|
Examle 3
Private Sub CommandButton1_Click()
Dim counter , sum As Integer
'To set the alignment to center
Range("A1:C11").Select With Selection .HorizontalAlignment = xlCenter End With Cells(1, 1) = "X" Cells(1, 2) = "Y" Cells(1, 3) = "X+Y" Do While counter < 10 counter = counter + 1 Cells(counter + 1, 1) = counter Cells(counter + 1, 2) = counter * 2 sum = Cells(counter + 1, 1) + Cells(counter + 1, 2) Cells(counter + 1, 3) = sum Loop End Sub
In this example, the program will display the values of X in cells(1,1) to cells(11,1). The value of Y is X2and the values are display in column 2, i.e. from cells(2,1) to cells(2,11). Finally, it shows the values of X+Y in column 3, i.e. from cells(3,1) to cells(3,11)
|
Example 1
 |
Example 2
 |
Example 3
Select Case.....End Select decision structure. The general format of a Select Case...End Select structure is as follow:
Select Case variableCase value 1StatementCase value 2StatementCase value 3Statement....Case ElseEnd Select
In the following example, I will show you how to process the grades of students according to the marks given.
| Explanation:
To set the cell align alignment to center, we use the following procedure:
Range("A1:B1").Select
With Selection .HorizontalAlignment = xlCenter End With
We can use the statement case value1 to value 2 to specify the range of values that fulfill the particular case.
You should also include the error case where the values entered are out of the range or invalid. For example, if the examination mark is from 0 to 100, then any value out of this range is invalid. In this program, I use case else to handle the error entries.
The diagram on the lower left illustrates the output of this example.
|
We will explore how to create VBA that can format the color of a MS Excel spreadsheet. Using Visual Basic codes, we can actually change the font color as well as the the background color of each cell effortlessly. Alright, I am going to creating a program that can create random font and background colors using a randomize process. Colors can be assigned using a number of methods in VBA, but it is easier to use the RGB function. The RGB function has three numbers corresponding to the red, green and blue components. The range of values of the three numbers is from 0 to 255. A mixture of the three primary colors will produce different colors.
The format to set the font color is
cells(i,j).Font.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255
For example
The format to set the cell's background color is
|
In the following example, the font color in cells(1,1) and background color in cells(2,1) are changing for every click of the command button due to the randomized process.
|
|
 |
This is a simple VBA counter that can count the number of passes and the number of fails for a list of marks obtained by the students in an examination. The program also differentiates the passes and failure with blue and red colors respectively. Let’s examine the code below:
Private Sub CommandButton1_Click()
Dim i, counter As Integer
For i = 1 To 20
If Cells(i, 2).Value > 50 Then
counter = counter + 1
Cells(i, 2).Font.ColorIndex = 5
Else
'do nothing
Cells(i, 2).Font.ColorIndex = 3
End If
Next i
Cells(21, 2).Value = counter
Cells(22, 2).Value = 20 - counter
End Sub
Private Sub CommandButton1_Click()
Dim i, counter As Integer
For i = 1 To 20
If Cells(i, 2).Value > 50 Then
counter = counter + 1
Cells(i, 2).Font.ColorIndex = 5
Else
'do nothing
Cells(i, 2).Font.ColorIndex = 3
End If
Next i
Cells(21, 2).Value = counter
Cells(22, 2).Value = 20 - counter
End Sub
This is a simple VBA counter that can count the number of passes and the number of fails for a list of marks obtained by the students in an examination. The program also differentiates the passes and failure with blue and red colors respectively. Let’s examine the code below:
|
Visual Basic Editor in MS Excel can handle strings just as good as a stand-alone VB program. All the string handling functions in Visual Basic such as Left, Right, Instr, Mid and Len can be used in Excel Visual Basic Editor. The following example illustrates the usage of all these functions
10.1 The InStr function
InStr is a function that looks for and returns the position of a substring in a phrase
10.3 The Right function
Right is a function that extracts the characters from a phrase, starting from the Right.
Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.
10.4 The Mid function
Mid is a function that extracts a substring from a phrase, starting from the position specified by the second parameter in the bracket.
Mid(phrase,8,3) means a substring of three characters are extracted from the phrase, starting from the 8th position from the left, including empty space.
|
We can write VBA code that can select certain range of cells and at the same time perform certain tasks according to a set of conditions. In this example, we shall write the VBA code such that it can accept range input from the user and then change the mark to blue if it is more than or equal to 50 and change it to red if the mark is less than 50.
Private Sub CommandButton1_Click()
Dim rng, cell As Range, selectedRng As String
selectedRng = InputBox("Enter your range")
Set rng = Range(selectedRng)
For Each cell In rng
If cell.Value >= 50 Then
cell.Font.ColorIndex = 5
Else
cell.Font.ColorIndex = 3
End If
Next cell
End Sub
Private Sub CommandButton1_Click()
Dim rng, cell As Range, selectedRng As String
selectedRng = InputBox("Enter your range")
Set rng = Range(selectedRng)
For Each cell In rng
If cell.Value >= 50 Then
cell.Font.ColorIndex = 5
Else
cell.Font.ColorIndex = 3
End If
Next cell
End Sub
Explanation:
The InputBox function is used to accept value from the users.
rng and cell are declared as a Range variable using the Dim statement while selectedRng is declared as a string that receive input from the user.
Once the input is obtained from the user, it is stored using the Set method and the Range function.
For Each cell In rng ......Net cell is a loop that can iterate through the selected range, one cell at a time.
The If...Then...Else statements are to specify the color of the font according to the range of values determined by the conditions.
|  |
No comments:
Post a Comment