Question

As a group of engineers with basic Visual Basic skills, you are asked to provide your client with a user-friendly macro-enabled Excel spreadsheet that can be used to calculate the deflection and bending moment of a simply supported beam and a cantilever beam. The spreadsheet will provide a user-friendly interface to allow the client to: i) define the type of beam (i.e. a simply supported beam or a cantilever beam); ii) define cross sectional properties of a given beam structure; iii) define structural parameters and materials. e.g. a main page will allow for a user friendly interface that lists all the individual regular shapes that form a complex shape with options to add more regular shapes to the composition via a button etc. This could lead to another interface that will allow the client to define the parameters of each individual shape (preferably in another sheet). Once the parameters are entered the client will be returned to the main sheet where the details of the newly defined shape will automatically be added to the list of shapes. Once the entire cross-section has been defined, the Excel spreadsheet will calculate the overall second moment of inertia via a button. Challenges (Optional): • An image will automatically be produced when the various shapes are added. • More than one material can be added to the shape in this Excel sheet. STAGE 2 – PART 2 (Maximum Bending Moment and Deflection) Similar to Stage 1 of the group project, this part builds on the information obtained from part 1. Your clients are bridge engineers who want to calculate the deflection and bending moment of their beams. A button on the main page will allow the users to open a new interface to define the load and properties of the beam so that they can calculate the deflection and bending moment of the beam. The user will be given the option to choose any of the following load configurations on the beam. • Concentrated point load • Uniformly distributed load • Uniformly varying load Once all this information is entered, Excel will automatically compute and present the results of the beam’s deflection and bending moment. Hint: Formulae for simply supported beam and cantilever beam can be found online. Challenge (Optional): Plot the shear force diagram and the bending moment diagram. STAGE 2 – PART 3 (Report) Once the cross section and the maximum bending moment and deflection of beam have been defined, the user will be given the choice to press a button to automatically generate an A4 printable report based on the input and calculations produced in this Excel sheet. This report will summarise the parameters that the user has put into Excel and display the results in a way that is simple for the client to interpret.

Answer 1

The following excel sheet has been attached which shows the difference between ss and cantiliver beams as follows: