APPLY EXCEL VBA TO TERRAIN VISUALIZATION

APPLY EXCEL VBA TO TERRAIN VISUALIZATION

1

Chih-Chung Lin(

),

Yen-Ling Lin (

)

1

Secretariat, National Changhua University of Education.

General Education Center, Chienkuo Technology University

2

Dept. of Management of Information Systems, National Chengchi University E-mail: [email protected]

ABSTRACT

Geographic Information System, GIS, which can effectively accesses, analyses, and visualizes spatial data in a digital way, is considered more and more important by many fields. However, affected by professional knowledge and software resources, GIS has a limitation of operation and application. Therefore, this article tries to apply the popular Excel software to presenting the geographic visualization of terrain, through using its character of cells and statistic diagram, writing easy-to- understand Visual Basic for Application (VBA) programming language, and combining easy to understand macro function.

Keywords Geographic Information System (GIS);

Excel; Visual Basic for Application (VBA); Digital Terrain Model (DTM)

1. INTRODUCTION

Many of the activities human beings do on the earth are related to geographic space location. To master space information accurately, interpret and deal with the increasingly complicated space problem, Geographic Information System, GIS, is considered more and more important by many fields, and is included in the important units of geography course of secondary education [1]. However, to operate GIS, besides professional knowledge, the provisions of software and hardware are also the keys. Therefore, its universal degree is restricted.

Excel, also called trial balance software, is a powerful and widely-used software. It can be used to build forms, calculate, analyze data, and has delicate layout, which makes data visualized, to help user making decision. Although Excel’s cells are similar to GIS’s grids, the former has never been seen to apply to presenting space data. As a result, this article tries to

apply popular Excel software to presenting the geographic visualization of GIS, through using Excel’s character of cells and statistic diagram, writing easy-to- understand Visual Basic for Application (VBA) programming language, and combining easy to understand macro function, in hoping to promote GIS and increase students’ learning interest.

2. THE DATA STRUCTURE OF GIS

GIS effectively accesses, analyses, and visualizes space data in a digital way. There are two categories in its space data structure, raster and vector. The two structures have their own pros and cons. Take raster for example, it presents data by separating space unit data into regular grids. The more and the smaller the grids are, the more clear the image it presents will be, and the more accurate the data will be, also the more the amount of data will be. Because the data structure of raster is simple and easy-to-learn, it is widely used in presenting altitude, soil, and land use information.

Digital Terrain Model, DTM, interprets the undulate condition of the surface of the earth in three-dimensional space by digital XYZ coordinates. The number information used in this article is from Taiwan digital terrain model data, sampling every 40 meters as regular grids. The DTM plane coordinate adopt Two Degree Zone Transverse Mercator Projection. The unit of plane coordinate is meter, and unit of altitude is also meter.

The original data is saved as three-dimensional coordinate by ASCII, the samples of sequential data is as followed:

150120. 2801840. -19.900 150160. 2801840. -19.900

…

350920. 2419680. -24.900 350960. 2419680. -24.900

47,985,210 coordinates points in total.

3. THE CHARACTERISTIC OF EXCEL’S WORKSHEET

Excel’s worksheet is consisting of rows and columns, which is similar to GIS raster space data structure. Excel 2007 has an upper limit of 1,048,576 rows by 16,384 columns, and 16,777,216 kinds of color in every cell.

The cells’ height and width can be minimized to 1 pixel, which equals to 1/40 centimeters [2].

In order to control other application program or simplify repeated operations, Excel provides its user with macro of Visual Basic for Application for programming and then practicing works automatically.

For Taiwan digital terrain model data, because its data can be contained in a worksheet (5,022 columns by 9,555 rows ＝ 47,985,210 cells < 16,384 columns by 1,048,576 rows, this article tries to integrate Excel’s build-in functions and designed a visualized VBA program.

4. THE PROCESS OF DTM DATA VIDUALIZATION

In every kind of topographic data visualization, Contours provided the richest topography, but normal people are not easy to distinguish. Layer tinting is to add different shapes and symbols between contour lines; one has to consider the chose of altitude interval and arrangement of colors. Besides, one can plot perspective traces, or called fishnets, by vertical intersect along x axis and y axis, to show the statistical 3D image, which is also a useful statistical surface expression [3].

Excel’s color index value is from 1to 56. For example, ColorIndex=1 is black, ColorIndex=2 is white, etc. The topographic altitude layer is used different colors to represent in this DTM layer case, as shown in table 1.

Table 1: Color of topographic altitude layer.

Characteristic of Terrain

(meter) Color ^Color _Index

>3000 53

2000 ~ 3000 46 1500 ~ 2000 45 1000 ~ 1500 44

500 ~ 1000 6

100 ~ 500 4

Above sea level

0 ~100 10

-10 ~ 0 34

-20 ~ -10 37

Under sea level

< -20 41

4.1. Visualizing Layer tinting

Because DTM use regular grids of equal interval, the process of using VBA program dealing with Layer tinting visualization is as followed:

(1)First, set the cells to have the same height and width to be the minimum 1 pixel, to increase the accuracy of visualization.

(2) In A1:B15 cells of “set” worksheet, increase progressively set the lowest limitation and its corresponding ColorIndex value of each topographic layer.

(3)Select DTM ’ s sequential data file, and use instruction “OPEN” to open it.

(4)According to DTM’s order of west to east and than from north to south，read (x, y, z) coordination one by one.

(5)In its corresponding cells, from A1 cell, move right and then move down, fill in its corresponding color of z coordination value.

(6)Repeat practicing step(4)~(5), until the end of DTM file.

The flowchart of the above (3)~(6) core steps program is as followed:

ri = 0 cj = 0 yid = 0

DTM_file = Application.GetOpenFilename() Open DTM_file For Input As #1 DO until EOF(1)

Input #1, x, y, z If yid - y = 0 Then cj = cj + 1 Else ri = ri + 1 cj = 1 End If

Cells(ri,cj).Interior.ColorIndex=Application.

WorksheetFunction.VLookup(z,Sheets("set").

Range ("A1:B15"), 2) yid = y

Loop Close #1

The VBA program of the above (3)~(6) core steps program is as followed

To present the whole topographic chart of Taiwan in a scene, This paper use DTM of 400 meters resolution, which has 503 points in x axis, 956 points in y axis, 480,868 altitude value in total, 11 different colors of altitude layers. Operated by the above Excel VBA macro, the result Layer tinting is shown below as Fig.1:

Fig. 1: Using Excel VBA dealing with DTM Layer tinting visualization result.

ActiveSheet.ChartObjects(" chart 1").Activate ActiveChart.PlotArea.Select

ActiveChart.SetSourceData

Source:=Range(Cells(1, 1), Cells(ri, cj)) 4.2 perspective traces 3D visualization

Besides using different colors in cells to present altitude visualization, Excel has many types of charts to choose, such as Column charts, Line charts, XY scatter charts, Surface charts, Radar charts. This paper use Surface charts to deal with topographic 3D visualization of perspective traces.

Its core processing steps are the same as Layer tinting.

The only difference is that Layer tinting is filling in appointed color to each cell, but perspective traces is filling in altitude value (z).

After filling all the cells, appoint the Source Data of Surface Chart. The following is its macro program:

This paper set different color of five altitude layers, operated by the above Excel VBA macro, the result perspective traces is shown as figure 2:

Fig. 2: Using Excel VBA dealing with DTM perspective traces visualization result.

5. CONCLUSION

If The Excel VBA macro program designed by this paper saved as xlsm form in Excel 2007 is only 35kb. It can process 1,048,576 rows by 16,384 columns digital terrain data and present in layer tinting and perspective traces 3D visualized. Operating 400 meters resolution DTM layer tinting under the environment of 2.4GHz, 1066MHz CPU, needs less than 30 seconds finishing Figure1. The function and quality is nearly equal to GIS.

Besides, Through VBA programming and DTM operating, the teaching and learning in Geography

education and information education can also be improved.

6. REFERENCES

[1] Ministry of education, 2005, General high school course principles.

[2] Microsoft Office Online,2007, Office Excel 2007 product overview, http://office.microsoft.com/zh- tw/excel/default.aspx

[3] He Chung-Ju, 2002, Cartography General Treatise, Taipei City.