Setting Up Spreadsheet Environment

General instructions for setting up environment for use of my spreadsheet collection, either downloaded from here or from ExcelCalcs. To test the installation, I create a new limited user account on MS Windows. I have done this previously for Windows XP, and Windows 7. The current process was on Windows 10, which is an upgrade of my Windows 7 installation, therefore I simply used the test account previously created under Windows 7. I renamed the original “eCalcs” installation folder to “eCalcsTest1”, and then captured screenshots as I went through the process of setting up a new “eCalcs” system.

[toc]

Setup Suitable Folders

In “My Documents” or Documents create a folder called “eCalcs”, and unzip the spreadsheets into this folder. The zip file should contain a subfolder called “Materials”, which will contain centralised materials data referenced by other workbooks.

Main eCalc folder
Main eCalc folder

 

Materials Subfolder
Materials Subfolder

Excel Options

Once  setup up the folders, do not open any of the spreadsheets, instead open MS Excel with a blank workbook. Then go to files/options/Customise ribbon, and make sure that Developer tab is switched on.

developermenu
Switch The Developer Tools Ribbon Tab on

 

Developer Tab select Excel Add-in's
Developer Tab select Excel Add-in’s

Making Excel Add-in Available

Click browse button, and move to the folder where the schTechLIB.xla add-in is located.

selecttechlib
Select the schTechLIB.xla Add-in

Once the add-in is loaded tick the check box next to it, to make it active.

add-inselected
The schTechLIB selected and active

Using the Technical Library Add-In

Check that the library functions are available. In a blank workbook Click in a cell and press the fx button.

Select user defined functions
Select user defined functions

Then scroll down the user functions and select a function (eg. AS4100_getMs)

Select user defined function
Select user defined function

 

Function input parameters
Function input parameters

Fixing External Links in Workbook

If the above is working can now open a specific workbook such as schDsgn4100.xls. Do not allow the spreadsheet to up date links: it is faster to open the dependent file first.

donotupdate
Do not update

Check the links are correct and pointing to the correct files. (Data/edit links) It is often faster to have the correct workbook open before updating the links. For example open the version of schStruMtrl.xls which wish to use, then edit the links of the dependent workbook and change the source. The linked cells will update faster from the open workbook than from the closed workbook on disk.

Materials Library Link
Materials Library Link

 

schTechLib link
schTechLib link

 

Enable the content of the spreadsheet
Enable the content of the spreadsheet

Check Configuration of Workbook

Check the configuration parameters of the workbook. These give the paths to the materials library, which are used by the open library button, and by the DAO functions used by the AS4600 workbook. (If not using AS4600, there is an alternative version of the technical library schTechLibV2, which excludes the DAO reference in vba).

Check configuration parameters
Check configuration parameters

 

Open the materials library
Open the materials library

Data Validation Lists

Now MS Excel does not allow the creation of drop down lists or validation lists referencing data in another workbook. My spreadsheets started in Borland QPro, using Paradox data tables for Vlookup, and using dialogue input boxes, then converted to MS Excel 1997.  The current workbooks were created by copying and pasting and moving worksheets around. Neither MS Excel 1997 or 2003, had a problem with copying data validation when using named lists based on external data: and I was unaware that my data validation lists were non-conformant, until later came to create new validation lists.

MS Excel 2016 however doesn’t allow copying cells from one workbook to another and retaining the non-conformant validation lists . It also appears that 2016 version also loses the drop down  validation lists when it saves to 2003 format. The current versions I opened in 2016 and saved, but didn’t notice loss of validation lists. To put them back need to save the files to 2016 format. Since the workbook contains the vba macro to open the materials library it needs to be saved as a macro enabled workbook (.xlsm). 

Save workbook as macro enabled
Save workbook as macro enabled

 

Check SectionList defined in the materials workbook
Check SectionList defined in the materials workbook

If sectionList not defined then create. If it is defined then check that it includes all the current sections in the materials workbook.

Select List of Section Names include column header
Select List of Section Names include column header

Create a named range which points to the section name list. {Note the increase in the number of rows 731 to 986. If new sections are inserted above the last row of the named range, and both workbooks are open, then the named range will be automatically expanded.}

create named range sectionList
create named range sectionList

 

Create data validation list based on sectionList
Create data validation list based on sectionList

 

Drop down validation list created in materials workbook
Drop down validation list created in materials workbook

Once created the data validation list, drag the worksheet into the design workbook. In the design workbook remove the merged cell format. Then copy the data validation cell into the correct location.

Arrangement of workbooks in preparation for dragging validation list worksheet to design workbook
Arrangement of workbooks in preparation for dragging validation list worksheet to design workbook

 

Design Workbook with data validation worksheet (Sheet2)
Design Workbook with data validation worksheet (Sheet2)

 

Remove the merged cells formatting from the section name on the calculator worksheet
Remove the merged cells formatting from the section name on the calculator worksheet

 

Section Name list after copy/paste of data validation
Section Name list after copy/paste of data validation

Merge the cells and apply uprotect style.

Apply the unprotect style to the input range
Apply the unprotect style to the input range

 

Section name field after cells merged and unprotect style applied
Section name field after cells merged and unprotect style applied

TIP: Use Headers in Lookup Tables

If the default description of SECTION is chosen from the drop down list then all the vlookup functions return the field names (column headers) from the materials database, providing a quick check on the correct data being retrieved.

Select suitable structural section

Select suitable structural section

Everything should now work. It should be noted that the materials library needs to be open for the calculations to be up to date. The materials library also needs to be open for the drop down list to work.

Additional Requirements for AS4600

If using the AS4600 design spreadsheet then additional steps are required to enable DAO. Information for setting up AS4600 was first documented on my personal metamorphs blog on blogger:

  1. Steel Design to AS4600
  2. My spreadsheets DAO and 64 bit Windows 7

This spreadsheet uses DAO to obtain section property data from a MS Access database. This allows the functions to operate by the passing of a simple section name, from which all dependent properties are then retrieved. These functions are wrappers for other functions which require passing long lists of parameters. Though some of these functions make use of user defined types (Type … End Type), to reduce the number of parameters  passed: such functions cannot be used in a worksheet.


Notes:

  1. I use a different set of workbooks than those I released. The workbooks I use are all linked to a common library, central to all users of a given computer. At one stage I did have the library central on file server: but if file server crashes, or laptop not connected to the network then the workbooks won’t work.
  2. MS Excel automatically updates drive references, when a file is copied from one drive to another. To avoid links  being altered use “save as” within Excel. For example, when working on a project file on laptop and want to copy to central project folder on file server: then don’t copy, use “save as”.
  3.  I’m working on converting to Excel 2016. I was considering having one users documents/eCalcs system, but that won’t work for me. I need a centralised library, which project files on the central file server can link to.
  4. I prefer to separate data from calculation engines. I therefore do not like workbooks which replicate and cart all the vba code around from design project to design project.
  5. dsgn4100, dsgn4600, dsgn1720 are all quick calculators. These workbooks are not expected to be copied to projects as they don’t produce reports. I have product specific workbooks for reporting (eg. balustrades, sheds, canopies, platforms, stairs etc…). I may therefore consider adding the relevant vba code and materials  data to these quick calculator workbooks and making stand alone.
  6. Originally had separate vba workbooks for AS1170, AS4100, AS4600, xmaths, xstrings typically following my approach in Turbo Pascal and Turbo C. But there are both worksheet links to be updated and vba references to update when files are moved around. So approach doesn’t work the same as with high level programming languages. Up until a few weeks ago, the only workbook still using this approach was AS1664pt1 limit state design of aluminium structures: this now added to the technical library, but not yet released.
  7.  Also looking into converting to LibreOffice. Not yet figured how to convert the Technical Library to LibreOffice: macro library seems more centralised but so far functions do not appear to be visible to workbook.
  8. I spend equal if not more time in the Visual Basic for Applications(VBA) Editor (VBE) as I do working in worksheets. I don’t like long complex worksheet formulae, I prefer to replace with vba functions. VBA functions can be built upon and used outside of Excel. VBA is also the best way to get data from other application software into Excel, or out of Excel and available for use in other software. Though that being said I have generated AutoCAD/LT script commands in worksheet cells, and also MicroStran arc file data in worksheet  cells. Though this approach requires manually copy/pasting data to a file, whilst the vba approach can automatically create a file and start the other application and load the new file.
  9. Also sorry, but when I release the revised workbooks I actually use, whilst they will remain libre they won’t remain gratis. {Currently a lack of structural design work in our usual areas of practice. So if interested in custom design and reporting spreadsheets contact me.}

Revisions:

  1. [24/09/2016] : Original
  2. [25/09/2016]: Added Headings. Revised creation of data validation lists based on external linked data. Added notes.