Writing Functions for Engineering Calcs

Hi all,

Below is an example of a function in the module "Connections" that tests the allowable shear/tensile/etc. against the actual results. If I had to write a similar function repeatedly, what would be the best way for me to write the functions & the best way for me to pull the variable from user input data on a sheet.

All advice is welcome and appreciated. Thanks

Please Login or Register  to view this content.

Hi yes,

The idea (as of right now) is:

1. User fills out relevant cells
1. a - presses button or keyboard combination to run the subroutine that contains the location of the variable and feeds it into the function (the reason for the sub is because the variables will be used for more than just calculating one function)

2. Results output next to input cells

3. Outputs results
3. a. Results are stored for other functions on the sheet, i.e., creating graphics, charts, etc.

I don't completely understand you desired result, but from the function, it looks like you are trying to figure out if the criteria you enter into the cells results in an ok bolt, or an overstressed bolt. I don't know all the definitions of the criteria you are using so I don't know if this works right or not. You'll just have to try it and see. There is no button or macro to run. It's just a formula in cell D9 that results in either OK, or Overstressed.

Yeah, I appreciate your help and setting it up like that...
This calc checks the capacity of a "thru-bolt" similar equations would be for "weld, wood connection, adhesive, ballast mount, brick, steel plate, platform."

Each has a different set of input variables and outputs - in my "Structural Template," -

I have tabs that go to the section that the user clicks on - there, they fill out the information, and in the background, the results are formatted to go on a final report.

I do like the way you have it set up (named cells, I'd add a button to leave the formula off the page), do you think that's the best way to go about it at scale?

My questions are:
What's the best way to organize all of these functions in the VBA editor?
Should I have all the input variables defined as cells on a sheet?
Should I write individual functions?
Should I have one sub-routine that defines the variables as range. value?


I really don't want to make the same mistake of writing 50+ equations of a garbled mess as a result in the end.

The full process is as follows (code already written). I don't expect you to go line-by-line, but this will give you a good idea of the process. Thankfully the next 20+ functions are all mostly straightforward.

I wrote the code below, but I'm sure someone has done something similar in a far more organized way. I don't want to organize what I've already written, but I want something more concise moving forward - no codes required, just advice is perfect.

1. Functions that calculate structural stuff

Please Login or Register  to view this content.

2.Call functions & create a table:

Please Login or Register  to view this content.

3. Create a string of the equation and result:

Please Login or Register  to view this content.

4. Save the resulting diagrams as PNGs:

Please Login or Register  to view this content.

A few reasons:

1. I don't want formulas on the sheet at all.

2. Many employees will use this workbook & I want to mitigate them accidentally changing the formulas. I also don't want to lock thousands of cells to prevent this.

3. Congesting cells with formulas makes tracking the whole process more challenging (for me). I'm not just looking for the "OK" vs. "Overstressed" results - in this same thru-bolt scenario, the next step is to calculate "Tensile Stress Adjusted for Shear" - a piecewise function with five if iterations & equations with over seven terms per equation. I could write something like that in a cell, but I'd rather not.

This is an example of a longer function (below) do you think adding that to a single cell is easier? If you think so, you are probably right - I only started VBA coding ~ 6 months ago. I just cant wrap my head around putting this long of a formula in a cell.

Please Login or Register  to view this content.

If you could work that out to make a formula work with all that, it would still be better and much faster. Using built in formulas are always better than custom functions. I'm not going to try to figure all that out for a formula in a cell, but even if you use a function like this, which you absolutely can, then you should at least use "Select Case" instead of "If then" for something this long.. ie

Please Login or Register  to view this content.

& Thank you for your patience. I know I can be stubborn.

When you define the cells - the name of the cells doesn't need to match up with the function, but the order they are input into the function matters?

& are you saying this method is poor? Because I like having each variable defined as they are used in other aspects of the worksheet. Many longer functions calculate instantaneously, so speed was never the problem.

Naming the cells will make it much easier to make your functions work and is a good idea. It is a great way to make things simpler. You should probably just go through and name every cell you will need in any function (sounds like you might need to make a list of every cell you name because it will have to be spelled exactly right any place you use it). Then you can use those cell names in formulas on the sheet, or in a function you write yourself (UDF). UDF's used to be a thing to avoid if possible mainly because they are slower than Excel's built in functions. With todays computers and processors, that's not as much of an issue anymore. You are fine doing it any way you want to whether UDF's or built in functions, but the built in functions are better and faster still. There are hundreds of built in functions. If you need to have values entered in some kind of report during the process of entering your data or whatever, and you can't use a formula in that report to reference a cells value or something for whatever reason, then yes, by all means, use a macro or UDF if you need to. I think you are trying too hard to do this the best way and can't decide which way to do it. It all boils down to using the built in functions and formulas in cells as being the best way, or UDF's with formulas in the cells as the second best, or UDF's and macros as 3rd best, but they are all fine. They all work just fine. If you want to use all UFD's, go right ahead. The only issue would be if you have too many long complicated UDF's calling each other and doing calculations which could slow down the results slightly, but that's rarely an issue. Your image you posted looks great! And yes, just put the formulas at that bottom like you pointed out using UDF's or built in functions or combination of both to display the results of the inputs above. You can lock those cells and protect the sheet so the formula cells can not be changed.

Thank you very much for your time; you're already deserving of reputation.

I will use the combination of the UDF & built-in functions like you said. I do have one last question, can one UDF have multiple outputs? In the screenshot(attached), the Shear Stress per Bolt and Ratio share the same variables, but I'm not sure how to output two results to different cells.


I want the ratio to be = Shear stress per bolt (value) / allowable shear strength per bolt (value)

or do I need to write a new function & call on the previous function to get the previous values?'


And thank you for the kind comment:D

And another thing you can do is write UDFs that don't use all the variables. You would put them in as optional.. ie..

Function Test(A as integer, Optional B as integer, C as Integer)
Test = A + B + C

End Function
Then in a cell put this formula to add only the first and last variable:
=Test(A1,,A2) - where A1 and A2 have the values to compute.
OR this to compute 3 variables
=Test(A1,A2,A3) - where A1 and A2 and A3 have the values to compute.
Both of them would use the same UDF.
I think that's how it works. I very rarely do that and I'm relying on memory :-)

I get what you're saying, but I don't want to have all of the outputs on the sheet. I'm trying to maintain the "clean" look of the sheet.

How about this method:

Write a sub routine that defines the variables i.e.

Please Login or Register  to view this content.

and whenever I write a function, i would call that subroutine i.e.

Please Login or Register  to view this content.

Actually the VLookup table is a good idea. You can put it in a different sheet and hide it. No one would ever know it's there.
On the other hand, your sub and function idea above would be awesome, but it won't work as is. All the variables you want to use in your function have to be included between the parenthesis.

I believe if I declare the variables as public it should work across all modules - functions can use variables as long as they are defined somewhere.

I guess your Define variable and function combo will work in another UFD or macro, but it won't work as a formula in a cell. You could use it to write other functions or run macros though. You would have to declare every variable at the top of the module as Private or Public for it to work though, but it would work.

Well, I was wrong again... it will work as a formula in a cell also. You just can't use any variables in the formula. I guess I've never used a function without defining the variables within it. So your idea will work.

That's good news I didn't even consider putting the function in the cell itself. Look at us learning from each other!

A cell with "=function()" only updates when that cell is refreshed (not the values)... I could write a macro that refreshes the cells, but I'm sure there is a simpler approach.

Instead of writing a function to define your variables, use constants instead. That is the correct way to do it. For example, this would go at the top of your module:

Where a-d are the variables and the number are their values
Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4

I don't know if that will fix your problem but it might. That's the way it should be done anyway unless you have different subs that define the same variables with different values.
You could also put this in the worksheets SelectionChange event:

ActiveSheet.Calculate

The formula doesn't contain constants (other than pi)... I'll try the selectionchange event function

Please Login or Register  to view this content.

I figured it out - I needed to add " Application. Volatile " at the top of each function.