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.

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

23 Mar 2018
File Size: 1,847.82 Kb
Downloads: 335
File Version: 6.0
File Author: Conrad Harrison
File Rating (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.
Comments: 11
infojunkie65 6 years ago
To illustrate here is the function for calculating phi.Mb to AS4600, using DAO:
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.
infojunkie65 6 years ago
I have updated the submission with a zip file. It has the required folder structure, and also contains the MS Access materials database, along with a quicktest.xlsx spreadsheet.
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.
infojunkie65 6 years ago
Hi Angelo,
I'm not familiar with Excel 2007, I went from 2003 to 2016. The files are all Excel 2003.
The following links may help:
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.
infojunkie65 8 years ago
Have prepared a blog post with general information on setting up MS Excel environment for using my spreadsheets. Activating the add-in, updating the links, and making modifications which may be caused by using other versions of Excel. The spreadsheets are created in Excel 2003, the post is based on opening files in Excel 2016.
infojunkie65 8 years ago
The file is an .xla add-in. It was created in Excel 2003, but still works in Excel 2016. In Excel 2016, and some earlier versions need to switch the "developer" ribbon on. On the developer ribbon will find Excel Add-in's, click this and browse for the xla file, select and add to the add-in's, it should have a tick next to schTechLIB.
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.
johndoyle[admin] 8 years ago
This file contains some very useful VBA code which many of Conrad Harrison's calculations use. If you are new to VBA then take a look at the [Microsoft's Getting Started Guide](
infojunkie65 10 years ago
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.
infojunkie65 10 years ago
I have searched the modules, and removed the hardcoded paths that I could find. I have added a worksheet with environment parameters. This basically uses worksheet functions to get the name of the workbook and obtain the path to the workbook, paths to materials databases are then built up from there.
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.
infojunkie65 10 years ago
A blog post listing the functions and subroutines contained in the library as opposed to the vba modules shown here.
infojunkie65 10 years ago
For those who have reached their daily download limit and need this to run my other spreadsheets, here is an alternative source:
infojunkie65 14 years ago
New functions added to assist with calculating windloads in the quadrants about the orthogonal axes of a building, relative to the 8 cardinal compass directions.
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.
Web Analytics