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.
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.
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.
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.
This download is for Registered Users Only.
Perhaps you need to login or register.