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

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

Comments  

 
#11 InfoJunkie65 2018-03-24 04:10
To illustrate here is the function for calculating phi.Mb to AS4600, using DAO:


Quote:
Function AS4600_phiMb(E As Double, G As Double, SectionName As String, _
Lex As Double, Ley As Double, Lez As Double, _
phi As Double, cb As Double, _
SectionType As Integer, BuiltUpType As Integer) As Double

'Section properties Database
Dim tbl As Recordset, StrCriteria As String
Dim dbs As Database
Dim fn As String
Dim Beam1 As TStructMEMBER

fn = MtrlDB

Set dbs = DBEngine.Workspaces(0).OpenDatabase(fn , , True)
Set tbl = dbs.OpenRecordset("Sections", dbOpenDynaset)

Beam1.Sect.descr = SectionName
Call getBuiltUpSectP rop(tbl, Beam1, Beam1.Sect.descr, SectionType, BuiltUpType)

'Effective Section Properties
With Beam1
AS4600_phiMb = get_phiMb(E, G, .Mtrl.Fy, .Sect.d, _
.Sect.b, .Sect.Tf, .Sect.ri, .Sect.dL, .Sect.Area, _
.Sect.rx, .Sect.ry, .Sect.xo, .Sect.yo, _
.Sect.Zx, .Sect.J, .Sect.Iw, _
Lex, Ley, Lez, _
phi, cb, _
SectionType, BuiltUpType)

End With

End Function


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.
 
 
#10 InfoJunkie65 2018-03-23 07:24
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.

regards
Conrad
 
 
#9 agexcel27 2018-03-21 15:00
Thanks very much for your. Unfortunately I still can not get your programs to work within excel. I am running excel 2007 in a windows 7 machine. Strangely, the previews in windows explorer preview pane, of the files that contain the schTechLIB.xla add in, look perfect but when I try to run in excel I can get no satisfaction, as the song goes. I have tried manually updating the links to the addin files all to no avail. Maybe there is a simple answer but unfortunately it is beyond me.
 
 
#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:
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.
 
 
#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
 

Please sign in or register to add a comment.

We have 11 guests and 45 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