Latest News - sebuah blog yang lahir dari anak Kependidikan Islam IAIN Surabaya

test Page

Monday, January 10, 2011

tes page....

under construction
under construction
under construction
under construction
under construction

under construction
under construction
under construction
under construction
under construction
under construction
Achmad Yazid BAstomi



Tuesday, July 27, 2010

16.1 Introduction to Arrays

By definition, an array is a list of variables, all with the same data type and name. 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, we might have difficulty in declaring 100 different names, this is a waste of time and efforts. So,  instead of declaring one hundred different variables, we need to declare only one array.  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. , which will make declaring variables streamline and much systematic.
16.2 Dimension of an Array
An array can be one dimensional or multidimensional. One dimensional array is like a list of items or a table that consists of one row of items or one column of items. A twodimensional array will be a table of items that make up of rows and columns. While the format for a one dimensional array is ArrayName(x), the format for a two dimensional array is ArrayName(x,y) while a three dimensional array is ArrayName(x,y,z) . Normally it is sufficient to use one dimensional and two dimensional array ,you only need to use higher dimensional arrays if you need with engineering problems or even some accounting problems.Let me illustrates the the arrays with tables.
Table 16.1. One dimensional Array

Student Name Name(1)
Name(3) Name(4) Name(5) Name(6)
Table 16.2 Two Dimensional Array

Name(1,1) Name(1,2) Name(1,3) Name(1,4)
Name(2,1) Name(2,2) Name(2,3) Name(2,4)
Name(3,1) Name(3,2) Name(3,3) Name(3,4)

16.2 Declaring Arrays

We could use Public or Dim statement to declare an array just as the way we declare a single variable. The Public statement declares an array that can be used throughout an application while the Dim statement declare an array that could be used only in a local procedure.
The general format to declare a one dimensional array is as follow:
Dim arrayName(subs) as dataType
where subs indicates the last subscript in the array.
Example 16.1
Dim CusName(10) as String
will declare an array that consists of 10 elements if the statement Option Base 1 appear in the declaration area, starting from CusName(1) to CusName(10). Otherwise, there will be 11 elements in the array starting from CusName(0) through to CusName(10)
CusName(1) CusName(2) CusName(3) CusName(4) CusName(5) CusName(6) CusName(7) CusName(8) CusName(9) CusName(10)
Example 16.2
Dim Count(100 to 500) as Integer
declares an array that consists of the first element starting from Count(100) and ends at Count(500) 
The general format to declare a two dimensional array is as follow:
Dim ArrayName(Sub1,Sub2) as dataType
Example 16.3
Dim StudentName(10,10) will declare a 10x10 table make up of 100 students' Names, starting with StudentName(1,1)  and end with StudentName(10,10).

16.3 Sample Programs

(i) The code
Dim studentName(10) As String 
Dim num As Integer
Private Sub addName() 
For num = 1 To 10 
studentName(num) = InputBox("Enter the student name", "Enter Name", "", 1500, 4500) 
If studentName(num) <> "" Then 
Form1.Print studentName(num) 
End If
End Sub
The above program accepts data entry through an input box and displays the entries in the form itself. As you can see, this program will only allows a user to enter 10 names each time he click on the start button.
The Code
Dim studentName(10) As String 
Dim num As Integer
Private Sub addName( ) 
For num = 1 To 10 
studentName(num) = InputBox("Enter the student name") 
List1.AddItem studentName(num) 
End Sub 
Private Sub Start_Click() 
End Sub
The above program accepts data entries through an InputBox and displays the items in a list box. 


Lesson 15: Creating VBA Functions For MS Excel

Saturday, December 19, 2009

15.1 The Needs to Create VBA Functions in MS-Excel
You can create  your own functions to supplement the  built-in functions in Microsoft Excel spreadsheet, which are quite limited in some aspects. These user-defined  functions are also called  Visual Basic for Applications functions, or simply VBA functions. They are very useful and powerful if you know how to program them properly. One main reason we need to create user defined functions is to enable us to customize our spreadsheet environment for individual needs. For example, we might need a function that could calculate commissions payment based on the sales volume, which is quite difficult if not impossible by using the built-in functions alone. The code for VBA is illustrated on the right.

Table 15.1: Commissions Payment Table
Sales Volume($)
In table 15.1, when a salesman attain a sale volume of  $6000, he will be paid $6000x15%=$720.00. A visual basic function to calculate the commissions can be written as follows:

Function Comm(Sales_V  As Variant) as Variant
If Sales_V <500 Then 
Elseif Sales_V>=500 and Sales_V<1000 Then
Elseif Sales_V>=1000 and Sales_V<2000 Then
Elseif Sales_V>=200 and Sales_V<5000 Then
Elseif Sales_V>=5000  Then
End If
End Function

15.2 Using Microsoft Excel Visual Basic  Editor
To create VBA functions in MS Excel, you can  click on tools,
select macro and then click on Visual Basic Editor as shown in Figure 15.1
  Figure 15.1: Inserting MS_Excel Visual Basic Editor

Upon clicking the Visual Basic Editor, the VB Editor windows will appear as shown in figure 15.2. To create a function, type in the function as illustrated in section 15.1 above After typing, save the  file and then return to the Excel windows.

Figure 15.2 : The VB Editor

 In the Excel window, type in the titles Sales Volume and Commissions in any two cells. By referring to figure 15.3, key-in the Comm function at cell C4 and by referencing the value in cell B4, using the format Comm(B4). Any value appear in cell B4 will pass the value to the Comm function in cell C4. For the rest of the rows, just copy the formula by  dragging  the bottom right corner of cell C4 to the required cells, a nice and neat table that shows the commissions will automatically appear (as shown in figure 15.3). It can also be updated anytime

Figure 15.3: MS Excel Windows- Sales Volume

Lesson 14: Creating User-Defined Functions

Friday, November 27, 2009

14.1 Creating Your Own Function
The general format of a function is as follows:
Public  Function functionName (Arg As dataType,..........) As dataType
Private  Function functionName (Arg As dataType,..........) As dataType
* Public indicates that the function is applicable to the whole project and 
   Private indicates that the function is only applicable to a certain module or procedure.

Example 14.1
In this example, a user can calculate the future value of a certain amount of money he has today based on the interest rate and the number of years from now,  supposing  he will invest this amount of money somewhere .The calculation is based on the compound interest rate.

The code
Public Function FV(PV As Variant, i As Variant, n As Variant) As Variant
'Formula to calculate Future Value(FV) 
'PV denotes Present Value
FV = PV * (1 + i / 100) ^ n

End Function
Private Sub compute_Click()
'This procedure will calculate Future Value 
Dim FutureVal As Variant
Dim PresentVal As Variant
Dim interest As Variant
Dim period As Variant
PresentVal = PV.Text
interest = rate.Text
period = years.Text

'calling the funciton
FutureVal = FV(PresentVal, interest, period) 
MsgBox ("The Future Value is " & FutureVal)

End Sub

Example 14.2
The following program will automatically compute examination grades based on the marks that a student obtained. The code is shown on the right.

The Code

Public Function grade(mark As Variant) As String
Select Case mark 
Case Is >= 80 
grade = "A" 
Case Is >= 70 
grade = "B" 
Case Is >= 60 
grade = "C" 
Case Is >= 50 
grade = "D" 
Case Is >= 40 
grade = "E" 
Case Else 
grade = "F" 
End Select

End Function

Private Sub compute_Click()
grading.Caption = grade(mark)

End Sub


Your Ad Here