lukecs (User)
Tadpole with legs (Senior Boarder)
Posts: 6

New way of making templates sheets 10 Years ago

Karma: 0

New member. Just found this site and haven't had time to look around but I'm really anxious to share an idea that I've been working on. This idea seems well in line with the purpose of this site and may well get the idea moving.
I'm a structural engineer and am hoping to find an easier way of making templates. I'm experienced in VBA and have made a template generator type workbook the basic idea is such.
Each sheet is its own separate calculation. Named ranges are all local and there are no references that leave the sheet. This sheet can thus be transferred between other workbooks freely without data lose or formula corruption. This is aided by several user defined functions that assist the user in displaying data and receiving data from other worksheets without direct references (similar to indirect). Another twist is that these tools are all made to work with either imperial or metric and can be easily changed on the spot.
Using Styles I have been able to do some advanced control of units and the display of units including displaying the formula of a cell with the value including units of the cells in the formula.
for example
A1 contains formula =P/(a+b)  T with result showing 50kip/ft
B1 contains formula =fm(A1) with the result showing =P/(a+b)T
C1 contains formula =fn(A1) with the result showing =60kip/(3ft+2ft)10kip/ft
So a single workbook consists of several of these sheets that are all organized in a similar fashion. Each sheet is thus a living calculation that can be updated and replaced. Thus you can make a repository of these sheets and make it into a complete calculation template. when a sheet is updated just copy and replace one of your sheets. To further aid this the workbook includes a template sheet that allows you to format your output. There is then a sub that is run by a click of a button that aggregates all your sheets (or portions selected) as text onto this single sheet.
Words can't explain all this. I have a working model but I've only been working on this for about a month and there is plenty to add. Its a very ambitious idea and I need assistance in development so I'm looking for your help, support or partnership on this idea. I'd prefer at this point to keep the working file in a small network. Please email me if you would like to see the working copy I have.



Logged


The administrator has disabled public write access.


Re:New way of making templates sheets 9 Years, 12 Months ago

Karma: 7

Seems like it could be a useful tool. Though writing a static report to word may provide greater flexibility in formatting rather than writing to an Excel worksheet.
One problem with excel is the column width requirements for different tables of calculations which wish to present on same page, or in a multipage report setout on a single worksheet.
Though connecting worksheets together is important, the output of one structural calculation are typically the inputs to another: loads, analysis, members and connection design are all dependent.
Not linking workbooks and not linking worksheets can slow calculation process down. Why design bits of a building when can design the whole building, by inputting a few dependent parameters.
Excel is a great place to sort thorugh and sift out those few dependent parameters and collect together on one or more parameter worksheets, or even a centralised repository of project data.
MS Access/Word/Excel and AutoCAD LT scripts can just about automate everything on a project.



Logged


The administrator has disabled public write access.


Re:New way of making templates sheets 9 Years, 11 Months ago

Karma: 0

I am a structural engineer myself, and am indeed interested in helping you in any way possible.
Almost all my calcs are within Excel spreadsheets, the main exception being frame and finite element analysis, for which I use Integrated Engineering Services' "Visual Analysis".
Each project has an Excel file with worksheet tabs:
Sheet 1 Title
Sheet 2 Design criteria
Sheet 3 Load combinations
Sheet 4 Roof loads
and so forth.
Then I cut and paste sheets as needed, depending on the type of project.
Its a great way to stay organized. If submission of calcs is required, I print PDF's and email them. Many times I fail to make a hard copy for my file, a problem I must work at.
I haven't spruced up all my calcs yet with XLC, it is an ongoing project.
Let me know if I can help; I'm only marginal at spreadsheets, but I can learn fast.



Logged


The administrator has disabled public write access.


Re:New way of making templates sheets 9 Years, 11 Months ago

Karma: 7

An on going project sprucing up calculations using XLC. Isn't that an uproductive waste of time, deteriorating the quality and value of the spreadsheets?
Sure I understand that XLC may make the workbooks more readable, if you consider some of the more estoric methods of mathematical notation more readable, then plain language or program code.
But the entire value of Excel workbook is that when change a parametric value, then the whole workbook is automatically recalculated and the calculations are upto date. As I understand XLC, I don't have current version installed or prior versions, the displayed equation is only valid and upto date when the XLC macros are run.
It is also only of value for simple algebraic formulations. Whilst efficient engineering design benefits from hiding complex series of calculations and makeing use of VBA functions.
Admittedly for structural engineering, the evidenceofsuitability requirements control presentation of calculations. But even then there is still a matter of just how much really needs to be presented given that in many instances the answers are largely known before any calculations are produced. That is within a particular area of practice, tend to know the limitations of the available materials and sections, and largely producing calculations to keep authorities happy, not to find any real designsolution.
Such requirements for evidence can be reduce calculations to a more compact and consise manner. Sketches and mathematical notation not adding anything to the over all presentation: just increasing the amount of paper to be checked through and eventually stored and managed in storage.
Sections are standardised, loads are standardised, and methods of assessing resistance are standardised, and structural forms actually used are relatively common. The majority of structural engineering is thus highly repetitive, though important training ground for gaining experience on those less commmon and more challenging projects.
My point is XLC is useful, but if been getting approval for calculations for years with out such extra technology, why modify now, spreading calculations over more paper as make space for the mathematical notation.
Calculations are a means to end, not and end in themselves. Calculations are meant to assist turn concept into reality, to aid decision making, it is not always necessary to print them out, to record them or even present them to any one.
And the people doing the checking should have equal or greater experience. If you can get the answer in a few seconds using Excel, then so should the certifying authority. So identify the inputs: the controlling parameters. Then give the calculated results, and identify the process of obtaining, which is usually some numbered identifiable formula from a code of practice.
Different audiences have different requirements: few of them need the benefits of calculations formatted with XLC.
Consider the number of tabulated results of calculations: such as section design capacity tables, and standardised connection design capacities. Or even frame analysis output. Do we really need to see all the matrix algebra used to get each and every result calculated by a frame analysis program, or all the detailed calculations of member resistance checking. The answer is No!. We only need the detail when investigating: why, we are not getting the results expected?
That is roughly where the benefits of using one Excel worksheet to produce the calculations and another to present the results comes into play. Traditional type programming, one set of procedures to do the calculations and various other procedures to present the results in different ways.
It is partly where the caculation repository has a problem. Workbooks get complicated and tied to the users actual daily work, and the productivity requirements for such work. The repository is based on simple calculations, say design of a beam, rather than say the common collection of structural elements in a residential building structure of a specific form. Or industrial platforms, crane installations, industrial racking or what ever common application structural system needs to be assessed.
In the age of computers where all involved in an industry can plug simple numbers in computer models of specific productforms, it becomes a large design task in itself determining what does constitute adequate evidenceofsuitability as required by the building codes.
It is not just a matter of producing calculations as usual. The calculations and detail there off, also need a justifiable reason for being submitted. If can get an answer in 1 minute from a published table, then do not submit several pages of calculations which may take a few hours to check. Excel provides the means of generating such tables.
It then becomes a matter of publishing and getting regular review and revision to keep such upto date. Structural engineers need to contribute more real value to their industries. Much of what they supply isn't wanted or needed: since everyone knows the answer already. Simply producing calculations as trace evidence, of followed an appropriate ritual is unproductive. There has to be real need for new calculations. Not a need generated because an "engineer" doesn't understand relationships and can only plug numbers in formula and calculate isolated point values.
The paper chase could be much reduced.
And what if we don't have electricity. Think about who will have the advantage from preengineered designsolutions, in the form of printed tables and graphs.
It is not mathematical notation and presentable calculations we are looking for it is real world results: how to obtain the maximum benefit from the available but otherwise limited resources.



Logged


The administrator has disabled public write access.

lukecs (User)
Tadpole with legs (Senior Boarder)
Posts: 6

Re:New way of making templates sheets 9 Years, 11 Months ago

Karma: 0

Lengthy and well thought out response and I agree with a fair bit of it. However engineers blindly accepting a table especially one produced by an individual rather then an organization or standards body is dangerous. Thus for many it is a requirement to know what is put in the table before using it and thus any none certified table must be checked by every engineer that uses it.
What we get in out company and I venture in most is the creation of hand calcs, excel templates, and mathcad sheets that all do the same calculation. This is because every engineer finds the need to verify and find their solution. By creating a template that is visual and displays step by step the calculation you get something that a user can verify and potentially learn something from quickly and efficiently. This is opposed to a complicated excel chart which is not so easily verified.
Since codes change frequently anything produced needs to be updated regularly. Thus it is essential for any calculation that it can be verified/checked by a wide body of people. If you hide calculations in complicated formulas or VBA code then the spreadsheet is extremely difficult to check and update for anyone other then the creator. This means that anyone using it must trust the creator completely or have a means to check the calculation for any possible combination of inputs.



Logged


The administrator has disabled public write access.


Re:New way of making templates sheets 9 Years, 11 Months ago

Karma: 0

InfoJunkie65,
If I understand you correctly, you've made the point that producing paper calculations for review is counterproductive, that spending time to enhance readability is wasteful, that the important and valuable effort is in the design and its implementation. Of course, you are right. But, I suggest a better forum for this argument might be at a conference of building officials, they are ones that require calculations submittals. My judgment is that fighting city hall in this matter, while possibly entertaining, would be an equally colossal waste of time that might not bear fruit.
It goes without saying that submitted calculations are often incomplete and often include "black box" zones. These should be minimized to facilitate self and pierchecking. Within the litigious society we practice, "due diligence" is what I believe I'm striving toward. Perfection might be beyond my grasp, but always "due diligence" is on my mind. If ever a question arises concerning my design output, either on paper or in the field, I want to be on firm footing. I still have many "canned" calculations that don't always get included in a submittal, but would be forthcoming if needed, so I always include in my calculations submittal this verbiage which I stole from another engineer:
"These calculations are a partial record of the design and decision making process. They are the designer's brief shorthand methodology and they are likely to be incomplete in scope and detail. These calculations may not necessarily be precise, or even accurate in all circumstances, depending on the adjudged level of scope required by the design engineer. These calculations have not been edited for clarity and therefore may be easily misunderstood and misused by others. Some of the information in these calculations may no longer be valid, since it is not uncommon to substitute materials, modify construction, change or add loads and modify the intended use of all or portions of buildings. Much information used during the selection and evaluation of various components of the construction has been furnished by others, in both verbal and written form, and is not included in these calculations. Some of this information may include soils information, equipment sizes and loads, architectural requirements, building code requirements, owner’s requirements, constructibility, engineered products, etcetera. Also, construction phase design and analysis may be omitted from these calculations. The use of these calculations by other than the original Engineer of Record is not recommended for any purpose."
It may be considered smoke and mirrors, but a better workaround isn't completely apparent to me at this time.



Logged


The administrator has disabled public write access.

