Workbook (calculation) modifications.

Table of Content:

• Protection of the workbook and sheets
• Calculation formulas
• How to modify
• "Calculation" sheet
• "Materials" sheet
• "Tables" sheet
• "Dictionary" sheet
• "DXF" sheet
• Connection of calculations

Workbook (calculation) modifications.

The structure of the sheets in the workbook was described in the chapter "Control, structure and syntax of calculations". A detailed description of the structure of some sheets (in views of the applicable modifications) and the possible and permissible procedures with their modifications and extensions are given in this chapter.

Recommendations: Modifications and extensions of the calculation shall be performed in a copy of the workbook only.

Protection of the workbook and sheets.

State after installation of the product.

• Protection at the workbook level - disabled.
• Protection at the sheet level - Only the "Calculation" sheet is protected against changes in the cells that contain formulas. The protection of other sheets is disabled. Protection can be disabled in the MS Excel menu "Setting -> Protection -> Unlock sheet". No password entered.
• VBA protection - The code written in VBA language is locked, provided with a certificate and protected by a password. Attempts to break the protection are illegal and considered as a breach of the conditions of license.

Calculation formulas.

The formulas (algorithms) of the calculation are entered into the named cells and ranges (more on the named ranges, see MS Excel Help). Entries of algorithms are much better arranged and easier to understand. The formulas then include names of the cells instead of their numbers.

Example of an entry:

Names of the variables used in the calculation are distinguished by their initial symbols as follows:

• Underscore sign "_" - Names determined for common use in the calculation.
• Letter "S_" - Names determined for naming the system variables.
• Letter "T_" - Naming tables (ranges).
• Letters "XC_", "XM_", "XS_" and "XV_" - Naming the cells whose contents is translated if there is a change in the calculation language.

Hint: If you are going to name your own cells, it is advisable to choose your own prefix and use it.

How to modify.

Knowledge of MS Excel and the respective engineering problems to an extent adequate to the intended changes is necessary for extension of calculations, their modifications, changes or formatting. When modifying the calculations, pay attention to the following recommendations:

• Changes and modifications should be performed in a copy of the workbook.
• Do not forget to unlock the "Calculation" sheet if you wish to modify it.
• After modifications and changes, it is advisable to compare the results of the modified calculation with the original calculation. This may prevent various errors.

Note: The names of auxiliary variables and coefficients in hidden ranges are in the English language only and are not translated.

Hint: If the calculation is completed with separate algorithms, it is advisable to establish a new sheet and perform all extensions in this sheet. These extensions can be transferred simply into a new version of our calculation.

"Calculation" sheet.

The "Calculation" sheet has rows 1-98, columns A-H and other hidden columns as necessary. The following table shows a layout of the ranges and their meaning.

1. Range of hidden rows (1-100) - It is used for a communication with parametric CAD systems. The description can be found in the help section to the supplement of connection of the respective CAD system.
2. Range of hidden columns (A-H) - It is used for system functions (hiding rows, indexes of selection sheets, state symptoms, transfer of values, etc.).
3. Visible range of calculation - The task is solved here.
4. Range of hidden columns - This range includes various intermediate calculations, definitions of some constants, etc.

Displaying the marking of rows and columns is disabled to enlarge the desktop. It can be enabled in the MS Excel menu "Setting -> Options -> Displaying".

Possible modifications.

Extension of calculation - The calculation can easily be extended by a solution of extreme problems, the addition of various coefficients or indexes (requirements: basic knowledge of MS Excel). Enter the new relations behind the existing calculation into the C range.

Modifications of the existing calculation - it is possible (after unlocking the sheet) to modify the used formulas or coefficients both in C range and D range as well. (requirements: very good knowledge of MS Excel, excellent knowledge of the solved problems).

"Materials" sheet.

If the calculation includes material tables, these tables can be found on the "Materials" sheet. In addition to pre-filled material values, it is possible to define new "user materials". A white background on the cells differentiates the range of user materials. If you add your own materials, fill out all values in the units that are given in the headers of the material tables.

Warning: If some values remain blank, the calculation will provide faulty results.

"Tables" sheet.

The "Tables" sheet, present with most calculations, contains data, coefficients, selection lists and other basic materials necessary for the calculation. A white background on the cells differentiates the ranges that can be changed. All the tables are given headers to provide clear information of what is included inside. Despite this, it is advisable that only experienced users who know very well the problems of the given calculation perform changes in the tables.

Note: The names of the tables are in the English language only and are not translated.

"Dictionary" sheet.

The "Dictionary" sheet contains a list of all translated terms. The first row of the sheet includes a list of all languages, beginning with column "G" (Marking columns according to ISO 639-1). If you wish to translate a calculation or its part, enter the corresponding equivalent of the password into the respective column. If no translated term is found in the translation process, the English term will be used automatically.

Hint: If the list does not include the desired language for the translation, use the last column marked "XX".

"DXF" sheet.

The "DXF" sheet contains definitions for the creation of 2D drawings and connections to 2D CAD systems. Details can be found in the chapter "Graphic output, CAD systems".

Connection of calculations.

Interconnection on more calculations including your own tables can provide solutions of more complex tasks. MS Excel Help provides information on how to interconnect workbooks. Some recommendations are given below.

• Copy the workbooks that have to be interconnected into a special directory.
• It is good to create a new (superior) workbook, from which you will control the input cells of the calculations and, at the same time, enter the input parameters into this workbook that have to be monitored and evaluated. This improves the arrangement.
• More interconnected workbooks can be opened simultaneously using a command from MS Excel by saving the desktop in the "File" menu.