Hello everybody by abstraks


									Hello everybody

Please find attached the updated spreadsheet. Version5

New features / updates (since version 5)

I discovered an error, due to feedback from a recipient.
The calculation of value b (effective gasket seating width) have been changed.
The 0.5 factor had by mistake (while including the metric unit option) been put inside the
square root sign. Secondly, this factor should read 2.52 when using metric units. This can be
seen in other codes using metric units, e.g EN13480).

New features / updates (since version 4)

I’ve added a number of new features since last version, which you might find interesting:

       Metric or Imperial Units Calculation.
        Now the spreadsheet can do both, however the material databases contain only
        imperial data (from ASME) and the dimensional tables contain only metric data. This
        matters only when you need to revise the databases. When you load the flange data
        from the form you select the units. Remember if you select the units on the
        spreadsheet itself, then you must also change the input values.

       Material databases
        Most steels from ASME B31.3 have been included in the flange and bolt material
        databases. Furthermore from now on you must SAVE each record before scrolling

       Gasket databases.
        Now updated with many more gasket types (from ASME B16.5)

       Database validation.
        A new feature, which allows you to validate material database records individually, so
        you know which ones you have checked and which you have not. The password to
        use for this is eng-tips and is not changeable.

       Comment in input fields
        If you don’t get red marks in the corner of the input fields do the following:
        Select <Tools><Options> and check the “Comment Indicator Only” button. Then you
        wil get additional comments in the input.

       Warning on bolt sizes.
        You get a warning if the diameter restrictions for the bolt material does not match the
        actual bolt size.

       Better looking equations. I have added greek characters etc in the equations. Be
        aware that this may cause a problem for some because my ARIAL font now includes
        these symbols. If you have an older version than WIN2000, your font library may not
        have those signs and the equations will become rubbish.

Before you start up, here’s a little background information and some comments

I started this spreadsheet some years ago when I needed a calculation sheet. I haven't made
any calculations recently, but have used this spreadsheet more as a "work bench" tool which I
could use to learn the Excel VBA language. I do not have a great knowledge of VBA and I
know that sometimes things may not work on other operating systems etc. Should this be the
case I hope that some of you guys may know how to debug such errors.

The Spreadsheets

The spreadsheet consists of 4 (visible) worksheets:

       A three page flange calculation sheet which works as a document in itself. The input
        fields are the white fields. By pressing the "Load Flange data" button the white fields
        will be filled automatically via a form. By pressing the Databases button you will gain
        access to databases containing material and gasket data. However none of these
        have to be used if you fill out the fields yourself. The external loads fields and the
        Modulus of Elasticity will not be filled out automatically.

       A one page flange drawing which works on its own. The input fields are the white

       A one page Material properties sheet which works on its own. The input fields are the
        white fields.

       A notes sheet. Now deleted and replaced by comments in the Cells

The calculation sheet uses to some extent the variables of the Taylor Forge Bulletin. This also
means that some variables (the greek flange factors) carry names not found directly in the
ASME code.

The Macros

There is quite a few macro's built into the system, however only a few are used on the
calculation worksheet. This is the worksheet functions which calculates the flange factors. All
other calculations are performed directly on the worksheet.

On the drawing sheet and the material sheet a couple of worksheet functions are used to find
dimensions and material stresses.

The remaining macro's are all related to the input form and the databases, The heaviest being
the one which processes the data before putting them on to the spreadsheet.

The Databases

6 databases are included:

       Flange dimensional database containing all B16.5 weld neck flanges.

       Pipe dimensional database (Pipe thickness versus schedule)

       Bolting Dimensional database

       Gasket material database

       Flange material database

       Bolt material database
The databases menu includes two "greyed out" buttons. These were added for later use, as I
have not made any forms for these. If you want to see/check the dimensional data for bolting
and flange, there's two ways of doing this:

    1. Go to the "Flange Drawing" tab and enter Size, Rating and facing in the white fields.
       The drawing will update the dimensions accordingly. Not all dimensions are there,
       though, fex. there are no gasket data.

    2. Go into the Tools Menu, and remove sheet protection. Then go into the Format menu
       and select Sheet and Unhide. This will bring up a list of all hidden sheets. Select the
       sheet called "FlangeData". This sheet contains a table of all dimensions for flange
       and gaskets. Only be aware if you manipulate the sheet, that you could mess up the
       database definition, thus producing false results.

Although I have not had any external to check my data, I am relatively confident that the
dimensional data for flanges and gaskets are correct (It is also those which cannot be
changed via the menu)

(I had considerations of how to present these data, as they are constant and should not be
modified, so a normal form was probably not the right way to present them. The option was a
form with a drawing on so you could scroll through the sizes or to improve the
"FlangeDrawing" tab to show all dimensions. The first option I liked best, only I don't know
how or if it is possible to print a data form other than via PrintScreen. (It can be printed in
VBA, but that is without data) The second one I just haven't got around to, so for the time
being, to see all dimensional data, you have to follow the above steps.)

The last four dabases are accessible via the "databases" button and can be altered by the
user, i.e. more materials can be added etc. A button on the form will print the complete

A few materials have been included but stress data for bolting, flanges and gaskets should
not be used without double checking against the code, as they ARE NOT all correct.
I entered those rather quickly and did some copy/paste just to test.

I have updated the Flange and Bolting materials to include most common ASME B31.3 steels
and B16.5

No password protection

All sheets are protected except input cells. Furthermore sheets not required for printout are

There is however no password protection on neither hidden sheets, locked cells or the VBA
code modules, i.e. anything can be acessed if desired. I have deliberately not used password
protection as I hope that in this way, by allowing others to see what is going on, that they will
also provide me with any improvements or information of errors.

Excel and VBA

The spreadsheet is made in Excel version 2000.

I have no separate VBA module installed on my computer, only the one that comes with
Miscellaneous Errors when running the menu

I believe I have removed most errors and replaced some by specific warnings but theres
almost always something you didn’t think about.

       I noticed that when I open up the sheet, don't change anything, but leave all settings
        as they are, and click ok, I get a "Run type error 13". This comes because the gasket
        field is left blank. When selecting a gasket, this did not happen.

       I have also noticed that sometimes I get an error when switching between RF and
        RTJ gaskets, but this I have not been able to track down, or to solve.

       I don't get any errors in the databases.

       If an error comes up, via the menu, It may be an idea to try to change the data in an
        other order. Some of the data you have to change in a certain sequence. For instance
        you cannot select the schedule until you have selected the size because not all
        schedules are available for all sizes.
        Almost all of the code lies in manipulating the menu, and many of these errors don't
        show up until somebody start using the menu's.

       The Metric bolt option don't work, as I have included no metric bolts. The "Bolt root
        area" field on the spreadsheet will show "don't work", and the calculation will fail. You
        will have to enter this data yourself. The bolt dia, and the number of bolts will work as
        they are picked from the flange data table.

       An error on the corrosion allowance may occur depending on your regional settings. If
        you load the flange data from the form, and you set the corrosion allowance to a non-
        integer (e.g. 1.6mm) then in some cases (when your computer is set to using comma
        as decimal digit) then the corrosion allowance that is put onto the worksheet will be
        16mm. You should check this on the input data and correct it directly if necessary.

Unfortunately I may not be able to solve all the errors, but I hope that among all of you who
received the sheet, the might be a VBA wizard who can debug it and share the information
with all of us. Anyway I hope that you will be able to avoid (or solve) the error, and to use the
spreadsheet. After all the spreadsheet itself will work irrespective of any errors in the menu.

A few more comments which I think may be of interest to all

Also something which is interesting to notice is when you "play" with the gasket types:
Try to load a 12" 2500# RTJ flange with Schedule XXS neck and 3mm ca.
Use flange material A105, Bolt material A193 B7 >2.5", pressure=345barg, temp=38F
Select soft Iron ring type gasket.
With this data the flange calculation will show that the flange is ok.
Go back into the menu and change gasket type to Stainless steel ring gasket. Now the bolting
will fail, and you have to pick some other (stronger) bolts. (There's no suitable bolts in the
database right now).

Please do not hesitate to revert with more comments, even though I may not be able to
answer them. After all a part of the reason why I distributed the spreadsheet was in the end to
get a better spreadsheet for everyone. Please also consider to use the eng-tips forum for any
interesting findings that may benefit everyone.
Best regards


To top