Reuse Method of the BASIC Program via Excel/VBA and the Ingenious Education of the Creative Idea

Tadayosi YOSHIMURA, Yoshihiro AOYAMA and Tatsuaki NISHIMIYA


1 Introduction

In Japan 2001, officials and people should promote the IT (information technology) revolution. It is the Heisei Restoration of IT education. There is a conventional idea that cannot be achieved without receptacle. The Japan budget is being put into hardware of construction in the IT center and acceleration of the communication network. This is only the hardware revolution. None notices it with respect to there being no software. We discuss what the contents of the work should be. It is important in information to know how the mechanism of the work is created. Creation with the character is a basis of IT education. The development programming is a defect of the software, even if the advanced hardware is done. It is necessary to carry out the motivation of software making which predominates in hardware for the creation. Creativeness is a source of high technology development on software programming in order to solve chemical problems.

2 Ingenious education on BASIC programming

Excel is one of the applications as a nucleus of the IT revolution. Certainly it is installed in a personal computer, and is utilized for the table computation process. In the computer application in the GUI (graphical user interface) environment, mainly on Windows, some applications prepared on the market are used. Then, the work of individual purpose is carried out. Therefore, individual work is not executed by programming to solve chemical problems. At MS-DOS in the previous DOS age, the work had been carried out in the programming of classical BASIC code with the line number. In putting the individuality, because the given function of application is not satisfactory, the intellectual work of programming is required and practiced. Most of the work as a necessity for the user may be covered with the applications now. Previously we had carried out acquisition and analysis of chemical information by the handmade BASIC program. In the improved Windows environment, the statistical processing application is newly purchased and is utilized, because the statistical processing function has improved in Excel. However, it does not have any functions for calculating eigenvalue and eigenvector, etc. The other add-in application is added, because the function is absent in the present Excel2000 version.
However, there is VBA (Visual Basic for Applications) for making a macro script of Excel platform. The principal component analysis program has been corrected a little in the old N88BASIC, and pasted in Excel/VBA editor. The line number has been given on the N88BASIC code, but it is unnecessary in Visual Basic language. It normally starts while the line number is kept, when we try to execute the program in Excel. We have been impressed with the old BASIC program started in new Excel base [1]. The BASIC programming in Excel/VBA is educationally important. In this paper, in comparison with the Visual Basic orientation report of Excel/VBA [2], the use of a classical BASIC is emphasized.
The learning is started from the base by the purchase of the VBA/Visual Basic manual book. However, we have given up the utilization by dropping programming composition of the object-orientation. When we would go for shopping at a local convenience store, we would like to go there more lightheartedly. There is surely the full equipment sensation and the handicapped aspect in Visual Basic utilization. The fizzle sensation is done, before the base of the VBA programming is learned, due to the difficulty of Visual Basic programming in which an amateur cannot seek help.
The BASIC code in the DOS age is a system for describing the program on the editor screen. The programming work is convenient for solving problems, even if it is the spaghetti type program code. It is advantageous that the flow of the work in the BASIC description is easy to confirm to the amateur. We, who were users since the DOS age, feel familiarity for the BASIC program. But, the young student, who changes to the user from Windows, does not think about the programming in the BASIC. Programming work with the individual request is not conceived by only the utilization of the application. The application is installed/used in the personal computer not to come out of the frame of Excel. Therefore, the creation in the application is not produced from individual desire. The user is satisfied with the work in his/her tolerable region.
What kind of method is education which cultivates the character of the student at the stage which raises the ability for problem solving? At present, it is a question asked in educational institutions. We would carry out the programming which utilized the character by VBA in the common field of Excel platform. The intention which carries out the creative work is raised in the programming. The programming process executes the thought of problem solving in the original idea. Basic ability in this regard is made to grow by the programming of Excel/VBA. There are some bugs in the programming. Ability rearing for problem solving by debugging is also an important proposition. The amateur's programming in VBA gives some hint and vitality of the solution, because it is excellent in the debug function. Self-confidence is acquired for creation in problem solving. The fulfillment in achieving the purpose is evident. We propose information education which enlightens us by programming as an education technique adapted to the IT age. One look at one BASIC program list produces simplification and intuition for problem solving. The transparency of the problem flow is produced, and the original solution method is conceived.
Now, the handling BASIC is a basis of classical BASIC in the DOS age. Though it is utilized on VBA, we do not program in the Visual Basic grammar of the object-orientation at all. The frequent use of "GoTo" sentence which is an origin of the spaghetti type program is permitted. It is the "GoSub" sentence that is the next most used. The "GoSub~Return" sentence is "Private Sub" procedure itself in VBA. It is a subroutine function which we want to use, until Visual Basic procedure can be understood.

3 Reuse method of the BASIC program

The method is explained by using "linear regression analysis (LRA) by the least squares method" which is a comprehensible example for the amateur. Excel work functions of slope and intercept have been prepared in the linear regression analysis. We can reach solution on the worksheet of Excel, however, the LRA problem is specially solved by the BASIC programming. The N88BASIC program list of LRA (y=a+bx) is shown in Figure 1. The empirical formula between mole heat of combustion (Hc (kcal/mol)) and carbon number n of the methane series hydrocarbon (CnH2n+2) is made in this problem [3].

Hc = a + b×n

The line number has been given in the list of the N88BASIC program, as shown in Figure 1. We may begin with the work which excludes the line number when converting for VBA. However, we choose the case in which the line number is generally kept, because the line number is recognized as a label in VBA. Though the dimension of variable (n) has been designated in the line numbers 110 and 120 in Figure 1, there is not function in VBA. The dimension arrangement is designated at the numerical value.

100 ' Linear Regression Analysis by the least squares method
110 n=10
120 dim x(n), y(n)
130 for i=1 to n
140   read x(i), y(i)
150 next i
160 data 1, 212.8, 2, 378.8, 3, 530.6, 4, 687.4,  5, 845.0
170 data 6, 995.1, 7, 1151,  8, 1307,  9, 1464,  10, 1620
180 '---------
190 gosub *Lsq
200 print using "Hc = ###.## + ####.## * n"; a, b
210 print "r =";r
220 end
300 '----Subroutine-----
310 *Lsq
320 sx=0:sy=0:sxx=0:sxy=0:syy=0
330 for i=1 to n
340    sx=sx+x(i)
350    sy=sy+y(i)
360    sxx=sxx+x(i)*x(i)
370    sxy=sxy+x(i)*y(i)
380    syy=syy+y(i)*y(i)
390 next i
400 b=(sxy-sx*sy/n)/(sxx-sx*sx/n)
410 a=(sy-sx*b)/n
420 r=(sxy-sx*sy/n)/sqr((sxx-sx*sx/n)*(syy-sy*sy/n))
430 return
Figure 1. N88BASIC code list of "linear regression analysis (LRA) by the least squares method" [3]

The data entry of line numbers 130~170 is exchanged in the cell of Excel (the under-mentioned). Correction for input/output of data is necessary, because the "read~data" sentence cannot be used.
The layout of the data input of the Excel worksheet is shown in Figure 2. The numerical values of x and y are set in the worksheet, and the output part of the calculation result to the cell is displayed in the 13th column of the Excel worksheet.

Figure 2. Layout on the Excel worksheet

The macro setting panel is displayed, because there is no macro program when the execution of the tool of VBA is chosen. The preparation button is pushed for the input of the macro name. The program list of Figure 1 is pasted at VBE (Visual Basic Editor), as shown in Figure 3. The grammar of VBA works here, and the error BASIC code displays in red color. Figure 4 describes the macro script which is converted into VBA. The colon (:) and line number are used without using an asterisk (*) mark - the label that has been permitted in N88BASIC.

Figure 3. Result of pasting the N88BASIC list on the VBE screen

Figure 4. List of the program by deletion/correction of line with unnecessary, line numbers, etc.

It becomes an output of the worksheet as shown in Figure 5, when improved the macro program is executed. The correlation coefficients are also obtained by the program in Figure 4.

Figure 5. Worksheet of the result of the macro LRA program

By the correcting operation on the above, it is possible to reuse a classical BASIC program which has been kept in a storehouse. Input and output of data are carried out in the assignment statement using Cells. It is almost the original BASIC code itself with respect to the line number. It is just the reuse of the classical BASIC program.

4 Reuse method from the paper list of book

"Copy & paste" of the BASIC code has been made to VBE on the above. The program list of paper or book is restored by using OCR (optical character reader) which is available on the market. First, a scanner is prepared. The page of the program publication is imaged by the scanner. In this stage, the BASIC program is merely image (Figure 6). In the menu [Environment setting]-[Character recognition processing], which is the menu function of the OCR software, "English" is designated. The list of the BASIC code becomes the text data, when we click the icon of the text display.

Figure 6. The scanned and imaged page of the program in book [4] by using PageManager

This program uses the Runge-Kutta method of the differential equation [4]. There is the relational expression between reaction rate x and reaction temperature t (°C), as follows:

While, the reaction is first-order. The temperature of the reaction tube is constant at 340. This is the nonlinear ordinary differential equation without an analytic solution. The reaction constant k is given in the Arrhenius' equation:

The relationship between x and t is calculated at x (0 to 0.95).
At this stage, the BASIC program becomes almost the text data which is computer-readable (Figure 7). It is pasted by copying the part of the program to the VBE editor window of Excel. The grammar of VBA works here, and the error BASIC code displays a red color in the error, and then is corrected by the above method. The Japanese character display should be done as reentry force of the part, because we may give it up at the stage of character recognition.

Figure 7. The result of taking in the character image by an OCR software

The exchange of the data in the Excel sheet is all corrected at Cells, because "print, input" sentence cannot be used. The * label, that cannot be utilized, is revised in the line number. The user-defined function of classical BASIC cannot be utilized. The note sentence is immediately chosen at the error part. Finally, the part of the user-defined function is added. Menu bar [Insertion]-[Procedure] of VBA is chosen, and [Addition of the procedure] is done. Two Private Function procedures of function name FNK and FNF are added by the selection of the Function/Private procedure (Figure 8). Macro program is executed "RKuta" as shown in Figure 9.

Figure 8. Result of adding two user-defined functions in the function procedure

Figure 9. Macro execution of "Rkuta"

The execution result (Figure 10) is displayed, if normal work is done. The N88BASIC program of the paper list would be perfectly restored, if it was saved at a disk file.

Figure 10. Execution result of Excel worksheet

5 Making the code block in several BASIC commands of the minimum

By combining some blocks of the limited form, a child creates the molding with his/her dream. Similarly, the BASIC programming is carried out in the order of the minimum of the fundamental commands. The minimally necessary parts of the BASIC command are itemized as follows:
  1. Dimension ... Dim
  2. Repetition ... For~Next
  3. Judgment/Junction ... If~Then
  4. Junction processing ...GoTo, GoSub~Return, Label(:)
  5. Comment ... Rem (' )
In Excel/VBA, we would order two commands to be necessary for an input/output of the data on the worksheet as follows:
  1. Selection of the worksheet ...Sheet("Sheet1").Select
  2. Selection of the cell ...Cells( ), Range( ).Select
Without checking a property or a method in respect to the VBA command, we may handle as a BASIC order. The analysis becomes just possible in the order of the minimum, though there is a function which omitted the description a little. In the present information society, an impossible work can be created only in Excel individually.
The BASIC code has originally grown with the function development of the personal computer. The creation is not produced, even if only the work in a given environment is done. Since it is important, we would work in the idea of ingenuity. The original work is carried out by creation if possible. We would work for the unresolved problem through possible BASIC programming without satisfying a given environment.

The authors are grateful to Prof. Brian T. Newbold of The University of Moncton for his valuable English checking.


[ 1] Tadayosi Yoshimura, Chemistry and Software, 23, 11-28 (2001).
[ 2] Shigekazu Moriguti, Excel/VBA Basic Orientation, JSA (2000).
Kiyoshi Kato, Visual Basic Programming at Excel, Kyoritsu (1999).
[ 3] Tadayosi Yoshimura, Hideo Sakagami, Information Technology in Chemistry, Ohmsha (1994), 63.
[ 4] Tadayosi Yoshimura, Hideo Sakagami, Information Technology in Chemistry, Ohmsha (1994), 83.
[ 5] Tadayosi Yoshimura, Yoshihiro Aoyama, Excel utilization research for the engineer, Transistor Technology Special, No.78, CQ Publisher (2002), 120-139.