Fun with functions…. Improving Excels native REPLACE() function

You know what annoys me about Excels built in REPLACE() function, it does not work like the equivalent VBA function Replace() function. It’s that simple and that is enough of a reason to think twice about using it if you just want to replace a sub-string of a string with another string. You can do it of course, but it’s overly complicated… there is an easier way. What exactly am I talking about here? Well, the built in Excel function…

VBA functions for determining (and graphing of) moment, shear, and deflection for a simply supported beams +/- cantilevers (Part 2)

One thing to state up front, the shear, moment and deflection are all based on the loads given, so if they are factored ultimate loads then the moment, shear and deflection are factored. Which for the deflection might not be too helpful? Usually, you’ll want to know the deflection under a different case than the strength requirements. But you can always use unscaled loads and scale the moment and shear appropriately afterwards and build up your own load combinations. I’ll…

VBA functions for determining (and graphing of) moment, shear, and deflection for a simply supported beams +/- cantilevers (Part 1)

Today we bring you moment, shear, deflection stuff! Sharing some basic VBA functions for determining the moment, shear and deflection of a simply supported beam (with or without cantilever) under almost any typical loading scenario imaginable. Point loads, variable length uniformly distributed loads (constant, triangular, trapezoidal), etc [the only thing not implemented is a point moment, because it’s not that common in typical beam design… never needed it, not that practical]. I created these because I tend to start off…

Fun with functions….Use cases for Excels LET() function

If you’re not aware of what the LET() function is, prepare to be informed (and get out from under the rock you’ve been hiding under!). The LET function was introduced in July 2020, and I must admit it wasn’t until this past week that I actually started using it in anger, and now I can’t stop myself using it. I quite like what you can achieve under certain use cases and I think I’m going to use it a lot…

Lambda functions coming in Excel!

Well, it’s not often I wake up at 4am and get excited about Excel formulas. Today was one of those days though. There on Google was the recommendation for a link saying on sheet lambda functions are on their way to Excel…. Currently in beta. This is pretty cool, can’t wait to test it out. It almost brings the full power of defining your own user defined functions in VBA to an on-sheet equivalent. Really looking forward to seeing how…

ADRS curve generation functions for the New Zealand seismic code (Part 4)

Following on from the previous blog posts in this series that provided functions for calculating the NZS1170.5 seismic coefficient. I’ve added some further functions to the GitHub repository that utilise the previous functions to generate an ADRS curve in accordance with the provisions of NZS1170.5 and the draft Seismic Assessment of Existing Buildings guidelines produced by MBIE in NZ. What is an ADRS curve you might ask, well it’s an Acceleration Displacement Response Spectrum, and they look like the below…

Excel…. Version control…. Git the hell out of here!

Version tracking Excel VBA code with git…. I’m not talking about uploading your spreadsheets to GitHub and calling it a day…. boring, we can do a bit better than that obviously! I’m talking about extracting your actual VBA code from your spreadsheets and tracking changes to that code using git. Well, that’s a pain you might say, extracting/exporting the code modules from Excel manually each time you make a change. Well, I couldn’t agree more, it would be a major…

Functions for calculating the New Zealand seismic coefficient Cd(T) and associated parameters (Part 1)

As exciting as it is calculating seismic coefficients (it’s not), you get sick of doing it by hand and implementing it time after time in various spreadsheets. Theres nothing worse than seeing someone calculate their seismic coefficient wrong on page 2 of their calculations as well, though it always gives me a laugh that they went on and did 403 pages of calculations based on the wrong seismic load…. laugh it happens.

Moment coverage, do it, and do it right (please!)

Something that really irks me when checking others concrete designs, is the apparent lack of knowledge surrounding moment or tension reinforcement development. Along with the concept of moment coverage diagrams to prove that all regions of your member have sufficient moment strength. Isn’t this the point of designing the beam….. Arrgghhh!

Stuff I should have known earlier … shortcuts…

The other day I learned a useful shortcut in windows, being that the Windows key + SHIFT + S opens up the snipping tool in windows, very handy. This is something I always used to open manually with a search for via the start menu, very cumbersome right. This got me thinking, what other things am I ashamed to admit I found out way after the rest of the world apparently did, and way after I’d like to admit after…

Metric for the win…

I’m not a fan of the imperial system when it comes to engineering calculations, I make no secret of this. Nothing is easier than multiples of ten in my mind, it just makes sense. As opposed to a 12 here and a 3 there, and the whole mass and weight thing which I didn’t even realise existed…..

Doing some cool stuff with JavaScript in Bluebeam (Part 2)

In part 1 we added a single line of JavaScript, it was basic. But hopefully that single line of JavaScript provided some inspiration on how you could make other stamps you might create dynamic, and less specific. In this post we’ll do another example, but this time I’ll show you how to use some data input by the user when you place a stamp to do some calculations using JavaScript.