This Excel calculation can be downloaded by ExcelCalcs subscribers.Please login or Subscribe.


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.

Wind 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.

Steel Design (AS4100)
Functions for assessing resistance or capacity of steel sections, and bolts.

Cold-Formed 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.

Timber/Wood 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.

Text Strings
Ripping files paths apart. Padding strings, checking character types, checking for numbers.

Debugging 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
| Find on | Find on | Find on | Find on | Find on |

Version History

Check the version history to see how this calculation has changed over time.

Submitted On:
28 Jan 2016
Submitted By:
File Date:
07 Apr 2014
File Author:
Conrad Harrison
File Version:
File Size:
1,118.00 Kb
File Type:
stars/1.gifTotal Votes:1
HTML Link:
Copy code below to your web page to create link to this page:
HTML Window:
Copy code below to your web page to create a dynamic window to this download:
Like This?:
View the profile of the person who submitted this calculation and see all their other calculations hosted at ExcelCalcs.
Need Help?:
Use the comment feature below to raise any questions relating to this download. The question will be automatically emailed the author and all users subscribing to this comment thread.


#8 InfoJunkie65 2018-03-01 11:24
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.
#7 agexcel27 2018-03-01 09:13
Hi Conrad, I have got myself into a muddle in using your spreadsheets. I believe I have installed the schtechlib add on on excel 2007 and even though it says that is it loaded I do not see it in the Add ins tab and unfortunately most of the spreadsheets come up with #name everywhere. I have tried to sort it out but it is beyond me. Can you please help. Cheers....Angelo
#6 InfoJunkie65 2016-09-25 06:45
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.
#5 InfoJunkie65 2016-02-18 03:35
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.
#4 JohnDoyle[Admin] 2016-02-17 18:25
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.

Please sign in or register to add a comment.

We have 8 guests and 38 members online
Contact Us
post/emailEmail (preferred method of contact)
telephone US +1 617 5008224
telephone EU +44 113 8152220
Our Feeds
Repository RSS. Forum RSS. User Comment RSS. News RSS.



Real Time Analytics