Andrey Stolyarov - 2014-09-02

Quick Start

1.There are some preliminaries necessary before you can get the program to work with Excel. The Excel must be set up for access to VB projects. To do this, activate the check box "Trust access to Visual Basic Project". In Office 2007-2013 it is located at "Microsoft Office button\Application Options\Trust Center\Trust Center Settings\Macro Settings". See the Installation and uninstallation notes and Microsoft Office Online for details.
2.Execute the file 'Excellator.exe' to start the program.
3.Just use the Demo to start.

Summary

Excellator is a program used for engineering calculations in MS Excel. The program allows you to work with formulas and variables in text format without using of cell addresses. It is possible to create a report in MS Word. The formulas are displayed in text format.

Excellator automatically generates a list of variables and calculation table based on a list of formulas.

Worksheet can be saved in MS Excel format and can be used independently from Excellator. Worksheet to be updated automatically when the source data are changed.

With the Excellator application are available options:
•change and add new formulas;
•generating reports in MS Word.

License information and Contacts

This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

Download

You can download the application from
http://excellator.sourceforge.net
The "Excellator latest.zip" file is highly recommended to download. The latest version of program always stored in this file.

Contacts

Please leave a comment on Excellator's forum on Sourceforge or contact me via email (tkr14@ya.ru). Please excuse my bad English and help me to improve this text and interface translation table.
Andrey Stolyarov

Multilingual support

Starting from version 1.2, Application has a translation table for its interface and supports local versions of MS Office. Interface translation table is stored in "Translations.txt" file. This file can be edited with MS Excel. To add a new language, you need to add another column to the right of the table. You can edit all columns except the first. The first column (in Russian) is used to identify the terms and can not be changed. The following lines should not be translated because they are the names of files and styles:
"License Information.txt",
"template\excellator-en.dotx",
"\htmlhelp\rus\htmlhelp.html",
"\htmlhelp\rus\ExcelString.html",
"Normal",
"Formula - center",
"Formula - right",
"Table header",
"Table",
"Calculation".
Starting from version 2.1, "translations.txt" file is saved in Unicode encoding.
As an example Hindi, German, Arabic, Persian, Spanish languages columns added to the file "Translations.txt". The translation the translation was done automatically by google translation. So I will be grateful for corrections. Please send the corrected file "Translations.txt" to tkr14@ya.ru (To Andrey Stolyarov).

Installation and uninstallation

Excellator program does not require special installation and works only in its directory. The program does not use system registry and does not create files in system folders. So uninstallation of the program is not required. To uninstall the program is enough to remove its working directory.
Important! Because MS Office blocks macros operations for security reasons, please perform these operations:
1.Allow adding macros to Excel workbooks.
To do this, activate the check box "Trust access to Visual Basic Project". ◾In Office 2003 or Office XP it is located at: "Tools/Macro/Security/Macro Security/Trusted Sources".
◾In Office 2007-2013 it is located at "Microsoft Office button\Application Options\Trust Center\Trust Center Settings\Macro Settings".
For more information, see the Microsoft Office Online .

2.For Office 2003 or Office XP change the template in the Settings "\template\Excellator-en.dotx" to "\template\Excellator-en.dot ".

Procedures for working with Excellator

1.Start MS Excel. Open a blank sheet.
Use originally generated by Excellator or blank sheets. It is strongly recommended to close third party documents in MS Excel to avoid damaging them. (Normally the program should prevent damaging of the third-party documents, but an error is not eliminated.
2.If you open a blank sheet, click the left button "Analyze".
The table header will be created automatically, including columns:
Variables (Description, Unit)
Calculation (Formula, Substituting numbers, Result)
You can enter the name for the calculation In cell A1. This name will be transferred to MS Word when a report will be generating.

3.Write down the mathematical expression in the column Calculation / Formula. Expression must be written in text form as a = f (b, c, d, e ...) in order of calculation.
For example:
ω=n/30Pi()
M_e=N_e/ω
MS Excel functions are allowed, for example:
F_p=Pi()DD/4
D=sqrt(F_p/Pi()4)
Formula analysis stops at an empty cell. So the column of formulas is to be filled from top to bottom with no empty cells.
4.Click on the button "Analyze".
As a result, are automatically filled:
1) a list of initial data in column "Formula",
3) cells with calculated formulas,
4) cells that display the formulas with the substituted values.
5.Fill the values of the initial data in column "Result".
If you change the source data, then the results and formulas with the substituted values are recalculated automatically by MS Excel. This feature is independent of the Excellator program .
6.Fill the cells with the descriptions of variables and units of measure.
7.Click on the button "Export to MS Word" to create a report in MS Word.
Every time a new document is generated by MS Word, which is not related to the Excellator application.

Detailed description of functions of Excellator

Excellator interaction with MS Excel

1.All file operations (create, open, save) realized through MS Excel.

2.Excellator performs operations on the currently active Excel sheet. An Excel workbook can contain any number of sheets, including sheets not for Excellator.
3.It is recommended to backup Excel files.
4.An excel file needs to be saved as .xlsm because Excellator adds to file a macros needed to improve the display of numbers.

5.When you click the "Analyze":
◦If the worksheet is empty, it is built header table and the book is written the necessary macros, if they are absent;

◦If the worksheet contains an excellator table, it is rebuilt;

◦If the worksheet contains information other than an excellator table, no operation is performed.
6.Notwithstanding the requirements of the local version of MS Office, is necessary to write functions at MS Excel expressions in the English form. You can find the list of English names of Excel functions at office.microsoft.com. For more information see Additional Information about Localization of names of Excel functions.
7.Besides the standard functions of MS Excel, you can use additional functions that are added to a file with macros.These are the functions: inv, ainv, linapprox, piecewise.

"Excel Input String" dialog

Dialog "Excel Input String" is designed to simplify entering names of functions and variables. Contents of the currently selected Excel cell (only in $C column) is duplicated in the field. Cell should not be edited in Excel at this moment.
Use the buttons F,α "Functions and Greek letters" and Var "Variable select window" to accelerate typing.

Use up/down arrow keys to move between cells.
You can insert a new line by button Insert String Button.
By buttons Undo/Redo You can undo/redo your changes until you change the active window.

If the input field is empty, connect to Excel with the button "Retry".

Expanding the set of functions of MS Excel

inv function###

inv(x)=tan(x)-x.

ainv function

This is the inverse function of inv(x).

linapprox function

linapprox(x,x1,y1,x2,y2,x3,y3,x4,y4...) - this is a simple linear approximation of dependence given by the set of points. Function is extrapolated outside the region of the definition.

piecewise function

piecewise(cond_1,res_1,cond_2,res_2,...cond_n,res_n,res_oltherwise)
With the piecewise function it is possible to express piecewise-continuous functions. The semantics are as in a case statement: if cond_1 is true then res_1, else if cond_2 is true then res_2, and so on. res_otherwise gives a default case which corresponds to all conditions being false. The default for res_otherwise is 0.

"Replace in variables" dialog
This dialog allows the substitution of variables in formulas.

Additional Information about Localization of names of Excel functions

By Niek Otte from www.office-archive.com

Function and command names are stored internally in Excel in this native language. Presentation
on the spreadsheet is in local language and with the regional setting of Windows (like list seperators and date formats).
You can always find out the translation between the two if you have a local version of Excel:
Type a formula (in the local language of course) in the spreadsheet. Go to VBA, the immediate window and type ?range("A1").formula. Now you see the English version of the formula.
The other way around: In the immediate window, type range("a1")="=rept(""aa"",10)"
Now look at the spreadsheet and you will see the local version of the fomula. The double quotes are to get quotes within quotes.
If you so wish, you can represent different addressing styles (R1C1 or A1), regional or international settings, local or international language, etc.

Excellator interaction with MS Word

1.When you click "Create report in MS Word" Excellator first performs an operation "Analyze", and then generates a report MS Word.
2.The report is created as a new document and has no feedback to the Excellator program.
3.The text of the document is created using styles "Calculation", "Formula - Center", "Formula - right", "Table" and "Table Header".

4.Styles can be adjusted in the template "template\excellator-en.dotx".
5.By default (If the template is not found):
◦style "Calculation" has a property keep with next;

◦style "Formula center" has centered alignment, and font Arial Narrow, which is most suitable to display the formulas;
◦style "Formula - right" is different alignment to the right, that is used when printing the numbering of formulas;
◦style "Table" is the middle alignment and zero padding;
◦style "Table header" has a property "keep with next".

6.Each formula is added number in parentheses. Number is a field MS Word {SEQ f}. Where f - is the name of the numbering sequence of formulas.
7.Each bookmark added to the formula number. Bookmark name is "f_". Bookmarks can be used to refer to the formula in the text of the calculation. To do this, insert the field (Ctrl + F9) with the name of the bookmark. You can see the name of the bookmark will be placed near the cursor and pressing Ctrl + Shift + F5.

8.Number is added to the table before its title. Number is the field of MS Word {SEQ t}. Where t - is the name of the numbering sequence of tables.

Multi-step and variation calculations

The button “Multi-step and variation calculations” opens the corresponding control elements on the main toolbar.

Variation calculations are supported by selecting the active column. The current active column uses for calculating and creating a report. Also you can create alternate versions of calculation if you select the cells in the Result column and drag it to the right. Then you can edit various initial data in the different columns and select any column is active.

Multi-step calculations are supported by setting the number of columns to be filled automatically. If this number is greater than 1, then cells in the next columns refers to the corresponding cells in the previous column. In general case the values of the initial variables are stayed the same. A special case is formulas like α=α+Δ. If the value of an initial variable was changed in the calculation, then the corresponding cell in the next column refers to the last new value of the variable. This allows organizing calculations based on the cyclic algorithms.

Special dialog helps to create a chart based on the results of multi-step calculations. It is necessary that “The number of the columns to be filled automatically” must be greater than 1 for creating a Chart. Also it is necessary than one of worksheets must be active at that moment when chart is creating. You can also to combine the data from other worksheets on the chart if these worksheets are identical to the active worksheet. You can add to the names of the series the values of selected variable that helps to differentiate series based on the data of the different worksheets.
To coping of worksheets and modifying its initial data provides the Variation of the Multi-step calculations at the same time. Creating charts based on the different worksheets provides the results comparisons.

To quick start see the demo on the Multi-step and variation calculations .

Launching the Program with Parameters

You can launch program with following parameters:
•analize - starts the analysis procedure of current MS Excel page;
•toword - starts to create a report in MS Word.

After performing a given operation, the program closes. Since the program in this mode must work automatically, no error messages are displayed. To see the error messages, you must run Excellator no parameters.

Quick Launch shortcuts

Pressing the "lnk" is the ability to automatically to generate icons that launch the program with parameters "analize" and "toword". These icons are created in the working directory, and you can manually move (drag) on the Quick Launch toolbar. To delete a shortcut from the panel, drag them to the Trash.

Errors and issues

1.You can not edit Word or Excel documents during the report is exported to MS Word. This may cause to the collapse of exports. As a result formatting the document may stay unfinished.
2.Error "Call was canceled" is possible. This is a natural situation, that Excel does not provide access to its interface while the user edit the cell content. You must move the cursor to empty cell.
3.Crash while create a new worksheet arises because You need to allow access to Visual Basic Project.
4.The template at directory>\template\Excellator.dotx may be blocked because he had received from the Internet. To unlock it open and close it without saving.

 

Last edit: Andrey Stolyarov 2014-09-02