Quadratic equation solver Program using MS Excel Editor. In fact, it is easier to do it in MS Excel as you just enter the values into the cells rather than having to create the text boxes. So for those of you who are without a copy of MS Visual Basic compiler, but you have MS Office, you can copy the code and try this program out in your MS Excel
The Code:
Private Sub CommandButton1_Click()
Dim a, b, c, det, root1, root2 As Singlea = Cells(2, 2) b = Cells(3, 2) c = Cells(4, 2) det = (b ^ 2) - (4 * a * c) If det > 0 Then root1 = (-b + Sqr(det)) / (2 * a) root2 = (-b - Sqr(det)) / (2 * a) Cells(5, 2) = Round(root1, 2) Cells(6, 2) = Round(root2, 2) ElseIf det = 0 Then root1 = (-b) / 2 * a Cells(5, 2) = root1 Cells(6, 2) = root1 Else Cells(5, 2) = "No root" End If End Sub | Explanation:
The format of the quadratic equation is as below:
ax2+bx+c , where a,b,c are constants.
The number of roots depends on the determinant of b2-4ac
If b2-4ac>0 then there are two roots
If b2-4ac=0 then there is only one root
If b2-4ac<0 then there is no root.
By making use the above conditions and employ the use of If....Then...Else statements, the program is able to solve the quadratic equation
|
21.1 What is an Array?
When we work with a single item, we only need to use one variable. However, if we have a list of items which are of similar type to deal with, we need to declare an array of variables instead of using a variable for each item. For example, if we need to enter one hundred names, instead of declaring one hundred different variables, we need to declare only one array. By definition, an array is a group of variables with the same data type and name. We differentiate each item in the array by using subscript, the index value of each item, for example name (1), name (2), name (3) .......etc.
21.2 Declaring Arrays in Excel VBA
We use Dim statement to declare an array just as the way we declare a single variable.In VBA, we can have a one dimensional array, two dimensional array or even a multidimensional array (up to 60)
21.2(a) One Dimensional Array
The general format to declare a one dimensional array in Excel VBA is as follows:
Dim arrayName(index) as dataType or
Dim arrayName(first index to last index) as dataType
For example,
Dim StudentName(10) as String
Dim StudentName(1 to 10) as String
Dim StudentMark(10) as Single
Dim StudentMark( 1 to 10) as Single
Example 21.1
In this example, we define an array StudentName of five strings using the Dim keyword. We include an InputBox to accept input from the user. We also use the For ...Next loop to accept the input five times and display the five names from cell A1 to cell E1. The code is as follows:
Private Sub CommandButton1_Click( )Dim StudentName(1 to 5) As String
For i = 1 To 5
StudentName(i) = InputBox("Enter student Name")
Cells(i, 1) = StudentName(i)
NextEnd Sub
** You can also declare the array using Dim StudentName(5) As String
When we run the program, an input box will appear, as shown below. This input box will repeat five times and let the user enter five names.
The five names will be displayed in the spreadsheet as shown below:
Example 21.2
You can also declare more than one array in a single line. In this example, we declare three arrays in a single line, separated by commas.
Private Sub CommandButton1_Click( )Dim StudentName(3) As String, StudentID(3) As String, StudentMark(3) As Single
For i = 1 To 3
StudentName(i) = InputBox("Enter student Name")
StudentID(i) = InputBox("Enter student ID")
StudentMark(i) = InputBox("Enter student Mark")
Cells(i, 1) = StudentName(i)
Cells(i, 2) = StudentID(i)
Cells(i, 3) = StudentMark(i)
NextEnd Sub
When we run the program, three input boxes will appear consecutively to let the user enter the student name, the student ID and then the student mark. The process will repeat three times until the particulars of all three students have been entered. The three input boxes and the output are shown below:
 |  |
 |  |
21.2(b) Two Dimensional Array
Multidimensional arrays are often needed when we are dealing with more complex database, especially those that handle large amount of data. Data are usually organized and arranged in table form, this is where the multidimensional arrays comeinto play. However, in this tutorial, we are dealing only with the two dimensional array. Two dimensional array can be represented by a table that contains rows and columns, where one index represents the rows and the other index represent the columns.
The format to declare a two dimensional array is
Dim arrayName (num1, num2) as datatype
Where num1 is the suffix of the first dimension of the last element and num2 is the suffix of the second dimension of the last element in the array. The suffixes of the element in the array will start with (0, 0) unless you set the Option Base to 1. In the case when the Option Base is set to 1, then the suffixes of the element in the array will start with (1, 1). For example,
Dim Score (3, 3) as Integer
will create a two dimension array consists of 16 elements. These elements can be organized in a table form as shown in the table below:
Score(0,0)
|
Score(0,1)
|
Score(0,2)
|
Score(0,3)
|
Score(1,0)
|
Score(1,1)
|
Score(1,2)
|
Score(1,3)
|
Score(2,0)
|
Score(2,1)
|
Score(2,2)
|
Score(2,3)
|
Score(3,0)
|
Score(3,1)
|
Score(3,2)
|
Score(3,3)
|
If you set the option base to 1, then there will be only 9 elements, i.e from Score(1,1) to Score(3,3). However, if you want the first element to start with suffixes (1,1) you can also use the following format of declaration:
Dim Score(1 to 3, 1 to 3) as Integer
Example 21.3
If a company wants to track the performance of 5 salespersons over a period of 2 days, you can create a 5x2 array in Excel VBA, denoted by a 5X 2 table in a spreadsheet.
You can write the following VBA code:
Private Sub CommandButton1_Click()Dim SalesVolume(2to 6, 2 to 3) as SingleDim SalesPerson as Integer, Day as IntegerFor SalesPerson=2 to 6For Day=2 to3SalesVolume(SalesPerson, Day)=inputbox("Enter Sales Volume")Cells(SalesPerson, Day)=SalesVolume(SalesPerson,Day)Next DayNext SalesPersonEnd Sub
When the program is run, the inputbox that will prompt the user to enter sales volume will appear 10 times, as shown below:
After all the sales Volumes are entered, the values in the spreadsheet are shown below:
If you need to make sure the user enters the correct sales volume, you can change line 5 statement to
SalesVolume(SalesPerson, Day) = InputBox("Enter Sales Volume of " & " SalesPerson " & (SalesPerson - 1) & " Day " & (Day - 1))
A clearer instruction will be shown as follows:
No comments:
Post a Comment