NUREG/IA-0191

International 

Agreement Report

A Tool for Drawing With Excel 

Prepared by 

A. Progek, B. Mavko, I. Parzer 

Institut Jo~ef Stefan 

Jamova 39 

1001 Ljubljana, Slovenija 

Office of Nuclear Regulatory Research 

U.S. Nuclear Regulatory Commission 

Washington, DC 20555-0001 

January 2001 

Prepared as part of 

The Agreement on Research Participation and Technical Exchange 

under the International Code Application and Maintenance Program (CAMP) 

Published by 

U.S. Nuclear Regulatory Commission
AVAILABILITY OF REFERENCE MATERIALS 

IN NRC PUBLICATIONS

NRC Reference Material 

As of November 1999, you may electronically access 

NUREG-series publications and other NRC records at 

NRC's Public Electronic Reading Room at 

www.nrc.gov/NRC/ADAMS/index.html. 

Publicly released records include, to name a few, 

NUREG-series publications; Federal Register notices; 

applicant, licensee, and vendor documents and 

correspondence: NRC correspondence and internal 

memoranda, bulletins and information notices; 

inspection and investigative reports; licensee event 

reports; and Commission papers and their 

attachments. 

NRC publications in the NUREG series, NRC 

regulations, and Title 10, Energy, in the Code of 

Federal Regulations may also be purchased from one 

of these two sources. 

1 The Superintendent of Documents 

U.S. Government Printing Office 

P. 0. Box 37082 

Washington, DC 20402-9328 

www access gpo gov/su-docs 

202-512-1800 

2 The National Technical Information Service 

Springfield, VA 22161-0002 

www.ntis.gov 

1-800-533-6847 or, locally, 703-605-6000 

A single copy of each NRC draft report for comment is 

available free, to the extent of supply, upon written 

request as follows: 

Address: Office of the Chief Information Officer, 

Reproduction and Distribution 

Services Section 

U.S. Nuclear Regulatory Commission 

Washington, DC 20555-0001 

E-mail: DISTRIBUTION@nrc.gov 

Facsimile: 301-415-2289 

Some publications in the NUREG series that are 

posted at NRC's Web site address 

www.nrc.gov/NRC/NUREGS/indexnum.html 

are updated periodically and may differ from the last 

printed version. Although references to material found 

on a Web site bear the date the material was 

accessed, the material available on the date cited may 

subsequently be removed from the site.

Non-NRC Reference Material 

Documents available from public and special technical 

libraries include all open literature items, such as 

books, journal articles, and transactions, Federal 

Register notices, Federal and State legislation, and 

congressional reports. Such documents as theses, 

dissertations, foreign reports and translations, and 

non-NRC conference proceedings may be purchased 

from their sponsoring organization. 

Copies of industry codes and standards used in a 

substantive manner in the NRC regulatory process are 

maintained at

The NRC Technical Library 

Two White Flint North 

11545 Rockville Pike 

Rockville, MD 20852-2738 

These standards are available in the library for 

reference use by the public. Codes and standards are 

usually copyrighted and may be purchased from the 

originating organization or, if they are American 

National Standards, from

American National Standards Institute 

11 West 42nd Street 

New York, NY 10036-8002 

www.ansi.org 

212-642-4900

The NUREG series comprises (1) technical and 

administrative reports and books prepared by the 

staff (NUREG-XXXX) or agency contractors 

(NUREG/CR-XXXX), (2) proceedings of 

conferences (NUREG/CP-XXXX), (3) reports 

resulting from international agreements 

(NUREG/IA-XXXX), (4) brochures 

(NUREG/BR-XXXX), and (5) compilations of legal 

decisions and orders of the Commission and 

Atomic and Safety Licensing Boards and of 

Directors' decisions under Section 2.206 of NRC's 

regulations (NUREG-0750).

DISCLAIMER: This report was prepared under an international cooperative agreement for the exchange of 

technical information. Neither the U.S Government nor any agency thereof, nor any employee, makes any 

warranty, expressed or implied, or assumes any legal liability or responsibility for any third party's use, or the 

results of such use, of any information, apparatus, product or process disclosed in this publication, or represents 

that its use by such third party would not infringe privately owned rights.

I
NUREG/IA-0191

International 

Agreement Report

A Tool for Drawing With Excel 

Prepared by 

A. Progek, B. Mavko, I. Parzer

Institut Joief Stefan 

Jamova 39 

1001 Ljubljana, Slovenija 

Office of Nuclear Regulatory Research 

U.S. Nuclear Regulatory Commission 

Washington, DC 20555-0001 

January 2001 

Prepared as part of 

The Agreement on Research Participation and Technical Exchange 

under the International Code Application and Maintenance Program (CAMP)

Published by 

U.S. Nuclear Regulatory Commission
0@ *0 @0 Institut "Jo.ef Stefan", Ljubljana, Slovenija S

ABSTRACT 

Worldwide the research is conducted how to reduce the user effects on safety analysis in the 

nuclear engineering field. It was shown that user effects could be reduced with automatic 

display of the result. Therefore a program was developed for automatic creation of figures 

with Microsoft Excel 97 with capability to present single curve or multiple curves. Primarily 

the program was developed for the RELAP5 users. However, due to flexibility it can be used 

for graphic presentation of severe accident or user developed codes when the output is in 

columns and ASCII format. The testing of the tool showed that figure can be quickly and 

easily created. The tool conforms also to quality assurance requirements, because all input 

data used are archived. Additionally a figure list is created including titles, boundaries, ranges 

of cells for plotting etc. A huge number of quickly generated figures enable detail analysis of 

certain phenomena, thereby improving the quality of the analysis.

IJS-DP-8278 October 2000 page iii

October 2000 

E:\analize99 - fina1\objave\Nureg\IJS-DP-8278 plot charts.doc page iii
0@ 

Institut "Joief Stefan", Ljubljana, Slovenija 

CONTENTS 

Page 

1. Introduction ................................................................................................... 1 

2. Capabilities of the code .................................................................................... 2 

2.1 W orksheet "Input data" ........................................................................................... 2 

2.1.1 Input of text data ........................................................................................ 3 

2.1.2 Settings ................................................................................................... 4 

2.1.2.1 M ode ...................................................................................................... 6 

2.1.2.2 Boundaries ................................................................................................ 7 

2.1.2.3 Text ................................................................................................................. 7 

2.1.2.4 Graphics ................................................................................................. 9 

2.1.2.5 Data ............................................................................................................... 10 

2.1.2.6 Program .................................................................................................... 11 

2.1.3 M acros ..................................................................................................... 11 

2.1.3.1 Button "Run". .......................................................................................... 11 

2.1.3.2 Button "Figure size". ............................................................................... 15 

2.1.3.3 Button "Save user-defined chart type in template" ............................. 15 

2.1.3.4 Button "Save all options". ..................................................................... 15 

2.1.4 Other ........................................................................................................ 16 

2.1.4.1 Date and tim e ............................................................................................ 16 

2.1.4.2 Paper size ................................................................................................. 16 

2.2 W orksheet "Options" .............................................................................................. 16 

2.3 W orksheet "Inp_figure" .............................................................................................. 17 

2.4 W orksheet "Control" .............................................................................................. 18 

2.5 W orksheet "Temp ....................................................................................................... 18 

2.6 Charts "color", "mono " and "figjsmall "................................................................ 19 

3. Data preparation ............................................................................................ 20 

3.1 General requirem ents for data .................................................................................. 20 

3.2 "Y multiple" data preparation .................................................................................. 20 

3.3 "X-Y" data preparation ............................................................................................ 21 

4. O utput workbook ............................................................................................. 22 

5. Program lim itations ....................................................................................... 24 

5.1 Built-in warnings ..................................................................................................... 24 

6. Literature ............................................................................................................ 28 

7 . In d e x .................................................................................................................. 2 9 

Appendices 

Appendix A: 

AN EXAMPLE OF OUTPUT WORKBOOK GENERATED BY THE PROGRAM ...... A.A-1 

Figures 

Figure 1: The worksheet "Input-data". ................................................................................ 3 

Figure 2: Dialog box for com m and selection ...................................................................... 11 

Figure 3: Dialog box for data location of "Y multiple" data ("Comparison") ..................... 13 

Figure 4: Dialog box for data location of "Y multiple" data ("One curve") ........................ 13 

Figure 5: The worksheet "Inpjigure" ................................................................................. 18 

Figure 6: Dialog box for W arning 29 ................................................................................. 27 

IJS-DP-8278 October 2000 page v 

E:kanalize99 - final\objave\Nureg\IJS-DP-8278 plot charts.doc
Institut "Jo.ef Stefan", Ljubljana, Slovenija 09

Tables 

Table 1: The function of the sheets ....................................... 2 

T able 2: Program settings ........................................................................................................... 5 

Table 3: Data and figure location and legend input as a function of data type, plotting mode 

and figure type when running commands .................................................................... 12 

Table 4: List of settings, stored on worksheet "Options" ................................................... 16 

Table 5: Description of columns for figure input data ........................................................ 17 

Table 6: Required format of "Y multiple" data .................................................................... 20 

Table 7: Selected options for "Ymultiple" data presented in Table 6 ................................ 21 

T able 8: "X -Y ' data ................................................................................................................... 2 1 

Table 9: Selected options for "X-Y" data presented in Table 8 ........................................ 21 

Table 10: Input data used for program execution archived on Worksheet "Output". ......... 22 

Table 11: Information about generated figures stored on worksheet "Figure-list" ........... 22

IJS-DP-8278 October 2000 E.\analize99 - final\objaveNureg\IJS-DP-8278 plot charts.doc

page vi
S0 

O0 

Institut "Jozef Stefan", Ljubljana, Slovenija 

1. Introduction 

Post processing of the calculated data is very important when performing thermal

hydraulic safety analyses. It is known that user effects can be reduced with automatic display 

of the results therefore research is conducted also in this direction. An example of such tool is 

Nuclear Plant Analyzer (NPA) [1]. It was developed for the RELAP5/MOD3 code and higher 

versions while it is not applicable to RELAP5/MOD2. Generally graphical presentation of the 

results is time consuming. 

Therefore in 1997 the macro was developed to create figures for Excel 95 and then 

converted to Excel 97. It was developed primarily for printing. The macro printed on A4 

sheet, landscape oriented, 9 charts with maximum 5 curves each and closed the workbook. 

The advantage of such tool was that many results could be presented graphically with the 

possibility of comparison. The disadvantage of the macro was (because there was no need) 

that linking with other documents was not possible. The reason was because only one sheet 

with nine plots could be saved. Another disadvantage was archiving of the workbooks 

because of the size and automatic renaming of worksheets and workbooks names. Also the 

figures could not be edited. 

In order to present the calculated results graphically in standard format, with clear and nice 

outlook, reducing the possibility of user errors, as well as time and effort needed for plotting 

as much as possible (depending on the processor speed only), the new macro was developed. 

It is intended for creating plots from any safety analysis for the reports, journal paper, 

published papers, diplomas, thesis etc. whenever the data were properly formatted. With the 

macro commands stored in the workbook for drawing with Excel the user can define the style 

of the chart (font size and type, grids, line thickness, chart size, colors etc.). The macro is 

intended for creating large number of similar plots (more than 5), while for generating smaller 

number of plots it may be faster to use Excel commands.

IJS-DP-8278 October 2000 

E \analize99 - final'objave\Nureg\lJS-DP-8278 plot charts.doc

pageI
0@ 

Institut "Jolef Stefan", Ljubljana, Slovenija Ce 

2. Capabilities of the code 

The macros are written in Visual Basic [2, 3]. As interface between the macros and user 

the Excel sheets (worksheets or charts) are used. The workbook for drawing with Excel 

consists of 8 sheets, listed in Table 1. Each sheet has a function, which is also described in 

Table 1. In the next paragraphs the sheets are described and how to enter the input data 

needed for macro execution. 

Important: In Table I the sheets required for macro execution are shown. These sheets can 

be renamed only if the names are changed in the macro source. Any new worksheets or charts 

can be added to the workbook. 

Table 1: The function of the sheets 

Sheet name Function User can change Input data for inputting data needed for macro execution YES 

Options for saving program settings (options) NO' 

Inp_figure for inputting figure text and boundaries YES 

Control text for dropdown menus NO 

Temp for saving and reading values needed for macro execution NO 

color chart style saved on chart "color" YES* 

mono chart style saved on chart "mono" YES* 

fig_small chart style saved on chart "fig small" NO+ 

+ only when default style is changed 

* when user change chart style 

2.1 Worksheet "Input data" 

The worksheet "Input data" is shown on Figure 1. Each cell used has a color depending 

on the function. The text input data are input in yellow cells, while their description is in light blue cells above, bellow or near yellow cell. The settings (options) are selected from drop down menus. Macros are run with the command buttons. 

The text input is: 

"* the data filenames with pathname and file extension, 

"* the workbook name and path, in which the data and/or figures are saved (output 

workbook), 

"* header text for A4/letter sheet, 

"* x-axis boundaries for quick input (same boundaries for all figures), " title and boundary worksheet name (the worksheet, worksheet name and text are input by 

the user). 

Program settings are selected from 20 dropdown menus in column E. All settings can be 

saved with the command button "Save all options" and later retrieved by last, 21 st dropdown 

menu. There are six option groups: 

"* Mode (the mode for reading data and plotting figures), 

"• Boundaries (specifying boundaries for x and y axis); "* Text (figure title, x- and y-axis title, the worksheet with figure text and legends); "• Graphics (style, format and figure type. For figure type "small" the number of figures in 

one row can be selected); 

IJS-DP-8278 October 2000 page 2 E:\analize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc
S.00 

Institut "Jolef Stefan", Ljubljana, Slovenija

"* Data (data type, title row, first and second row for axis title, first data row, number of 

rows to skip from bottom for plotting); 

"* Program (saved options). 

Each group has its own color. 

Macros are run with left mouse click of command button. Command buttons are within 

white box (column F) and are the following: 

"* Run (macro for reading data and creating figures), 

"* Figure size (macro for figure size), 

"• Save user-defined chart type in template (macro for saving chart style), 

"* Save all options (it saves all program settings, called also options). 

Besides program settings and macros other possibilities are also check box for selection of 

logo and date to be printed in the left bottom comer of the figure and dropdown menu for 

paper size selection (A4 or letter).

8 C O E

Figure 1: The worksheet "Inputdata"

2.1.1 Input of text data 

Data filenames. Information for filenames is input in yellow cells in columns A to C. 

Full filenames are input different for plotting modes "One curve" and "Comparison". 

For the plotting mode "One curve" pathname, filename and file extension are input 

separately in the columns with the name "Directory", "Filename" and "File extension "; at

IJS-DP-8278 October 2000 page 3

E'\anahlze99 - finaiobjave\Nureg'dJS-DP-8278 plot charts.doc
0°0 Institut "Jo.ef Stefan", Ljubljana, Slovenija :0. 

the end the user inputs also "Y" in the column "Open (Y/N)" (case insensitive) to open the data file. If the user does not input "Y", the data file is not opened nor figures are created. The number of data file rows is limited to 100. Data files can have one independent variable (first column, usually time) and from one to nine dependent variables. 

Another option is to compare plots. In such a case the plotting mode "Comparison" is selected. In the plotting mode "Comparison" different calculations are compared. The file extension is prescribed (sequential numbers from 01, 02,..,25). This numbers are used to input only one filename for data files (maximum 25) of the same calculation. In such a case, the C column ("Legend") is used to input legend names. Altogether 20 different calculations can be compared. The number of data files is selected by dropdown menus placed in cell E13. First dropdown menu is for selecting first data file extension and second for the last data file extension (first number 3 and last number 5 mean that selected filenames are 'file".03, "file".04 and 'file".05). For inputting filenames little text is needed. In the plotting mode "Comparison" it is also possible to generate figures with one curve. This is very useful, when the files have different file extensions (instead of inputting 25 rows for loca.01, loca.02,..., loca.25 one row is sufficient when the plotting mode "Comparison" is selected and 1 for first 

file extension and 25 for last file extension). 1important: In the plotting mode "Comparison" the column C is used for legend names whileI 

Sin the plotting mode "'One curve" for file extensions. 

Output workbook. The path and filename of output file (workbook) are input in cells E2 and E4. Extension "xls" is added automatically, therefore file extension is not input. 

The macro during execution opens new workbook or overwrites old workbook if the workbook with the same name exists. In the later case a backup copy is made (with file extension "xlk"). The user must save manually the new output workbook. 

Important: If output workbook already exists, it must be saved under new filename (with command SaveAs), because in the opposite the original workbook is lost. Therefore the 

command SaveAs is recommended for saving. 

Header text. In the cell E6 header text is input. The header text can be used for description of calculation or other. 

x-axis boundaries. When the boundaries are the same for all figures (usually time), they are input in cells F7 to F 10. In this way the user avoids time-consuming input of boundaries for each figure in the separate worksheets. This option is very useful when the user is interested to analyze certain time interval. This input works only for option "quick input". 

Title and boundary sheet name. The user inputs the worksheet names. First new worksheet is created, then it is optionally renamed, and finally figure text and boundaries are input. Such worksheet can be used for figure text. However the worksheet name must be input in one of the cells from G2 to G2 1. 

Legend. Legend is input in light yellow cells. In the plotting mode "Comparison" this is column C, while in the plotting mode "One curve" the cell F6. The color of cells is automatically changed to light yellow to denote that will be used as input. 

2.1.2 Settings 

There are 20 settings in 6 groups. Besides the mode also options for boundaries, text, graphics and data are available. In the last group are program settings, selected by the user previously. This setting is not used for macro execution but other settings can be selected by

IJS-DP-8278 October 2000 E:\analize99 - final\objave\Nureg.\IJS-DP-8278 plot charts.doc

page 4
0@ S. S. Institut "Jo.ef Stefan", Ljubljana, Slovenija 0

this selection. The program settings are listed in Table 2. If the setting is not applicable in 

certain mode, N.A. is displayed and the default values are used during macro execution. 

Table 2: Program settings 

Name Variable in Options 

the code 

Mode 

Plotting mode kazprimerjava Comparison 

One curve 

First file ext. kaz dat z from 01 to 25 

Last file ext. kaz dat k from 01 to 25 

Boundaries 

x-axis boundaries kaz x m auto 

input 

quick input 

y-axis boundaries kaz_y_m auto 

I Textinput 

Text 

Figure title kaz-napt auto 

input 

input+auto title 

input+auto x-axis title 

input-auto y-axis title 

input+filename* 

input+auto y+filename* 

no 

x-axis title kaz-napx auto 

input 

input+auto 

no 

y-axis title kaznapy auto 

input 

input+auto 

no 

Title and boundary sheetname kaz strip the name of worksheets input 

into cells G2 through G21 

Legend kazilegenda Yes 

No 

Graphics 

Style selection (for figure type large only) kazstil color 

mono 

Figure format kazpic Picture 

Chart 

Figure type kazvel large 

small 

No. of figures in row (for figure type kaz niz from I to 6 

small only)

IJS-DP-8278 October 2000 

E~kanalize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc page 5
0. 

00 Institut "JoIef Stefan", Ljubljana, Slovenija 0.5 

Table 2: Program settings (continued) 

Name Variable in Options the code 

Data 

Data type kaz_podatki Y multiple 

X-Y 

Figure title row kaz tr from 0 to 10 

First and second row for axis title kaz lfr, kaz lsr from 0 to 10 

First data row kaz dfr from 1 to 11 

No. of bottom data rows, skipped for kazdskip from 0 to 3999 

drawing 

Program Saved options ] kaz opcije the name for options is 

prompted when running the 

button "Save all options" - filename or worksheet name 

2.1.2.1 Mode 

Plotting mode. With this option is selected to generate figures with one or multiple curves 

what mean comparison. The mode has influence on the naming of data files. 

a) Comparison: This option enable comparison of calculations. Maximum number of 

calculations is 20 (arbitrary chosen with possibility to increase to 255). On the figure 20 curves are presented labeled with legend. If legend is not input, the legend names 

are determined by Excel (Series 1, Series2,...,Series20). 

For data files maximum 25 filenames can be input for one case. When option "Comparison" is selected, two dropdown menus can be used. With these dropdown 

menus the first file and last file extension are specified (from 01 to 25 is standard 

format of file extension). The Microsoft Excel 97 limits the maximum number of charts (depending on the 

memory, but maximum 256 in one workbook). When the number of charts is greater, the solution is to put all charts on single worksheet and to select format "Picture" or to 

generate charts by pieces and save them in several workbooks. 

b) One curve: It is intended to generate figures with one curve. The plotting mode is equivalent to the plotting mode "Comparison" with one data file, but the difference is 

that in this mode the filename is not prescribed. First file ext. The user selects between a number between 01 and 25. First file extension 

must be smaller than last file extension, otherwise warning is displayed and the last file 

extension is set to the value of first file extension. 

Last file ext. The user selects between a number between 01 and 25. Last file extension 

must be greater than first file extension, otherwise warning is displayed and the last file 

extension is set to the value of first file extension. 

IJS-DP-8278 October 2000 page 6 

EAanalize99 - final\objave\Nureg\IJS-DP-8278 plot charts.doc
00 

Institut "Jozef Stefan", Ljubljana, Slovenija 

2.1.2.2 Boundaries 

For each figure it is necessary to select the x- and y-axis boundaries. One option is to 

specify the boundaries by user and another is to leave Excel to automatically select the 

boundaries. 

x-axis boundaries. There are three options, how to input boundaries for x-axis: 

a) auto: Boundaries selected by Excel automatically. 

b) input: To input the x-axis boundaries on the worksheet with figure text and boundaries 

(see "Title and boundary sheetname", page 8) for each figure. 

c) quick input: The values for x-axis boundary (minimum and maximum scale, major and 

minor unit) are input in cells F7 to F10. These values are used for all figures. 

y-axis boundaries. There are two options, how to input boundaries for y-axis: 

a) auto: Boundaries selected by Excel automatically. 

b) input: To input the y-axis boundaries on the worksheet with figure text and boundaries 

(see "Title and boundary sheetname", page 8) for each figure. 

2.1.2.3 Text 

Each figure may have figure title, x-axis title and y-axis title. This titles can be input by 

user on title and boundary worksheet (see "Title and boundary sheetname", page 8) or to be 

read automatically from data file (in case of RELAP these are component names), with option 

to define the title rows. Another option is to combine titles from worksheet "Title and 

boundary sheetname" with titles from data files. 

Figure title. There are eight options. 

a) auto: It means, that figure title is read from data file. In data settings it is selected the 

row with figure title. When comparing the results the title from first data file and data 

worksheet is used, respectively. 

b) input: The figure titles are input on title and boundary worksheet (see "Title and 

boundary sheetname", page 8) in column C. First figure title is input in second row, 

second figure title in third row etc. 

c) input+auto title: Same as "input" with additional figure title in parenthesis read from 

data file. This option is useful when figure title is not displayed along y axis and 

information is desired on figure. 

d) input+auto x-axis title: Same as "input" with additional x-axis title in parenthesis read 

from data file. This option is useful when x-axis title is not displayed along x-axis and 

information is desired on figure. 

e) input+auto v-axis title: Same as "input" with additional y-axis title in parenthesis read 

from data file. This option is useful when y-axis title is not displayed along y-axis and 

information is desired on figure. 

t) input+filename: Same as "input" with additional filename. This option is useful when 

information about filename is desired on figure. 

,a) input+auto v+fllename: Same as "input" with additional y-axis title in parenthesis read 

from data file and filename. This option is useful when information about filename is 

desired on figure. 

h) no: Figure has no figure title. 

x-axis title. There are four options. 

a) auto: It means that x-axis title is read from data file. In data settings are selected two 

rows (or one) from which x-axis titles are read. If "no" is selected this means no text

IJS-DP-8278 October 2000 

E:•analize99 - final\objave\Nureg'dJS-DP-8278 plot charts.doe page 7
00 

Institut "Joief Stefan", Ljubljana, Slovenija 0o0 

for x-axis title. In the case of more curves the x-axis title is read from first data file 

only and first worksheet, respectively. 

b) input: The x-axis titles are input on worksheet with titles (see "Title and boundary sheetname", page 8) in column A. First x-axis title is input in second row, second x

axis title in third row etc. 

c) input+auto: Combination of "auto" and "input" options. After x-axis title from 

worksheet with titles the x-axis title from file in parenthesis is written. 

d) no: Figure has no x-axis title. 

y-axis title. There are four options. 

a) auto: It means that y-axis title is read from data file. In data settings are selected two 

rows (or one) from which y-axis titles are read. If "no" is selected this means no text 

for y-axis title. In the case of more curves the y-axis title is read from first data file 

only and first worksheet (for data stored on worksheets). b) input: The y axis titles are input on worksheet with titles (see "Title and boundary sheetname", page 8) in column B. First y-axis title is input in second row, second y axis title in third row etc. 

c) input+auto: Combination of "auto" and "input" options. After y-axis title from 

worksheet with titles the y-axis title from file in parenthesis is written. 

d) no: Figure has no y-axis title. 

Title and boundary sheetname. For inputting the values default sheet "Inp_figure" is 

provided. First row describes the variable that may be input while next sequential rows are for 

inputting values. For details see Section 2.3. 

New worksheets are simply created by inserting a new worksheet, and then values are 

input as described above. At the end the worksheet may be renamed and the worksheet name 

must be input in one of cells from G2 to G21. The worksheet name will then appear in the 

drop down menu (in cell E21) and can be selected from the list. In total 20 worksheet names 

can be input. The worksheet names can be input or deleted by user. It is also recommended to 

copy first row from worksheet "Inpjigure" to new inserted worksheet. 

Important: After drawing figures worksheet "Figure_list" is generated with the titles and 

boundaries to be used for figures. Therefore it is recommended to draw figures with "auto" 

options for titles and boundaries and then from the list create a title and boundary worksheet. 

Legend. There is option to display legend or no. For plotting mode "One curve" legend 

name is input in cell F6, while for plotting mode "Comparison" in the column C. When 

plotting mode is changed warning is displayed with information about legend names (number, 

name and location). 

Plotting mode "One curve": In plotting mode "One curve" legend is input in cell F6. If the 

cell is empty, the legend name is defined by Excel automatically in case of data type "Y 

multiple". If data are "X-Y" type, the legend name is equal to the name of worksheet with 

data. 

Plotting mode "Comparison": In plotting mode "Comparison" the legends are directly input for "Y multiple" data type (legends in column C). The first legend is the value with 

the first "Y" in column D, second with the second "Y" etc. If cells are empty, Excel 

defines the legend names. For "X-Y" data type the comparison is possible only for data 

stored on worksheet. The legend name is equal to the worksheet name by default (since no 

input for data filenames is required). To indirectly define the legend the user can rename 

the data worksheets appropriately and then generate figures.

IJS-DP-8278 October 2000 

E \analize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc page 8
0@ 

Institut "Jozef Stefan", Ljubljana, Slovenija ee 

2.1.2.4 Graphics 

The user can choose between different chart styles, figure formats and types of figures. If 

the figure type is "small" (mean multiple figures on the worksheet), the user can define the 

number of figures in one row on the screen (n=1,2,...6). At the same time on the A4/letter 

landscape page n times n figures will be printed. The chart style is defined on chart sheets as 

described in Section 2.6. The chart can be designed free by user, however it is necessary to 

save chart style (see Section 2.1.3.3). 

Style selection. For figure type "large", it can be selected "color" or "mono" chart style, 

while for "small" figure type the default style is 'figsmall". 

a) color: It means "color" chart style on worksheet "color", which will be described later. 

The option is not applicable for figure type "small", which has by default 'fig_small" 

chart style. 

b) mono: It means "mono" chart style on worksheet "mono", which will be described 

later. The option is not applicable for figure type "small", which has by default 

"figjsmall" chart style. 

c) fig small: It means 'figjsmall" chart style on sheet 'figismall". The option is not 

applicable for figure type "large". 

Figure format. The user can select between "Picture" and "Chart" figure format. If figure 

format "Picture" is chosen, the figure is no more linked with the data and figure titles and 

boundaries cannot be changed in Excel. 

a) Picture: Excel figure is converted from "Chart" to "Picture" in order to reduce space, 

but in such a case the links with data are lost. The advantage of "Picture" format is that 

Excel can generate more figures with "Chart" format before Excel message "Not 

enough memory" is displayed (when figures placed on one worksheet). How to 

generate figures on one worksheet see Section 2.1.3.1. The program is arbitrary limited 

to maximum 600 figures. 

b) Chart: This is normal Excel figure format, which can be edited after generation. 

Figure type. The user can select between "large" and "small" figure type. The difference is 

that each figure of type "large" (the figure can be very small) is printed on its own sheet 

(from chart or worksheet) while in case of "small" up to 36 figures can be printed on single 

landscape A4/letter sheet. 

Large: Each figure is on its own chart or figures are placed on worksheets printed on 

separate A4/letter sheets. 

small: With the command "Read data and create figures" 9 figures are generated on one 

worksheet. The number of figures is equal to the number of dependent variables in the 

columns on the worksheet. If there is only one dependent variable on the worksheet one 

figure will be generated. With the command "Create figures from data" all figures are 

generated on one worksheet. However when printing from 1 to 36 figures will be printed 

on single A4/letter sheet, landscape oriented, depending from "No. of figures in row" 

setting. 

No. of figures in row. The user can select from I to 6 figures in one row. On A4/letter 

sheet, landscape oriented from 1 to 36 (6 times 6 matrix) figures are printed. The chart style 

from worksheet 'figsmall" is valid for matrix 3 times 3, when at scaling the font sizes are 

default values from the program (matrix different from 3 times 3). 

IJS-DP-8278 October 2000 page 9 

E:\analize99 - final\objave\Nureg\IJS-DP-8278 plot charts.doc
00 

Institut "Jo.ef Stefan", Ljubljana, Slovenija 0.0 

2.1.2.5 Data 

For data there are five settings: "Data type", "Figure title row", "First and second row for 

axis title", "First data row" and "No. of bottom data rows, skipped for drawing". 

Data type. The user can select between "Y multiple" and "X-YI' data type. 

Y multiple: The data of "Y multiple" data type have in the first column independent 

variable and in the next columns dependent variables (maximum 9 for reading). The "Y 

multiple" data type preparation is described in Section 3.2. The data of "Y multiple"type with one independent variable are equal to data of "X-1' type, but there are differences 

when reading data and generating figures (see Table 3). 

X-Y: The data of "X-1' data type have one independent and one dependent variable. More 

detail description of"X-I" data type is in Section 3.3. 

Important: At the first moment it seems that there is no difference between reading one 

variable with the "Y multiple" or "X-Y' data type option. However, the difference is in the 

data stored on the worksheet. For the "Y multiple" data type with data stored on one 

worksheet is independent only the variable in column A and all dependent variables have the 

same number of data. On the other hand, for "X-Y" data type each dependent variable has its 

own independent variable (odd column are independent variables and even columns 

dependent variables). This means that each pair of variables can have different number of 

data. Also when reading data with "Y multiple" and "X-Y" on common sheet only first 10 and 

2 columns are stored on worksheet by default, respectively. 

Figure title row. The user can select numbers from 1 to 10. If user selects "no" this mean, 

that there is no data row with figure titles. The values from 1 to 10 means the row with figure 

titles in data file (5 mean the fifth row). This value must be specified when auto (or 

combination with auto) option is selected. 

First and second row for axis title. The user can select numbers from 1 to 10. If user 

selects "no" this mean, that there is no data row with axis titles. The values from 1 to 10 

means the row with axis titles in data file (6 mean the sixth row). It is possible to select two 

rows (for example one for variable name and another for units). 

First data row. This is very important information. The user can select between 1 and 11. 

This means that maximum number of header rows is ten. If user selects 1 it means that all 

rows are data. In such case all title rows must be set to "no" otherwise warning is displayed 

and execution terminated. The values from 1 to 11 means the first row with data in data files 

(11 means the eleventh row). Important: This input datum is important both for reading and drawing. For reading it is[ 

important because macro checks if data in that row really exists. For drawing it is important 

because based on this datum the range for drawing is determined. Wrong information for first 

data row can cause wrong execution of the macro or error termination. 

No. of bottom data rows, skipped for drawing. The user selects number between I and 

4000 and "no". Option "no" means that no data rows are skipped for drawing. The number 

means the number of rows to be skipped. This option prevents macro execution from 

interruption if any text rows follow data rows (numeric values). 

IJS-DP-8278 October 2000 page 10 

Ekanalize99 - final\objave\Nureg\IJS-DP-8278 plot charts.doc
*0 0@ 

Institut "Jozef Stefan", Ljubljana, Slovenija 0 

2.1.2.6 Program 

Saved options. In the drop down menu the saved combinations of options can be selected. 

To save a new combination of settings the user first selects settings and then save them with 

command button "Save all options ". This last setting is not used for macro execution but for 

selecting the first 20 settings, which are used during macro execution (see Figure 1). 

Important: The saved options are assigned when a drop down a menu is activated and a 

combination is selected. If the user further change the settings they may differ from the saved 

-settings even if it is displayed that certain combination is selected. 

2.1.3 Macros 

Macros are run with left mouse click. The command buttons are in white box in column F. 

The command buttons to run the macro are: "Run", "Figure size". "Save user-defined chart 

type in template" and "Save all options". 

2.1.3.1 Button "Run" 

With command button "Run" the main drawing macro is run to read the data, read the data 

and create figures or create the figures. For reading data and creating figures there are two 

commands which are different by inserting the data on worksheet and locations for figures. 

After clicking the button "Run" the dialog box shown on Figure 2 is displayed. Options are 

the following: 

"* Read data (Command 1), 

"* Create figures from data (Command 2), 

"* Read data and create figures (Command 3), 

"* Read data + Create figures from data (Command 4 - combination of first and second 

command). 

r Read data (1) : Create figures from data (2) 

Read data (1) 

':Read data and ceate:' + 

fges(3) i Create figures from data (2) 

statt Cancel 

Figure 2: Dialog box for command selection 

The execution of the commands depends on the data type, plotting mode and figure type as 

shown in Table 3. Depending on the combinations the location of data in the workbook, 

location of figures (chart or worksheet) and cells to input legend are changed. When the data 

type is "Y multiple" function all four commands. When the data type is "X-Y' Command 3 is 

disabled and Command 4 works only in the plotting mode "One curve". Limitation for 

Command 3 is intentional to prevent situation with one figure on a sheet and data files with 

one dependent variables stored each on their own sheet. In the plotting mode "Comparison" 

with data type "X-Y' function only Command 2 because it is not expected that the filenames 

with one dependent variable will differ only by extension. Nevertheless it is able by proper 

combination of reading and drawing (Command I and Command 2) and manual renaming of 

IJS-DP-8278 October 2000 page II 

E:\analize99 - final\objave\Nureg'IJS-DP-8278 plot charts.doc
Institut "Jo.ef Stefan", Ljubljana, Slovenija

00

worksheets to get all options. It is expected to prepare the data of type "X-Y' manually (by the 

user) or read them in the plotting mode "One curve".

Important: It is important to note that for data of type "Y multiple" the result is the same with 

mode "One curve" or "Comparison", the difference is only in the location of stored data in the 

workbook and in inputting legend names. 

Important: In the case of data with one independent and one dependent variable and the 

same number of data rows any data type can be chosen ("Ymultinle" or "Y- ')h

Table 3: Data and figure location and legend input as a function of data type, plotting mode and figure type when running commands 

Data type Plotting Command Data Figure type (location) Legend mode (location) small large (input) 

Y multiple Comparison 1 option77- NA. N.A. N.A 

2 N.A. one option(3) column C 

worksheet( 2 _ 

3 own more own chart column C 

worksheet worksheets(41 

4 one one own chart column C worksheet(5) worksheet(2 

One curve I option(6) NA. N.A. N.A 

2 N.A. one optionU3 cell F6 

worksheet_2) 

3 own more own chart cell F6 

worksheet worksheets(4) 

4 own one own chart cell F6 

worksheet worksheet(2) 

X-Y Comparison I X717-- X-(7) XF37 

(W 201) 

2 N.A. one option(" automtatics) 

worksheet(') 3" (V7)X( X( 

(W 203) 4* V(7) V(7) V7) X(7• 

(W 205) 

One curve 1 option, N.A. N.A. N.A 

2 N.A. one option(3) cell F6(1

" 

worksheet(2) 3" V7) X(7) V•7) X(7) 

(W 204) __ _ __ _ _ 1 

4 one one own chart F6 

I worksheett 0° worksheet(2 _ (1) • -. •1^ *--.-. A....[..a ... n o a e o t s c i n r o m eca~ta file in¢serted on separate worksheet or data f'iles of the same calculation inserted on common worksheet 

(2) all figures placed as an embedded objects on a worksheet "Fig small" 

(3) all figures placed as an embedded objects on a worksheet "Fig large" or each figure placed on a separate 

chart 

(4) worksheet names are "Figsmall 1I", "Figsmall_2" .... " Figsmall_20" (5) data files of the same calculation inserted on common worksheet 

(6) each data file inserted on separate worksheet or data files of the same calculation inserted on common 

worksheet "Data" 

(7) X means that command is not executed 

( the legend name is the name of worksheet with stored data

IJS-DP-8278 October 2000 

E.'ma]ize99 -final\objave\Nureg\JJS-DP-8278 plot charts.doc page 12

I
09 

Institut "Jolef Stefan", Ljubljana, Slovenija 0.0 

(91 each data file inserted on separate worksheet or all data files inserted on common worksheet "Data" 

H0) all data files inserted on common worksheet "Data" 

() if the cell is empty, the legend name is name of worksheet with stored data 

in parenthesis the warning (W) number is specified. For details refer to Section 5.1. 

Read data. The macro will insert the data from input data filenames on worksheets. In the 

plotting mode "One curve" the data files of "Y multiple- data type are inserted on a separate 

worksheets where the data filename is assigned to worksheet name or data files of the same 

calculation are inserted on common worksheet with worksheet name "Data". In the plotting 

mode "Comparison"' for "Y multiple" data type each data file is inserted on separate 

worksheet (worksheet name is data file name) or data files of the same calculation are inserted 

on common worksheet with name equal to common data filename without extensions plus 

first and last data file extension (for example "local.02-04" when "local" is common 

filename and first file extension is "02" and last file extension is "04"). On the screen the 

dialog box "Data for one case on one sheet?" is displayed (see Figure 3). With mouse the user 

selects option "Yes" or "No" and then clicks button "Continue". If "Cancel" button is clicked 

the execution is terminated. 

-Te No 

ConrKiue Cancel 

Figure 3: Dialog box for data location of "Y multiple" data ("Comparison") 

In the plotting mode "One curve" the user chooses between inserting all data files on common 

worksheet "Data" or to insert each data file on a separate sheet. On the screen the dialog box

"Data on one sheet?" is displayed. With mouse button "Yes" or "No" or "Cancel" is clicked. 

If button "Yes" is clicked, data are stored on the same worksheet. In the program it is assumed 

that each dependent variable has the same dependent variable. If this is true the user can click 

this button. In the opposite button 'No' is clicked and each data file is stored on separate 

worksheet. When button ))Cancel<< is clicked, the execution is terminated.

I... Dat onoxset

Do ymu want al data on one sheet? 

In such a case each data file must have the same x-axs data 

Select No. if -axes ae 6fterent. 

Figure 4: Dialog box for data location of "Y multiple" data ("One curve") 

Tip: For reading data files the plotting mode "One curve" is used when the filename is free. If 

the user wants to compare curves, it is recommend to insert data files on common worksheet. 

In the next step the figures are generated with command "Create figures from data" in the 

plotting mode "One curve" or "Comparison". 

Important: The plotting mode "One curve" is for reading more general than the plotting 

mode "Comparison". However it requires more input. 

IJS-DP-8278 October 2000 page 13

E:\analize99 - final',objave\Nureg'JJS-DP-8278 plot charts.doc
00 

Institut "Jozef Stefan", Ljubljana, Slovenija 000 0.0 

Create figures from data. The macro will generate figures for open workbook when in the 

cell E4 on worksheet "Input data" the opened workbook name without extension is input. More workbooks can be open at the same time. However figures are generated only for the 

workbook specified in the cell E4. 

Data type "Y multiple": If the plotting mode "One curve" is selected, the macro generates figures with one curve for data stored on all worksheets. If the plotting mode 

"Comparison" is selected, the data can be stored in the 20 worksheets for which the curves 

are compared. If there are more than 20 worksheets in a workbook the figures will be 

generated for the first 20 worksheets. Further, each worksheet can contain 256 columns 

where first column is independent variable and other columns are dependent variable. 

Besides the worksheets with stored data are allowed only worksheets created by macro 

automatically. Other non-data worksheets must be deleted. 

Data type "X-Y": If the plotting mode "One curve" is selected, the macro generates figures with one curve for all worksheets with stored data. Maximum number of dependent variables is 128 what gives 256 columns (maximum number limited by Excel). If the 

plotting mode "Comparison" is selected, maximum is 20 worksheets with stored data. If 

more than 20 worksheets in a workbook the figures will be generated for the first 20 

worksheets. On each worksheet maximum number of columns is 256, odd columns are 

independent variables and even columns are dependent variables. 

Good property of this command button is that data can be prepared in advance. The only requirement is to have data in columns. Acceptable data format is described in Section 3. 

For maximum number of figures in a workbook refer to Excel limitations in Section 5. 

This limitations are not applicable for "Picture" figure format with figures embedded on 

common worksheet (at figure type "large" the button "Yes" is selected, when dialog box 

"One sheet for figures" is displayed). In this case all figures are placed on one worksheet what 

makes printing easier (arbitrary limitation is 600 figures). The worksheet names are "Figjlarge" and "Fig_small" for figure types "large" and "small", respectively. 

The command "Create figures from data" can be run more times (for example generates both "small" and "large" figures. If chart exists the warning is displayed that it already exists 

and the letter A will supercede the chart name (Warning 12). To continue macro execution 

button <Enter> must be pressed. When figures are located on the worksheet, the Warning 10 

(for "large") and 11 (for "small") is displayed that worksheet must be renamed or deleted by 

the user manually. 

Important: Option "One sheet for figures" is recommended when the user wants to have all 

figures on one worksheet because of printing. 

Read data and create figures. The macro read the data files and store the data on 

worksheets and create figures. The data from data file are always stored separately on their 

worksheet. The plotting mode "One curve" differs from "Comparison" for one curve in that 

data filenames may be free. In the plotting mode "Comparison" the filenames are prescribed what reduce the possibility to compare different data (for example different calculations). 

For figures of type "small" the figures generated from data stored on the worksheet will be 

placed on separate worksheet. For figures of type "large" each figure will be placed on 

separate chart. If the user wish to define the location of data and figures in the output workbook, the data must be read separately and then the figures are generated. 

Important: This command works only for "Y multiple" data.

IJS-DP-8278 October 2000 E:\analize99 -fina]\objave\Nureg\lJS-DP-8278 plot charts.doc page 14
0* 

Institut "Jozef Stefan", Ljubljana, Slovenija 

Read data + Create figures from data. This command first executes "Read data" 

command and then "Create figures from data" to save time when there is no need to edit 

data. The command differs from "Read data + Create figures from data" as shown in Table 3. 

When data are "X-1' type and mode is "Comparison" the command is not executed. 

Important: All figures of type "small" are always placed on one sheet what simplifies 

printing. The advantage compared to printing multiple sheets is in duplex printing. 

2.1.3.2 Button "Figure size" 

This button sets the figure size on charts "color" and "mono", i.e. for figures of type 

"large". In our case the figure size is outer border of object copied to some application (for 

example wordprocessor Word). The advantage of fixed figure size is that defined font size for 

the report can be assigned to figure if original figure is not scaled. 

Plot area size, font size, font type, color, line style, color and weight, titles position, legend 

position, grids etc. are defined by user. 

m rant: After any modification the chart style must be saved in template with command 

Jbutton "Save user-defined chart type in template" in order to be used for figure style. 

2.1.3.3 Button "Save user-defined chart type in template" 

To modify the chart style the figures on "color", "mono" and 'figsmall" sheets are 

modified and then command button "Save user-defined chart type in template" on worksheet 

"Input-data" is clicked by mouse. It is recommended to save original chart style before new 

chart style is saved. The chart style must be saved under same name as chart ("color ", 

"mono" or 'figsmall "). Chart styles are for Windows NT 4.0 (for personal computer) saved 

in the file (if path was not set otherwise at Microsoft Office97 installation): 

C:\Prow-am Files\Microsoft Office\Office\ Xlusrgal.xls 

Important: When the drawing program is first run in Excel (after the workbook for drawing 

with Excel is opened), it must be first run macro with command button "Save user-defined 

chart type in template" placed on worksheet "Input data" to save the chart style in template 

Xlusrgal.xls. If macro is not run the warning will be displayed. 

The chart style can be saved manually. First Excel is run, then it is opened workbook for 

drawing with Excel, one by one the charts "color", "mono" and 'fig small" are selected and 

on each chart the following commands are performed: 

The chart is selected, right mouse button is clicked and we select "Chart Type", then tab 

"Custom Types". On tab "Custom Types" the option button "User-defined" is selected, then 

the command button "Add" is clicked, and in the textbox "Name" the chart name is inputted 

(for example "color"). If the above mentioned chart styles would not be added in template, 

the macro execution will be terminated because of missing chart style. 

2.1.3.4 Button "Save all options" 

By clicking macro button "Save all options" the 20 settings listed in Section 2.1.2 (except 

last "Saved options") are saved. When the command button is clicked, the dialog box is 

displayed to input the name of the collection of settings. These settings are stored on 

IJS-DP-8278 October 2000 page 15 

E:\analize99 - final\objave\Nureg\IJS-DP-8278 plot charts.doc
Institut "Jo.ef Stefan", Ljubljana, Slovenija

00

worksheet "Options". The skilled user can directly change the values on worksheet "Options". Second possibility is to change the setting and then again store the settings on the worksheet with command button "Save all options" under same name. The macro will prompt the user 

to overwrite the old settings. 

2.1.4 Other 

2.1.4.1 Date and time 

When check box is selected, located in cell F3, IJS-RED logo with date and time is shown on 

the figure in left bottom comer. The position of text box and font size is fixed and can be 

changed inside the code only. 

2.1.4.2 Paper size 

For selection of paper size is intended the dropdown menu in cell G23. This option is 

applicable only when figures are put on single worksheet. There are two options: A4/letter 

format (210 x 297 mm) or letter format (21.59 x 27.94 mm). The paper is oriented landscape. Before printing all this settings can be changed by user. 

2.2 Worksheet "Options" 

On this worksheet in each row one collection of settings is stored. First row is description 

row, the rows 2 to 31 are for storing the collections of settings (options) as shown in Table 4. 

Table 4: List of settings, stored on worksheet "Options" 

Column number Option 

I1 Option group name 

2 x-axis boundaries 

3 y-axis boundaries 

4 Figure title 

5 x-axis title 

6 y-axis title 

7 Style selection (for figure type large only) 

8 Plotting mode 

9 First file ext. 

10 Last file ext. 

11 Figure format 

12 Figure type 

13 Data type 

14 Title and boundary sheetname 

15 No. of figures in row (for figure type small 

only) 

16 Legend 

17 Figure title row 

18 First row for axis title 

19 Second row for axis title 

20 First data row 

21 No. of bottom data rows, skipped for drawing

IJS-DP-8278 October 2000 

E:analize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc page 16
Institut "Jolef Stefan", Ljubljana, Slovenija

00 

00 

%6

2.3 Worksheet "Inpfigure" 

On the worksheet "Inp_figure" shown in Figure 5 the titles and boundaries are input. To 

use the titles and boundaries for figure generation, in the drop down menu "Title and 

boundary sheetname" the worksheet must be selected (for example "Inpfigure") and option "input" for titles and boundaries. How to create new worksheet with titles and boundaries, see 

2.1.2.3. If the cell is empty, the title is not shown on the figure. If one of the boundaries is 

missing all boundaries for that figure are determined automatically by Excel. 

Table 5: Description of columns for figure input data 

Column name Description 

x-axis title title on x axis 

y-axis title title on y axis 

Figure title figure title 

y-axis min. scale lower y axis boundary 

y-axis max. scale upper y axis boundary 

y-axis major unit major unit on y axis (axis text) 

y-axis minor unit minor unit on y axis 

x-axis min. scale lower x axis boundary 

x-axis max. scale upper x axis boundary 

x-axis major unit major unit on x axis (axis text) 

x-axis minor unit minor unit on x axis 

For each figure these 11 data must be input in the row. For 9 figures 9 rows must be input. The order of the titles is the same as order of the figures. Figures of type "large" are placed on 

charts 01, 02,..,N, if N is the figure number. Figures of type "small" has in the plotting mode 

"One curve" before the number (file extension of strip file) the preceding text "Fig_small_"; 

for data from first worksheet the figures are therefore placed on worksheet "Figsmall 01", 

for data from second worksheet on worksheet "Fig_small_02" etc. In the plotting mode 

"Comparison" all figures of type "small" are embedded on worksheet "figsmall " and are 

named 01, 02,...N, where N is the number of figures. 

Important: The sequence of titles is the same as figure sequence. This is very important when figures are generated with command "Create figures from data ". For this command the 

recommended option for titles is "auto" (titles are stored (or can be entered) with data).

IJS-DP-8278 October 2000 page 17

October 2000 E:analize99 - final\objave\Nure~g•lJS-DP-8278 plot charts.doe page 17
00 

Institut "Joief Stefan", Ljubljana, Slovenija 

EL- _ r.- FV "Q I-D..ta-- 

A 9 C • •D • ' . ••E,. .; .• •I • " ,I ' , 1 , , " J, , , K -2..:1= 

1 :x-a tKe ads title FAwe tte y-ans mm. scay-ixi r.sc y.asrI uy-axs rnm uniix-aos mm.nscakx-axis max scMx-isnw uri.miso "Kx. n i it 

2 ime (s; vanlaole ,g 1 0 10 31 _ 5 

3 rTimes Vans 2 2e F!g 2 0 10 1 31 0 2000 500 50 

4 ýTime s va 0e 3 Fg 3 0 10 13 1 2 2000 500 50 

A'Trne Is) vaisle 4 Fg 40 10 1 3 1 0 2000 500 50 '-:Time s) anasIe5 Flg 5 0 10 31 0 2200 500 0 "7 ZTre (s) varasle 6 Fg 0 2000 500 50 

8' Time (s) Vanable 7 Fig 7 101d 1 D1 200 500 50 

• gi Time (s) Variable 7 Fig 5 0 10 1 1 0 2000 500 00 V-Time varable 0 Fg 0 D) 2 10 D 1 C 2200 500 !0 

.9 Time(s) Varalbe13 Fg 10 0 10 01 C 200 502 . 0 

$2 Time s) varible l F g R 0 10 1 '1 0 2300 5 2 00 0 

i Time(s) VariOale12 Fg0 0. 0 10 C1 0 2300 593 00 

-1,Time [s) Variable 13 F g 3 0 10 11 0 2300 503 50 --- Time (S) Variable1 Fg 14 0 10 101 0 2200 503 50 

1'Time Is) Var'able 15 F 1 0 10 - 0 1 0 2200 50o 50 

17_Time s) Vana te 16 F g I6 0 10 01 0 2300 503 50 

-18 JTine (s) Variable 17 Fg 17 0 10 1 0 1 0- 2000 50D' 50 

0 trTiel(s) Varable18 F~g I B 10 1 31 0 2000 503 50 

2 3 4... . . . .. . . .. 

"3V 

Figure 5: The worksheet "Inpfigure" 

2.4 Worksheet "Control" 

On this worksheet there are numeric values of settings, which are selected before macro 

start and are needed for macro execution and cells with text for dropdown menus. The values 

are linked therefore great attention must be paid when modifying values. In the first row there 

are settings, listed in Tables 2 and 4. In the second row are values of variables (switches) from 

the code (see Table 2). From the fifth row down there are in the cells options for drop down 

menu lists (see Table 2). The names can be modified by the user, because the names have no 

influence on macro execution (for first option the value of switch is one, for the second two 

etc.). With renaming also this manual for drawing with Excel program should be modified 

accordingly. 

Important: The names are normally changed when translating to another language. 

2.5 Worksheet "Temp" 

This worksheet is for temporary reading and storing data during macro execution and may 

not be modified unless for further development of this program for drawing. Besides 

temporary values there is text in Slovene and English, for translation from one language to 

another and opposite. 

Important: Modifying the worksheet "Temp" can cause error in macro execution.

IJS-DP-8278 October 2000 page 18

October 2000 

E:•analize99 - final,,objlave'ýNuregk\IJS-DP-8278 plot charts.doc page 18
Institut "Jo;ief Stefan", Ljubljana, Slovenija

%0 

O0

2.6 Charts "color", "mono" and "fig_small" 

On the charts "color ", "mono " and "figsmall " are placed original chart styles, which are 

needed for macro execution. The chart styles "color" and "mono" are for generating figures 

of type "large", and the chart style "figsmall" is default chart type for figures of type "small". All chart styles can be modified. For modifying the chart style Excel is used. The 

user can modify line type, color and weight, font size, type and color, legend position etc.

IJS-DP-8278 October 2000 page 19

October 2000 E:analize99 - final\objave\Nureg\JJS-DP-8278 plot charts.doc page 19
00 

Institut "Jo.ef Stefan", Ljubljana, Slovenija es 

3. Data preparation 

When reading with "auto " the ASCII data must have one of the described data format ("Y 

multiple" or "X-1'). If data are not in one of these formats, the user must prepare them. The 

figures are then created with command "Create figures from data". 

3.1 General requirements for data 

For normal macro execution the data must have correct data format. Inserting of data on 

the worksheets is following: the data files are opened, the text delimiter is space (or 

consecutive spaces). When preparing ASCII data the user must take into account the 

following: 

0 The figure titles will be read from data files if they will have same spaces (or consecutive 

spaces) as numeric data. The exception is title row. This means that one word can be used 

for one variable. In the title row may be as much as possible words. However, it must be 

taken into account the limitation for text length on the figure. 

The titles from data file will be read correctly only if the user will input correct values for 

figure title row, first and second row for axis title, first data row and no. of bottom data rows, 

skipped for drawing settings. 

Important: If data are of other format as described above, the user must prepare the data 

manually and to store them on the workbook to assure the correct macro execution. When 

preparing data on worksheet, the only requirement for text is to store text in the right cells. 

3.2 "Y multiple" data preparation 

The "Y multiple" data can have up to ten heading rows with consecutive 4000 data rows. 

The columns are separated with space (or consecutive spaces). 

The first ten heading rows can be used one for figure titles and two for figure axis titles 

(for example variable name and unit). The heading rows can be skipped. For further details 

refer to Section 2.1.2.5. Table 6 shows the data file stored in the workbook with one 

independent and nine dependent variables. In this case three rows are heading rows and the 

fourth row is first data row. There are ten columns with independent variable in the first row 

and dependent variables in the next nine rows. 

Table 6: Required format of "Y multiple" data 

independent I dependent 2 dependent 3 dependent 4 dependent 5 dependent 6 dependent 7 dependent & dependent 9 dependent variable variable variable variable variable variable variable variable variable variable 

row i RELAPS/MOD2!36 5NEKMBEIE99/Jul21 

row 2 TIME TLMPF TEMPF TEMPF TEMPTF TEMPF TEMPF TEMPF TEMPF TE'MPF row 3 0 90010000 80020000 80030000 00040000 80050000 81010000 81020000 81030000 81040000 

ro.w4 0 561 4 5614 5614 5614 5614 5612 561.2 5612 561 2 row 5 992 561 4 561.4 561.4 5614 5614 561.2 561.2 5612 561 2 

ro. 6 19.92 561 4 561 4 5614 561 4 561 4 5612 561.2 5612 561 2 row 7 29.92 5614 561 4 561.4 561.4 561 4 5612 561 2 5612 561 2 row 8 39.92 5614 561 4 5614 5614 561 4 5612 561.2 5612 561 2 

row 9 4992 561 4 561 4 5614 5614 561 4 5612 561.2 561 2 561 2 row 10 5992 561 4 561 4 561.4 561 4 561 4 561 2 5612 561 2 561 2 ro. 1I 6992 561 4 561 4 561.4 561 4 561 4 561 2 561.2 561 2 561 2 row 12 7992 561 4 561 4 561.4 561 4 561 4 561.2 5612 561 2 561 2 

ro. 4003 10200 561 4 561 4 5614 561 4 561 4 561.2 561.2 561 2 561 2 

The input figure title row in the first row is "RELAP5/MOD2...". In the second and third 

row are stored axis titles. The first figure x-axis and y-axis title would be "TIME 0" and 

"TEMPF 80010000", respectively. First column (variable TIME) is independent variable and

IJS-DP-8278 October 2000 page 20

October 2000 E:\analize99 -final\objave\Nureg\lJS-DP-8278 plot charts.doc page 20
00 

00 Institut "Joj.ef Stefan", Ljubljana, Slovenija

the other columns are dependent variables. The first data row is fourth and there is 4000 data. 

For "Data" group the settings would be as shown in Table 7. 

Table 7: Selected options for "Ymultiple" data presented in Table 6 

Option name Value 

Data type Y multiple 

Figure title row 1 

First and second row for axis title 2 and 3 

First data row 4 

No. of bottom data rows, skipped for drawing 0 

Important: If the data were not generated with RELAP5 "strip" (ASCII format of data file 

with results in columns) the data format must be checked. The data group settings must be 

correctly input otherwise the figure generation may fail. 

3.3 "X-Y" data preparation 

The "X-Y" data have one independent and one dependent variable. The columns must be 

separated with space (or consecutive spaces) otherwise the data file is not correctly read. This 

data file can be read also with "Y multiple" setting but some other functions would be lost 

which only "X-Y' setting has (see Table 3). 

In the heading rows (up to 10 rows) the user can select one row for figure title row and two 

rows for axis title rows. The heading rows can be skipped. For further detail refer to Section 

2.1.2.5. 

Table 8: "X-Y" data

row I 

row 2 

row 3 

row 4 

row 5 

row 6 

row 7 

row 8 

row 9 

row 10 

row It 

row 12

indennmd,t" variahl. --------------------- '---- -- a Ws)

Time 

Title I

0 

5 

to 

15 

20 

25 

30

dependent 

vaniahle

(kg) 

Mass

0.00E+00 

0 00E+00 

0 OOE+0o 

0.00Es00 

o.00E+00 

0 OOE+00 

0 OOE+O0

row 4005 20000 O.OOE+00 

row 4006 1on. data row 

An example of "X-YJ data is shown in Table 8. The fifth row is figure title row and the title 

is "Title 1". The fourth and first rows are axis title rows where the x-axis title is "Time (s)" 

and y-axis title is "Mass (kg)". The sixth row is first data row. There is 4000 data. The 4006th 

row must be skipped for drawing. The settings for "Data" group are shown in Table 9.

Table 9: Selected options for "X-Y" data presented in Table 8 

Option name Value 

Data type X-Y 

Figure title row 5 

First and second row for axis title 4 and I 

First data row 6 

No. of bottom data rows, skipped for drawing 1

IJS-DP-8278 October 2000 

Ekanalize99 - finalhobjave\Nureg\1JS-DP-8278 plot charts.doc page 21
Institut "Jo~ef Stefan", Ljubljana, Slovenija

@0 

00

4. Output workbook 

All results of drawing with Excel program are stored in output workbook. These are 

mainly data stored on worksheets and created figures placed on charts or worksheets. 

Additionally three worksheets are inserted (by Command 3 and Command 4), one is 

"Output", where input data from worksheet "Input _data" are stored (see Table 10), the second 

is "Figure-list", where information regarding figures is stored (see Table 11) and the last is 

"Legend". If only Command I is executed worksheet "Output" is created. If only Command 2 

is executed "Output1", "Figure list" and "Legend" are created. These worksheets are added 

for archiving purposes and for later figure modifications as well as for generating information 

to be input on "Inpfigure". The user can change automatically selected titles or boundaries. 

On the worksheet "Output" (or "Output]") all information from "Inputdata" is stored, 

including program settings, for regenerating figures if needed. The difference between 

worksheet "Output]" and "Output" is that in the worksheet "Output]" the data filenames are 

replaced with worksheet names (the figures are created from already opened workbook with 

worksheets) which are listed in column C while columns A and B are empty. 

An example of output workbook is shown in Appendix A. 

Table 10: Input data used for program execution archived on Worksheet 

"Output" 

Column no. Name Description 

I Directory data file path 

2 Filename data filename (without extension) 

3 File extension name of extension 

4 Output filename output workbook path and filename 

4 Name of data sheet(s) worksheets with stored data 

4 Date and time the date and time of importing data 

5 Options group see Table 2 

5 Header text text in header when printing "small" 

6 Seq.no. see Table 2 

7 Option see Table 2

Table 11: Information about 

"Figure-list"

generated figures stored on worksheet

Column no. Name Description 

1 Figure location chart or worksheet with embedded chart 

2 Label by default "Figure" 

3 Fig. no. seq. number of figure 

4 x-axis title title on x axis 

5 y-axis title title on y axis 

6 Figure title figure title 

7 y-axis min. scale lower y axis boundary 

8 y-axis max. scale upper y axis boundary 

9 y-axis major unit major unit on y axis (axis text) 

10 y-axis minor unit minor unit on y axis 

11 x-axis min. scale lower x axis boundary 

12 x-axis max. scale upper x axis boundary 

13 x-axis major unit major unit on x axis (axis text)

IJS-DP-8278 Octob 

E\analize99 -final\objave\Nureg'lJS-DP-8278 plot charts.doc

'cr2000 page 22

9er 2000 page 22
0. 

Institut "Jo.ef Stefan", Ljubljana, Slovenija 

Table 11 (Continued) 

Column no. Name Description 

14 x-axis minor unit minor unit on x axis 

15 Data for curve no. 1 worksheet, data range and legend for figure 

16 Data for curve no. 2 worksheet, data range and legend for figure 

24 Data for curve no. 20 worksheet, data range and legend for figure 

The figures of type "large" are placed on charts "01", "02-, do "nn", where nn is number 

of all figures. If figures of type "large" are placed on single worksheet, the worksheet name is 

"Fig large". 

The figures of type "small" are always placed on worksheet "Figsmall" with exception 

for command "Read data and create figures" (function only for "Y multiple" data), when for 

each data file the figures are placed on separate worksheet. The worksheets names are 

"Figsmall_01", "Figsmall_02",..., "Fig__smallnn", if nn is the number of data files. 

Important: When generating figures of type "small" there is option to have in one row from 

1 to 6 figures (see Section 2.1.2.4), which can be also printed in such layout. By this the 

maximum is 36 figures on single A4/letter sheet.

IJS-DP-8278 October 2000 

E:\analize99 - final\objave\NuregSJS-DP-8278 plot charts.doc page 23
S0 

Institut "Jo.ef Stefan", Ljubljana, Slovenija Se 

5. Program limitations 

Macros stored in workbook for drawing with Excel are written generally, however the 

Excel 97 limitations were taken into account. Therefore the maximum number of worksheets 

and charts is as much as allowed by memory or as limited by Excel 97. Each figure of format 

"Chart", embedded on worksheet counts same as chart sheet. 

The first action to reduce the number of worksheets or charts is to store data on common 

worksheets and place charts on one worksheet. However as figures of format "Chart" count 

same as chart sheet the format "Picture" must be selected to have large number of figures 

(arbitrarily limited to 600 figures). 

If the number of worksheets or charts or embedded charts on worksheets is greater than 

limitation by Excel 97, the execution is terminated and the dialog box "Not enough memory" 

is displayed or Excel application interruption dialog box "Application error". In such a case 

the application must be closed and Excel must be run again. 

To prevent too frequent interruption of the program because of errors in input data the 

warnings (messages) are built into the program to stop the execution and display the reason. If 

termination with system message appears the most probable reason is wrong read or prepared 

data. In such a case it is recommended to check the data (see Section 3). At unexpected error 

during execution the Visual Basic window is opened. To quickly leave the window press keys 

<Alt Q>. 

5.1 Built-in warnings 

There are 33 built-in warnings, which are described below. 

Important: Warning is displayed when an error in a certain procedure occurred. In some 

special cases the reason may be other as displayed. 

1. Warning 1: Maximum 20 curves can be compared. Other curves will not be plotted 

Explanation: The number of curves on one figure is limited to 20. If there is more 

variables there will not be presented on figure. 

2. Warning 2: Title and boundary sheetname is not specified Specify the sheet or select "11auto " or "no "for titles! 

Explanation: When boundaries and titles are used from worksheet the worksheet name 

must be specified. How to create such worksheet see Section 2.1.2.3, and how to 

prepare the data see Section 2.3. 

3. Warning 3: Sheet 'worksheet' with titles does not exist! Specify the sheet or select auto 

or no for titles. 

Explanation: Worksheet with titles and boundaries was wrong specified. Select the 

worksheet which exists or create new worksheet (see Section 2.1.2.3). 

4. Warning 4: Workbook with the name 'workbook name' is already opened! Rename or 

close the workbook or run the command 'Create figures from data'! 

Explanation: The workbook is already opened therefore new workbook cannot be 

renamed to the existing workbook name. 

5. Warning 5: Worksheet "worksheet name" already exists. The data file 'filename" will 

not be opened again! 

Explanation: The worksheet in the output workbook already exists which means that 

the data file with the same name was already imported into the worksheet by the

IJS-DP-8278 October 2000 

EAanalize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc page 24
Institut "Jo•.ef Stefan", Ljubljana, Slovenija %6e 00 

program. This warning also prevents different data files with the same filename to be 

stored under same worksheet name. 

6. Warning 6: Error in module for reading data! Execution terminated 

Explanation: These mean that data the macro execution was terminated because of 

error in module for reading data. 

7. Warning 7. First filename extension = NI is greater than last filename extension = N2. 

Correct!!! 

Explanation: First file extension is greater than last file extension. A new value of file 

extension must be selected. 

8. Warning 8: Filename filename' does not exist! Execution terminated 

Explanation: Data file does not exist. Check the path and filename plus extension. 

9. Warning 9. Directory 'path' does not exist! Execution terminated 

Explanation: The path does not exist. A path of existing folder must be input. 

10. Warning 10: Worksheet with 'large' figures of name -'worksheet names- already 

exists!!! Execution terminated! Rename or delete the worksheet. 

Explanation: For figures of type large the worksheet already exists therefore execution 

is terminated. The worksheet must be renamed or deleted. 

11. Warning 11: Worksheet with 'small' figures of name -'worksheet name'- already 

existsP!! Execution terminated! Rename or delete the worksheet. 

Explanation: For figures of type small the worksheet already exists therefore execution 

is terminated. The worksheet must be renamed or deleted. 

12. Warning 12: Figure -'chart name" already exists!!! Do you want to continue and plot 

the figure with the name 'chart name'? 

Explanation: The chart exists however the execution can be proceeded. A letter A is 

added before the chart name. Such a situation could appear when the command "Create 

figures from data" is run consecutively for figures of type large located on chart sheets. 

13. Warning 13: User-defined chart type added to templateM!! 

Explanation: This is the message for the user that the template was saved and the work 

can be continued. 

14. Warning 14: Title row number must be smaller than first data row number! Title row 

number is NJ. First data row number is N2. 

Explanation: This warning is related to data description. First data row must be greater than figure title row. The selected settings must be changed to fulfill the above 

requirement. It is assumed that heading rows must be before data rows. For more 

information about data preparation see Section 3. 

15. Warning 15: First axis title row number must be smaller than first data row number! 

First axis title row number is N1. First data row number is N2. 

Explanation: This warning is related to data description. First axis title row must be 

smaller than first data row. The selected settings must be changed to fulfill the above 

requirement. It is assumed that heading rows must be before data rows. For more 

information about data preparation see Section 3. 

16. Warning 16: Second axis title row number must be smaller than first data row number! 

Second axis title row number is N1. First data row number is N2. 

IJS-DP-8278 October 2000 page 25 

EAanalize99 - finalhobjave\Nureg\lJS-DP-8278 plot charts.doc
00 

09 Institut "Jot~ef Stefan", Ljubljana, Slovenija 0 0 

Explanation: This warning is related to data description. Second axis title row must be 

smaller than first data row. The selected settings must be changed to fulfill the above 

requirement. It is assumed that heading rows must be before data rows. For more 

information about data preparation see Section 3. 

17. Warning 17: Title row number must be different from axis title row numbers! 

Explanation: This message is intended to prevent duplication of information on figure. 

18. Warning 18- Number of data for plotting is smaller than 1. Check data or skip less data 

rows! Number of data rows is N1. Number of skipped data rows is N2. 

Explanation: For generating the figure at least one row of data is needed. Information 

is displayed when too many data rows are skipped or the data file has empty rows. 

19. Warning 19. Checkpathfor output workbook:. filename'. Execution terminated. 

Explanation: The directory to save output workbook does not exist or is currently 

unavailable. The folder must be created or renamed or the user must wait for network 

connections. 

20. Warning 20: First data filename extension must be smaller or equal to last data 

filename extension. 

Explanation: Last file extension is greater than first file extension. A new value of file 

extension must be selected. 

21. Warning 21: At least one data file must be selected to open (column D, select Y). 

Explanation: The commands "Read data", "Read data and create figures" or "Read 

data + Create figures from data" may be run when at least one data file to be open is 

input and is selected to be open. 

22. Warning 22: Error during writing on sheet 'Output'. Execution aborted 

Explanation: This warning prevents abortion of the macro and gives information that 

error occurred when macro is storing information on worksheet "Output". The user 

must identify the reason with Visual Basic debugger. This warning may appear also 

when the workbook with data is not correctly prepared. 

23. Warning 23: Execution terminated because style is missing Run macro 'Save user

defined chart type in template'. 

Explanation: When guideline in Section 2.1.3.3 is not used such a message is displayed 

in order to prevent macro abortion when macro is run and the chart style does not exists. 

24. Warning 24. Each data file can be opened only once. Select 'Y' only once for file 

filename'. 

Explanation: The data file is already inserted on worksheet. Each data file can be 

inserted only once since worksheet names are data filenames. 

25. Warning 25: Maximum number of columns allowed is 256. N columns were tried to be 

put on one worksheet. 

Explanation: Maximum 256 columns are allowed in Excel therefore reduce the number 

of data files to be opened at one macro run. 

26. Warning 28: Error!!! Cannot rename to already existing sheet 'worksheet name'. 

Explanation: When data file is stored on the workbook with name of data file it cannot 

be read again therefore the macro is terminated. The worksheet must be renamed or 

deleted or the data file is not opened at later reading. Already stored data on worksheets 

can be used or saved even if execution is terminated.

IJS-DP-8278 October 2000 

E:'analize99 - final\objave\Nureg\1JS-DP-8278 plot charts.doc page 26
0* 00 Institut "Joi.ef Stefan", Ljubljana, Slovenija 0 0 

2 Warning 29. Column C is now legend. Be careful, first legend is legend with first 'Y in 

column D, second with second 'Y in D etc- The legends are. (the legend list is displayed 

on the screen, see Figure 6). Selected cell is "Cn" 

Explanation: Column C has two functions, as legend in the plotting mode 

"Comparison" or extension in the plotting mode "One curve". This warning is just to 

remind the user what are legend names and in which cells are specified. 

28. Warning 30. Be careful, cell F6 is now legend 

Explanation: In the plotting mode "One curve" legend is input in cell F6. For details 

see Section 2.1.2.3. This warning is just to remind the user. 

29. Warning 201:. Only mode 'One curve' can be used for reading data. 

Explanation: For "X-Y" data the plotting mode "Comparison" cannot be used (see 

Table 3). 

30. Warning 202. Excel workbook name does not exist. 

Explanation: The specified output workbook is not opened. The workbook must be 

opened or created or check the workbook name. 

31. Warning 203. Only mode 'One curve' can be used for reading data and plotting figures. 

Perform read and plot separately! 

Explanation: For "X-Y" data and the plotting mode "Comparison" the Command 3 

cannot be used (see Table 3). 

32. Warning 204. X-Y data, therefore button not applicable for reading data and plotting 

figures. Select button 'Read data + Create figures from data'. 

Explanation: For "X-Y" data and the plotting mode "One curve" the Command 3 

cannot be used (see Table 3). 

33. Warning 205. Only mode 'One curve' can be used for reading data and plottingfigures. 

Perform read and plot separately! 

34. Explanation: For "X-Y" data and the plotting mode "Comparison" the Command 4 

cannot be used (see Table 3). 

Www-2!hCokxnnC is now-legebd: Be ert Aý lged h fiit Y in co&Enn D, se~ dvih onY in D. :ý lhegends are: -Legend I (cellC 03 0 . - -. . -- 

Legend 3 fc&M 03 

Legenid 4(celf7O Ut 02,~,. . ... , .

Legqeid 5 [cell CS% exp 

Selected ce is C41 

Figure 6: Dialog box for Warning 29

IJS-DP-8278 October 2000 

E:,anafize99 - final~objave\Nureg%1JS-DP-8278 plot charts.doc page 27
0@ 

Institut "Jo.ef Stefan", Ljubljana, Slovenija @5 

6. Literature 

[1] D. M. Schinedr, K. L. Wagner, W. H. Grush, K. R. Jones, Nuclear Plant Analyzer, Vol. 

I to 3, NUREG/CR-6291, INEL-94/0123, Idaho National Engineering Laboratory, 

1995. 

[2] Help from Visual Basic in Excel 97, Microsoft Office 97. 

[3] Microsoft Office 97 / Visual Basic language reference, Volume 2: Microsoft Excel 

Language reference, Microsoft Corporation, ISBN 1-57231-339-0, Washington, 

Microsoft Press, 1997.

IJS-DP-8278 October 2000 

E:\anahize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc

page 28
S0 

00 

0 . Institut "Jo.ef Stefan", Ljubljana, Slovenija 

7. Index

A 

auto, 5, 7, 8, 10, 17,20,24 

B 

Boundaries, iii, 2, 5, 7 

C 

Cancel, 13 

Chart, 5, 9, 15, 24 

color. iii, 2. 3,4, 5, 9, 15, 19 

Command 1, 11, 22 

Command 2, 11, 22 

Command 3, 11, 22, 27 

Command 4, 11, 22, 27, A-I 

Comparison, iii, 4, 5, 6, 8, 11, 12, 13, 14, 15, 17,27 

Continue, 13 

Control, iii, 2, 18 

Create figures from data, 9, 11, 13, 14, 15, 17. 20, 24, 25, 

26, A-1 

D 

Data. iii, iv, 3, 4, 6, 10, 12, 13, 14, 16, 20, 21,23, 25, A-1 

Data for curve no., 23 

Data for one case on one sheet?, 13 

Data on one sheet?, 13 

Data type, 6, 10. 12, 14, 16, 21 

Date and time, iii, 16, 22 

Directory, 3, 22, 25 

F 

Figlarge, 12, 14, 23 

figsmall, iii, 2, 9, 15, 17, 19 

Figsmall, 12, 14, 17. 23, A-1, A-3, A-4 

figure format, 9, 14 

Figure format, 5, 9, 16 

Figure location, 22 

figure size, 3, 15 

Figure size, iii, 11, 15 

figure style, 15, A-I 

figure title, 20 

Figure title, 5, 6, 7, 10, 16, 17, 21,22 

Figure title row, 6, 10, 16, 21 

Figure type, 5, 9, 12, 16 

Figure_list, iv, 8, 22, A-i, A-5 

File extension, 3, 22 

Filename, 3, 22, 25 

First data row, 6, 10, 16, 21, 25 

First file ext., 5, 6, 16

Graphics, iii, 2, 5, 9 

Header text, 4, 22

G 

H

I 

Inpfigure, iii. 2, 8, 17, 18, 22, A-I 

input, ii, iv, 2, 3, 4, 5, 6, 7,8, 10,11, 12, 13, 14, 15, 17, 20,21, 22,24, 25, 26, 27, A-l, A-5 

Input data, iii. 2. 3, 14, 15. 22, A-I, A-2 

input+auto, 5, 7, 8 

input+auto title, 5, 7 

input+auto y+filename, 5, 7 

input+filename, 5, 7 

L 

Label, 22 

large, 1, 5. 9, 12, 14, 15, 16, 17, 19, 23, 24, 25 

Last file ext., 5, 6, 16 

Legend, 4, 5, 8, 12. 16, 22, A-I, A-6 

M 

Mode, iii, 2. 5. 6 

mono, iii, 2, 5, 9, 15, 19 

N 

No. of bottom data rows, skipped for drawing, 6, 10. 16, 

21 

No. of figures in row, 5, 9, 16 

Not enough memory, 9, 24 

0 

One curve, iii. 4, 5, 6, 8, 11, 12, 13, 14, 17, 27 

One sheet for figures, 14 

Option group name, 16 

Options, iii, iv, 2, 5,6, 11, 16,22 

Options group, 22 

Output, iii, iv, 22, 26, A-i, A-5 

Output filename, 22 

Output1, 22 

P 

paper size, 3, 16 

Picture, 5, 6, 9, 14, 24 

plotting mode, iv, 3, 4, 6, 8, 11, 12, 13, 14, 17, 27 

Plotting mode, 5, 6, 8, 12, 16 

Program, iii, iv, 2, 3, 5, 6, I1, 15, 24 

Q 

quick input, 2, 4, 5, 7 

R 

Read data. 9, 11, 13, 14, 15, 23, 26, 27, A-I 

Read data + Create figures from data, 11, 15, 26, A-I 

Read data and create figures, 9, 11, 14, 23, 26 

Run, iii. 11. 26

IJS-DP-8278 October 2000 

E:\analize99 - final\objave\Nureg\lJS-DP-8278 plot charts.doc page 29
00 

Institut "Jozef Stefan", Ljubljana, Slovenija 0 0

S 

Save user-defined chart type in template, iii, 11. 15, 26 

Second row for axis title, 16 

small, iii. 2, 5, 9, 12, 14, 15, 16, 17, 19, 22. 23, 25, A-I, 

A-3, A-4

T

Temp. iii. 2, 18 

Text, iii. 2, 5. 7 

Title and boundary sheetname. 5, 7, 8, 16. 17, 24 

X 

x-axis boundaries, 2, 4. 5, 7, 16

x-axis major unit, 17, 22 

x-axis max. scale, 17, 22 

x-axis min. scale, 17. 22 

x-axis minor unit, 17, 23 

x-axis title, 5, 7, 8, 16, 17, 21, 22 

X-Y. iii, iv, 6, 8, 10, 11, 12, 14, 15. 20. 21, 27

y

Y multiple, iii. iv, 6, 8, 10. 11, 12, 13. 14,20.21,23 

y-axis boundaries, 5, 7, 16 

y-axis major unit, 17, 22 

y-axis max. scale, 17, 22 

y-axis min. scale, 17, 22 

y-axis minor unit, 17, 22 

y-axis title, 2, 5, 7, 8, 16, 17, 20, 21,22

IJS-DP-8278 October 2000 page 30

E:\analize99 - final\objave\Nureg\!JS-DP-8278 plot charts.doc
00

Institut "Jo.ef Stefan", Ljubljana, Slovenija

APPENDIX A 

A AN EXAMPLE OF OUTPUT WORKBOOK GENERATED BY THE 

PROGRAM 

In this appendix is shown the output workbook with worksheets, created at running 

command 4 ("Read data + Create figures from data"), when data shown in Table A-2 and 

input data shown on Figure A-1 were used. For titles the worksheet "Inp_figure"<<, shown on 

Figure A.2 was used while figure style used is shown in Figure A-3. The worksheets created 

are listed in Table A-1 and shown in Figures A-4 through A-9. Finally, on Figure A-10 an 

example of figures imported into Word 97 is shown. 

Table A-I: List of worksheets which were created in workbook "Output 

workbook.xls" after running the Command 4 (Read data + Create figures from 

data) 

Figure no. Worksheet name Worksheet function 

A-4 local00 rep.02-03 worksheet with stored data for case 1 

A-5 loca200 rep.02-03 worksheet with stored data for case 2 

A-6 Figsmall worksheet with figures of type "small" 

A-7 Output worksheet with data input on worksheet "Input data" 

A-8 Figure list worksheet with information, used for figure creation 

A-9 Legend worksheet with legends 

Table A-2: Data files in ASCII format 

Data file: local00 rep.02 

ZLP5,iM0D2/36.5 NEIQ4BEIE 99/Ju121 

ME CThkL7AR C4Th3.VXR 0CNTLVOR CNMVAWR CNTRP2V9,R =4TR1V1R CNT9LV0A C479LVAR ~CNCLVAR 0 610 611 612 613 614 615 616 617 618 0. 310.C 159 0 "059C 258 0 66.30 66.30 49.25 49.25 62.30 9.920 314.6 157.8 .57.8 257.2 65.54 65.31 49.22 49.22 62.50 19.92 303.2 157.6 '57.8 157.2 65.42 65-9 49.22 49.22 62.36 29.92 311.3 157.9 5 8 157.2 65.38 65.15 49:22 49.22 62.27 

1.0200E+04 167.3 61.76 61.78 120.0 43.72 39.04 49.22 49.22 23.21 

Data file: local 00 rep.03 RZLAPS/MOD2/36.5 NE10MEIE 99/Ju121 T2I2E CNTRLVAR CNrRL JV a 4TRI.VAR NTRLV95 C RLVAJTR C4792N2P. CNTRLV CZT52,LVAR 1 C NTRLV7 0 619 620 621 622 623 624 625 626 627 0. 100.3 100 0 69.35 69.35 65.30 65.30 19.80 100 0 100.0 9.920 100.3 100.0 68.60 68.59 65.29 65-29 18.69 100.'. 99.99 -9.92 100.0 100.0 69.27 69.26 65.29 65.29 16.59 100,1 99.99 29.92 100.0 100.0 69.35 69.34 65.29 65-29 18.65 100.i 99.99 

1.0200E+04 100.0 100.0 66.01 66.55 65.29 65.29 90.25 1.625 0. 

Data file: loca200 rep.02 RE1APS/MCD2/36.5 NE8MBElE 99/3u12, TDC C4NTRLVAR CNTRLVAR CNLV'TAR CNTRLVAR CiTRILVAR OC.NTLV2.V CQ4RLVAR CNTRLVAR CNTRI.VA, 0 610 611 612 613 614 615 616 617 618 0. 310 0 159 0 159.0 158.0 66.30 66.30 49.25 49.25 62.30 9.920 314.6 157.' 157.8 257.2 65.54 65.31 49.22 49.22 62.50 19.92 313.2 157,8 157.9 157.2 65.42 65.19 49.22 49.22 62.36 29.92 311.3 157.8 157.9 257.2 65.38 65.15 49.22 49.22 62.27 

1.0200E+04 212.5 18.55 18.55 120.0 54.74 55.1' 18.87 18.68 0. 

Data file: loca200 rep.03 RKAPS/MOD2/36.5 NEIME E 1 9 9

/Jul121 TIME akLVAR CNV CNTRLVAR hV CNTR NTRLVAR CNTR2VAR OnR3VAR CNTR1V5R CNTR9LVAP CNTRLVAR C 619 620 621 622 623 624 625 626 627 0. 103.0 100.0 69.35 69.35 65.30 65.30 18. 0 1C0.0 100.0 9.920 103,0 10,.0 68.60 68.58 65.29 65.29 8 .69 100. 1 99.99 19.92 103.0 100.0 69.27 69.26 65.29 65.29 18.59 100.1 99.99 29.92 100.0 100.0 69.35 69.34 65.29 65.29 18.65 100.1 99,99 

1.0200E.04 74.59 74.59 66.36 67.47 10.96 101-2 1.9:34E-02 6.260 0.

IJS-DP-8278 October 2000 page A-I

E:\analize99 - final\objave\Nureg\1JS-DP-8278 plot chans.doc
00 cu 

- - - --~4 VEIýý 2RR FF1 

g s a 8 S000000s 

~t 

1flit . 

-O ---- - ----C 

PIN1 00 

4-A 0. 

'4-ý IN 9 9 

E) LC-, 

4c s 

- - - - -- - - - - m 

j&"A F~I=F~---l~k E LE+FEiEFEF E c, "'"~ ~~~~~~~~~~ 3⁄4 ~ F41 r P nm 091 90C LO - -g~ p.p I 'ý 4"l
0@1 

LiiIM Li 

A %i ii 

"8888 888 8 88 

- -- --- - - - - - - - - - -

F * .. ..... F 

.(ý sq ýcC Vý5 tWW F, 88448884488%1W%888 it888 wit88 

In~ .. . .8.2. . ... 

jeA . . 0 & -J -- 

CI~ ~' c ~ o 0~Q 

IA PA. MR M MR4A 

''4.~ r' C7 ~ t 0 . - 00 

tm .0) 1* 4'
0 .i . .

I .. ... .. 

~.00077F 

.. .. .. .. .. .. .. .. . .. .. . 

"~~'Ow88 B ~~~888888s88 8P8888&&VZR& 

-E -.-& - -

mt 84 S i8 P R Z L a ;C : C 

0 0. 

'0* -- ..0..0 ....~.... F4O0 q [110 fl'~ F3. q-: P ;qqqF IP,,4F,;F 6 ;Fý 

.... ... ... .. ...........

00 0 i000223 0 0 o~'1 Ili 00 

-- - 00 

-'Al :LNe ,0rI ~~~1'0010100~~~~~~~~2 7icrrr0rrr 0 rrrr, 1 1 - 0 00 000-o 

EI EL
- - - - - - - - -___ 

1.'1 ------ --

A LP 

r- CZ. 

x L L 

- --- - 0L 

Z~ ;r 

21 I 9 

0 144 iz~i 

U) ',U 

CU CA-.a
S0 S. 

Institut "Jotef Stefan", Ljubljana, Slovenija 

_I0 . 1 L.pd2 

2 D ILOCA 2"LOCA 

3 0 LCCA 2TLCA 

403 ILOCA 2 LOCA 

'04 LOCA 2" LOCA 

6l007 "LOCA 2" .(A :• T105 I"LOCA 2"LOCA "!06 'LOCA 2"LOCA 

1 t " LCA 2"LOCA 

9 Li"1 LOCA 2"LOCA 

W"130 14"1 I I"LOA LOCA 2"LOCA 2'LCA 21,21 LOCA 2: "LC 

821? 1C'LCA 2 .OCA 

-AA 

F 17r A - LOCrLw 2"eLnfA 

9• L-OCA 2- LOCA i 

p;•,L 

Figure A-9: Worksheet "Legend" with legend names for figures

3,50 -- - -- - -. - - -. ... -- - -

I I L 

250 --- -, -T -- - --- - - -- - - -:- - • • • 

150 - 7 -r -I- - - - - - T - -i -

0, 

0 1 2 3 4 5 6 7 8 9 10 

US-RED pbt n11 10.0(12:1542) Tine (s)

60o, Fig. 8 !CN. TRLVAR 617)

r i i I i 

50-F -+ -.....----- -1- - __ " LOCA 

-Z'LOCA 40 - - - - - - -- - -- - -- -

30 -- ----- -•- -

10 .. -- . - - . -.i - - - -I . . -.. 

I I I I 

20 SI I I I I i I 

O I I I I I I , I 

0 1 2 3 4 5 6 7 8 9 10 

IJS-RED plot O 11.10.00(12.15 49) Time (s) 

180 -IsM Uis. I-L- J 11 160 . . . 

240 ~- - - -- -- - 4 LC 

' ' • ' ' - 2-" LOCA I 

- - --70 J ..... - - -LI T- -- - - -I 

SI I I I I 

60L 

40 . . .-• . - -. T . . . . . . - . - . t 

20 - -- - - -- 

0 1 2 3 4 5 6 7 8 9 10 

US-RED pbl on 1110.00)(12 15:44) Tine (s)

70 Fin. 15 ICNTRL VAR 624) 

60 -- . -.i - - -• --- ]- 

60I I I I 

I- LOCA 

50 .- - - -- - -- - 2 LOCA 

40 -- --- - -- - -- -- - - -- - --- - -- - -- 

30 - - - - I- 7 n

20t -- I - - --- -r- - - - - -- - - - - -

0,,----L_-_ - .

0 2I I 4 5 I 6 I 

0 1 2 3 4 5 6 7 8 9 10

US-RED plotOn 11 0.D(12 15:•6) Time (s)

Figure A-10: Charts exported in Word

IJS-DP-8278 Octol 

E:\aa]ize99 - final\objave\Nureg\1IJS-DP-8278 plot charts.doc

icr 2000 page A-6

400 r- Fig. 1 (CNTRLYAR ,610)

page A-6

I -

•er 2000
NRC FORM 335 U.S. NUCLEAR REGULATORY COMMISSION 1. REPORT NUMBER 

(2-89) (Assigned by NRC, Add Vol., Supp., Rev., NRCM 1102, anATA and Addendum Numbers, if any.) 3201,3202 BIBLIOGRAPHIC DATA SHEET 

(See istructions on the reves) NUREG/IA-0191 

2. TITLE AND SUBTITLE 

A Tool for Drawing With Excel 3. DATE REPORT PUBLISHED 

MONTH YEAR 

January 2001 

4. FIN OR GRANT NUMBER 

5. AUTHOR(S) 6. TYPE OF REPORT 

A. Prosek, B. Mavko, I. Parzer Technical 

7. PERIOD COVERED (Inclusive Dates) 

8. PERFORMING ORGANIZATION - NAME AND ADDRESS (If NRC, provide Division, Oflice or Region, U.S. Nuclear Regulatory Commission, and mailing address; if contrectv, 

provide name and mailing address.) 

Institut Jozef Stefan 

Jamova 39 

1001 Ljubljana, Slovenija

9. SPONSORING ORGANIZATION - NAME AND ADDRESS (if NRC, type Same as above'; ifcontractor, provide NRC Division, Office or Region, U.S. Nuclear Regulatory Commission, 

and mailing address.)

Division of Systems Analysis and Regulatory Effectiveness 

Office of Nuciear Regulatory Research 

U.S. Nuclear Regulatory Commission 

Washington, DC 20555-0001

10. SUPPLEMENTARY NOTES

11. ABSTRACT (200 words or less) 

Worldwide research is conducted to show how to reduce the user effects on safety analysis in the nuclear engineering field. It 

was shown that user effects could be reduced with automatic display of the result. Therefore a program was developed for 

automatic creation of figures with Microsoft Excel 97 with capability to present single curve or multiple curves. Primarily the 

program was developed for the RELAP5 users. However, due to flexibility it can be used for graphic presentation of severe 

accident or user developed codes when the output is in columns and ASCII format. The testing of the tool showed that figures 

can be quickly and easily created. The tool conforms also to quality assurance requirements, because all input data used are 

archived. Additionally a figure list is created including titles, boundaries, ranges of cells for plotting, etc. A huge number of 

quickly generated figures enable detail analysis of certain phenomena, thereby improving the quality of the analysis.

12. KEY WORDS/DESCRIPTORS (List words or phrases that wif assist researchers ilocating the report.) 13. AVAILABILITY STATEMENT 

RELAP5/MOD3 unlimited 

Post-Processor 14. SECURITY CLASSIFICATION 

(This Page) 

unclassified 

(This Report) 

unclassified 

15. NUMBER OF PAGES 

16. PRICE

NRC FORM 335 (2-89) This form was electronically produced by Elite Federal Forms. Inc.
LpaPrirnted on reicycled 

paper Pg 

Federal Recycling Program
NUREG/IA-0191 A TOOL FOR DRAWING WITH EXCEL JANUARY 2001

UNITED STATES 

NUCLEAR REGULATORY COMMISSION 

WASHINGTON, DC 20555-0001 

OFFICIAL BUSINESS 

PENALTY FOR PRIVATE USE, $300