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

Background
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.
Contents
Wind Loading (AS1170.2)
Originally written for AS1170.2:1989 and revised for 2002, and 2011 versions. There is some similarity with ASCE705 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.
ColdFormed Steel Design (AS4600)
Functions for assessing resistance or capacity of coldformed steel sections, and bolts. The calculation of effective section modulus (Ze) is an iterative process, functions are available for calculating Ze for csections. 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.
Beams
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.
Mathematics
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 Amazon.com  Find on Amazon.co.uk  Find on Amazon.fr  Find on Amazon.de  Find on Amazon.ca 
Version History
Check the version history to see how this calculation has changed over time.  Submitted On:
 23 Mar 2018
 Submitted By:
 InfoJunkie65
 File Date:
 23 Mar 2018
 File Author:
 Conrad Harrison
 File Version:
 6.0
 File Size:
 1,847.82 Kb
 File Type:
 zip
 Downloads:
 289
 Rating:
 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.
Comments
Quote:
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 hotrolled 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.
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/steeldesigntoas4600.html
http://metamorphs.blogspot.com.au/2014/04/myspreadsheetsdaoand64bitwindows7.html
https://miscion.com.au/settingupspreadsheetenvironment/
https://miscion.com.au/wordpress/wpcontent/uploads/2016/02/SpreadsheetEnvironmentIssue1.pdf
The pdf file is the most recent and covers Windows 10 and Excel 2016.
Addin'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.
RSS feed for comments to this post