schTechLIB
(4/4)
Description
My general preference is to use VBA functions over writing complex
worksheet cell formula. Such functions make the creation of tables
simpler and more reliable. More importantly VBA is useable across a
variety of applications, so onced solved a problem using vba it can be
used in multiple locations, and if need be readily translated to other
programming languages. Originally I had the library in
multiple workbooks, such as xmaths.xls, xstrings.xls, as1170.xls etc...
the problem with that approach was that it created
complicated chains of links due to the dependencies. By putting all the
functions into one workbook, only one link needs updating.
The library provides functions and procedures concerned with structural
and mechanical design, mathematics and string manipulation. Some
functions are already available as MS Excel worksheet functions but not
directly available in VBA, whilst the worksheet functions can be called
in VBA that wouldn't be very efficient when working across
applications. Other functions may also be redundant as have now been
added to VBA or are available through filesystem objects: again the
purpose is to work consistently across programming languages and
environments. When moving to other programming langauges one of the
first things I do is translate the xmaths and xstrings libraries: which
often makes some of my functions little more than aliases. The
advantage being that the main program remains similar across
programming languages, with minor synatax changes needed.
The other benefit of VBA functions is that they simplify reports, as a
intermediate calculations and conditional testing can be hidden behind
the scenes. I contend that textbook style calculations are
only required when learning a calculation procedure, or otherwise when
messing with algebra: at all other times just need the decision
critical numbers. Error checking is a different process than design and
should use different tools for comparative checks.
So using the VBA functions can create quick calculators using
worksheets as input forms, or setup more detailed design reports, and
have some level of consistency because the calculation process is in
the VBA not the worksheet setup. Also calculations can be wrapped in
more complex iterative loops than can be achieved directly in a
worksheet: for example find the limiting height versus span envelope
for a gable frame shed when using a given structural section.
Loading (AS1170.2)
Originally written for AS1170.2:1989 and revised for 2002, and 2011
versions. There is some similarity with ASCE7-05 so some of the
functions could be tweaked.
The functions return wind speeds, multipliers, pressure coefficients
for enclosed buildings and free roofs (canopies). Most of the functions
permit linear interpolation between table rows and columns. There are
functions for getting maximum wind speed in a sector when checking all
8 cardinal directions.
When working with local pressure extents, and magnified
pressures on areas a/2 x a/2 and a x a, there are functions to
determine an equivalent uniformly distributed pressure(UDL) so that can
use girt/purlin manufacturers load capacity tables.
For longitudinal (theta=90) wind loading, there are functions to
determine the equivalent pressure coefficient at a given frame,
assuming the frames have consistent spacing.
For stepped wind loading across a roof, there are functions to
determine the partial loads on each side of a gable roof and determine
an equivalent UDL for each slope, based on maximum moment when roof
considered as simply supported beam. This is useful when using rigid
frame formula, such as Kleinlogel and only have formula for UDL.
Design (AS4100)
Functions for assessing resistance or capacity of steel sections, and
bolts.
Steel Design (AS4600)
Functions for assessing resistance or capacity of cold-formed steel
sections, and bolts. The calculation of effective section modulus (Ze)
is an iterative process, functions are available for calculating Ze for
c-sections. There are typically two types of functions those which
require input of sections parameters, and those which take a section
name and retrieve all properties from MS Access database tables using
Data Access Objects (DAO): this more reliable than using vlookup and
getting correct numbered column. It is also less cumbersome than using
multiple vlookup, index, match, and indirect to retrieve fields by name
from a worksheet.
Design (AS170)
Functions for assessing resistance or capacity of timber sections, and
assessing joints: nailed, screwed and bolted.
Functions for common beams: simply supported, fixed ended
(encastre), overhanging, and cantilevers. Functions mainly for bending
moments and deflections, purpose to calculate values across the span of
a beam for different load types and then sum the results to get the
point of maximum moment or deflection.
Areas, Logs and Exponentials, trigonometry, angles from degrees to HMS
and from HMS back to degrees, components of rectangular and
polar coordinates. Linear Interpolation, and equations of a line.
Strings
Ripping files paths apart. Padding strings, checking character types,
checking for numbers.
and File Management
Some procedures for opening files, checking existence of files when
working with DIR command. Functions to assist with debugging, tracing
to either worksheets or log files.
* Note this library needs to be loaded into Excel for many of calculations published by this author.
Calculation Reference
Wind design
Calculation Preview
(4/4)
Full download access to any calculation is available to users with a paid or awarded subscription (XLC Pro).
Subscriptions are free to contributors to the site, alternatively they can be purchased.
Click here for information on subscriptions.
Several versions of schTechLIB are included. The original file schTechLIB.xla, this requires a reference to DAO type library set in VBA editor under tools references. Password=disaster.
schTechLIBV2.xla doesn't require DAO, but this limits AS4600 to using functions which require a large number of parameters.
schTechLIB.xlam is an updated version of the library, to Excel 2016. Contains functions for Aluminium structures code AS1664 (limit state). Also wind loading functions for canopies have been changed, now require more parameters. Consequently its use will break some of the workbooks.
schTechLIB.xlsm is macro enabled workbook. If cannot get library working can change some of the reference paths in the workbook and then save as schTechLIB.xlam.
QuickTest.xlsx shows the simplest use of the functions for checking phi.Ms and phi.Mb to AS4600. If this doesn't work then chances are either DAO is not available or the function library cannot find the MS Access data file. Therefore need to adjust the reference paths. Though paths should be automatically updated relative to location of the library workbook.
regards
Conrad
I'm not familiar with Excel 2007, I went from 2003 to 2016. The files are all Excel 2003.
The following links may help:
http://metamorphs.blogspot.com.au/2013/11/steel-design-to-as4600.html
http://metamorphs.blogspot.com.au/2014/04/my-spreadsheets-dao-and-64-bit-windows-7.html
https://miscion.com.au/setting-up-spreadsheet-environment/
https://miscion.com.au/wordpress/wp-content/uploads/2016/02/SpreadsheetEnvironmentIssue1.pdf
The pdf file is the most recent and covers Windows 10 and Excel 2016.
Add-in's need to be loaded and activated (switched on). Materials workbooks need to be open.AS4600 design modules makes use of MS Access materials database using Data Access Objects (DAO) and VBA, the type library needs to be available.
Drop down validation lists may need recreating for your version of Excel.
Using functions from function library creates a link to the library, therefore may need to update links.
The pdf file explains most of the issues.
http://miscion.com.au/setting-up-spreadsheet-environment/
Once available, then when go to insert functions (fx), can select user defined functions and choose from the long list of functions available from this add-in and any other add-in's which make functions visible.
Some of the functions duplicate worksheet functions, this is because such functions are not available in VBA and referencing worksheet functions in vba is cumbersome.
I currently have the files in a folder called eCalcs under "My Documents". But the xla can be placed in the default Excel add-in's folder if prefer.
1) Added functions to AS1170.2:1989 Appendix E for calculating terrain category from surface roughness length (z0). Refer to design chart in current commentary to AS1170.2.
2) Added functions relating mean return period, risk and life expectancy. Refer to Wind loading of Structures by John Holmes for background.
3) Revised terrain category multiplier (Mz,cat) functions, so that works with region W, for New Zealand.
The assumption is that schTechLIb be placed in a folder called ExcelCalcs and below this another folder be created called materials, this is where the materials database will be located (.xls and .mdb).
I have further checked and only AS4600 functions use data access objects (DAO) to obtain section properties from the MS Access file. Even so it still requires the materials spreadsheet (.xls) to fill drop down lists and display information on the worksheet. Originally schStruMtrl.xls used exactly the same information as struMtrl.mdb as it was directly linked to and refreshed on opening. However that only works if everyone has the same version of Office: at one stage we had Excel97 and XP and 2003 in the office, and needed different versions of the access table and spreadsheet, so the link to the Access database was broken and the spreadsheet and made static so access to Access wasn't required. Note that DAO and also MS Query should be available on all Windows machines, MS Access is not required.
The functions using DAO are containers for other functions, rather than pass all the parameters required via the spreadsheet, just the section name is passed and DAO is used to get all the section properties from a database. These properties are then passed to functions with long lists of parameters. These other functions can be used in the worksheet instead if users do not like the DAO functions and cannot add new sections to the MS Access tables. However some functions to further reduce the parameter lists make use of record structures to pass information around, and functions so written cannot be called in worksheet cells as have no way of assigning values to the fields of the record structure.
Sorry, I've always used spreadsheets and databases together (Lotus 123 and Dbase), (Quattro Pro and Paradox), and now (Excel and Access). Traditionally spreadsheets had memory limits and databases didn't, this not so much as issue as it used to be. However it is still easier to manipulate data in a database than a spreadsheet: records and fields are more directly accessible than messing with vlookup in a spreadsheet. Plus a formula is written once and more reliably applied to all data, compared to copy pasting a formula in a spreadsheet. And if need formula to change based on contents of a record, then also easier to implement in a database management system. I once had to mess around processing railway curve data in Lotus, memory had to be removed from other computers to create one computer which could handle the data, and its was filled with messy formula. It would have been better in FoxBase which is what I recommended, but had to use Lotus because that's all the design engineer could use and limited license for FoxBase. With MS Office however I do not see the reason not to have the professional version with MS Access. Though vba and DAO does diminish the need somewhat.
Any case I have now tested use of schTechLIB (with AS4100 and AS4600) by creating folders on my profile under my documents, from there I copied and set up in shared documents. I also created a limited user account and under my documents and copied from the shared folders. I didn't time it but in each case I'm guessing it took less than a minute to set up and fix links: and that included time to void original locations on my computer so that would crash with bad links.
schTechLIB should be added to Excel (Add-in's) first, before any links are updated. If this is done then Excel will automatically update the invisible links to the user defined functions. Do not put schTechLIB in the Excel default add-in's folder it doesn't need to be there, place it in mydocuments/excelcalcs or any where else it is easy to find and remember. The dafult setting for schTechLIB is that it will find the materials database in a folder below where it is located. mydocuments/excelcalcs/materials.
http://metamorphs.blogspot.com.au/2013/12/schtechlib-contents.html
http://metamorphs.blogspot.com.au/2013/11/technical-function-library.html
The workbook already in '.xla' format.
To simplify conversion of my highly linked workbooks. I have assumed shared documents folder is available to most people, and setup ExelCalcs (spelt as shown) folder to place dependent files in. I assume this will allow the workbooks to be easily tested without modification before moving over into own systems.
Though still may not work. Excel has a habit of updating drive names and messing all paths up when a file is simply copied from one drive to another. To avoid such problems typically have to use Excel "save as" menu option.
Unfortunately I don't yet have installation or configuration macros, so some messing around has to be accepted to get the workbooks to work on another machine. Something which I have to do locally in any case: moving between office computers and network and stand alone home computers.
Microsoft Access is not required. It makes use of data access objects (DAO) which are available on all Windows systems. Though the location of the library is different for 64 bit Windows 7 and 10.
As4600_phiMb is a wrapper function for get_phiMb which requires a multitude of parameters. I wouldn't have ordinarily wrote such function with large number of parameters, I would just simply pass the record type as a parameter, however cannot pass record types to functions in a worksheet cell.
I could have wrote 3 levels of function. First using the record type, second using long list of parameters to pass to the first, and the third using DAO to get the data to pass to the first. Problem is dreaming up names for all these levels, added to different materials. Functions for AS4600 were written first, so no AS4600 prefix to the function names (except the wrapper functions). Whilst AS4100, AS1720, AS1664 are prefixed with code reference: which helps keep them all together in the user defined function (UDF) list.
The benefit of the vba functions is that with a 18 column A4 portrait worksheet I can calculate phi.Ms and phi.Mb on two lines, and only require one additional line for each additional segment check. Whilst original presentation required 1 A4 page for each segment check.
The vba functions and DAO make it easier to check multiple members and multiple segments in one workbook: allowing rapid design of whole structure. Parametric model for whole structure.
It allows a presentation as simple as using the AISC/ASI safeload or DCT's for hot-rolled sections. Few people present detailed calculations to the code in such situations so why do so for other materials. An independent check should just that, independent, the checker can use whatever tools they like to check to codes. As long as they know the inputs their blackbox should give the same results.
So can choose not to use the DAO version, but then have to get all the section properties for all the members into the one workbook.