計算機語言 ( 大一 , 第二学期 )

1. 計算機語言 (大一, 第二学期) 福島康裕 助理教授, 環境系統工程研究室 email: fuku@mail.ncku.edu.tw, ext. 65838

2. Requirements • Use buttons to start macros • “Read data” button : readAllData 巨集 • Ask user how many data files there are • Import the required number of data file • “Clear data” button : clearData 巨集 • Clear all the data that are imported

3. Starting point: Reading 10 data at once! Sub readAllData() Dim stringnumber As String For i = 1 To 10 stringnumber = i With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Documents and Settings\福島康裕\Desktop\climate\climate" + stringnumber + ".dat", Destination:=ActiveCell) .TextFileConsecutiveDelimiter = True .Refresh BackgroundQuery:=False End With ActiveCell.Offset(5, 0).Range("A1").Select Next i End Sub

4. Type of parameters • Type of Parameters in VBA • Text • String • Number • Integer, Long Currency, Single, Double, Date • Logical type • Boolean • Casting (changing type of a parameter) • You need to have same type when you operate some calculation • Ex: a = 1, b = 2.345, c = a + b • In this case, a will be Integer, b will be Double. VBA will automatically change type of a into Double, and add them together.

5. Type of parameters • Casting in our exercise • We can combine two texts with “+” • name = “Fuku”greeting = “Hello, ” + name + “!!” • i = 1, 2, 3, 4, … so i is Integer type • we cannot add Integer with String directly • We cast the value in i into String • Dim stringnumber As Stringstringnumber = i ’ casting happens in this line!! We need to combine … “climate” and 1,2,3,…,9 and “.dat”

6. Reading n data at once! Sub readAllData() Dim stringnumber As String num = Application.InputBox("How many data files?")‘ ask number to user Range("F2") = "number of data:“‘ write Range("G3") = num‘ number of data input by user Range("A5").Select‘ always import to the same place For i = 1 To num stringnumber = i With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Documents and Settings\福島康裕\Desktop\climate\climate" + stringnumber + ".dat", Destination:=ActiveCell) .TextFileSpaceDelimiter = True .Refresh BackgroundQuery:=False End With ActiveCell.Offset(5, 0).Range("A1").Select Next i End Sub

7. Starting point: Reading 10 data at once! Sub readAllData() Dim stringnumber As String For i = 1 To 10 stringnumber = i With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Documents and Settings\福島康裕\Desktop\climate\climate" + stringnumber + ".dat", Destination:=ActiveCell) .TextFileConsecutiveDelimiter = True .Refresh BackgroundQuery:=False End With ActiveCell.Offset(5, 0).Range("A1").Select Next i End Sub

8. Using button control • [View][Toolbars][Forms] Click button, and put it on the sheet. A dialog will pop up, and let you relate the button with a macro!

9. Clearing the data • First, just figure out how to clear a range… Sub clearData() Range(A5:M8).Select Selection.ClearContents End Sub Question: This range A5:M8 is for only 1 data set What is the range for n data sets?

10. Clearing the data • Now let’s clear the exact range… Sub clearData() ' Macro recorded 2005/4/14 by Fukushima Yasuhiro ' Dim num As String Dim erasing_range As String num = 5 * Range("g3").Value + 4 ‘ change type to String erasing_range = "A5:M" + num ‘ connect Strings Range(erasing_range).Select Selection.ClearContents Range("f2").ClearContents ‘ erase number of data Range("g3").ClearContents ‘ written in the sheet End Sub