Skip to main content

Posts

Showing posts with the label excel vba

Why I Wrote The Sheet Mechanic (And Why Calculations Aren’t Enough)

For engineers who already know the math—but still lose projects. For the last few years, I’ve been sharing technical guides here on Mechanical Design Handbook —how to size a motor, how to calculate fits, and (as you recently read) how to choose between timing belts and ball screws. But after 25 years in industrial automation, I realized something uncomfortable: Projects rarely fail because the math was wrong. They fail because: The client changed the scope three times in one week. A critical vendor lied about a shipping date (and no one verified it). The installation technician couldn’t fit a wrench into the gap we designed. University taught us the physics. It didn’t teach us the reality. That gap is why I wrote my new book, The Sheet Mechanic . This is not a textbook. It is a field manual for the messy, political, and chaotic space between the CAD model and the factory floor. It captures the systems I’ve used to survive industrial projec...

Stop trying to be a hero. Start being a mechanic.

Get the book →

The Sheet Mechanic on Amazon. Affiliate link, I earn from qualifying purchases.

Excel VBA Kinematic Simulation: Overlapping Motion in High-Speed Machines

The Engineering Challenge: In 2005, I was tasked with upgrading a mechanical transfer turret used to handle highly fragile glass tubes between a conveyor and another process. Production demanded a 25% throughput increase—jumping from 1,200 UPH (Units Per Hour) to 1,500 UPH. Simply speeding up the main drive motor was impossible; the resulting inertial forces and acceleration spikes would have violently shattered the glass tubes before they ever reached the sealing station. The Solution: When you need to increase machine throughput without increasing acceleration forces, the answer is almost always overlapping motion . However, overlapping mechanisms in tight spaces introduces a severe risk of catastrophic mechanical collisions. To validate this 25% speed increase safely, I didn't use expensive 3D motion analysis software. Instead, I used Microsoft Excel and VBA to build a custom 2D kinematic simulator. Here is how that mathematical model was built, and how that exact m...

Design Hoeken’s Linkage in Excel (with Free VBA Simulator)

Figure 1: Geometry of the Hoeken’s straight-line linkage and resulting coupler-point trajectory. The lower portion of the curve approximates straight-line motion over ~180° of crank rotation. The Hoeken’s Linkage is a mechanical engineer's favorite magic trick. It is a four-bar mechanism that converts simple rotational input into a near-perfect straight-line output. Unlike the Watt Linkage (which traces a figure-8), the Hoeken’s Linkage creates a "tear-drop" shape with a long, flat bottom (see Figure 1). This makes it the standard choice for walking robots and intermittent linear actuators. But how do you find the link lengths? If you guess, you get a wobble. This guide provides practical "Golden Ratios" and an Excel VBA tool to simulate the motion path. Advertisement 1. The Geometry: Practical Design Ratios To achieve a usable straight line, link lengths must follow specific proportions relative ...

Pneumatic Cylinder Sizing: Bore, Force & Air Consumption

Figure 1: The "Pull" force is always weaker than the "Push" force because the rod takes up space. If you walk through any factory, you will hear the hiss of wasted compressed air. This is usually the sound of oversized pneumatic cylinders dumping energy. Many engineers size cylinders by "eyeballing it"—picking a 50mm bore because it "looks strong enough." This leads to sluggish cycle times and massive energy bills. This guide covers the physics of Force vs. Pressure , the critical difference between Extend and Retract force , and the often-ignored cost of Air Consumption . Advertisement 1. The Physics: F = P × A Pneumatics is simple: Pressure (PSI or Bar) acts on a Surface Area (Square Inches or mm²) to create Force. Force = Pressure × Area Unit Conversion Cheat Sheet: Pressure: 1 Bar = 0.1 MPa ≈ 14.5 PSI Force: 1 Newton (N) ≈ 0.225 lbs_force Area: 1 mm² ≈ 0.00155 in² ...

The Ultimate Guide to Industrial V-Belt Drives: Selection & Tensioning

          Figure 1: Not all black rubber bands are the same. Choosing the wrong profile is the #1 cause of slip.   If you walk into a plant and hear a high-pitched "chirp" or smell burning rubber, you are witnessing wasted money.     The industrial V-belt drive is the most common power transmission method, yet it is often the most misunderstood. Engineers often specify "A-Section" belts out of habit, ignoring modern, high-efficiency options.   This guide covers Profile Selection , Length Calculation (with VBA) , and the critical belt tensioning method to eliminate belt squeal and premature failure. Advertisement 1. The "Wedge" Effect: How it Works   A flat belt relies purely on friction. A V-Belt relies on the Wedge Effect .   As tension pulls the belt into the sheave groove, the side walls push outward, multiplying the normal force.   Critical Rule: The belt should NEVER touch the b...

Calculate Conveyor Motor Power & Torque: Sizing Guide

Figure 1: The Free Body Diagram (FBD) is the first step in sizing a drive. It visualizes the formula: Te = Friction + Gravity. The most expensive mistake a mechanical designer can make is undersizing the drive motor. If you guess, you risk burning out the winding or stalling the load during startup . If you oversize, you waste thousands of dollars on electricity and larger gearboxes. This guide covers the physics of Effective Tension (Te) , Torque , and Horsepower , and includes a real-world selection example and an Excel VBA script to automate your calculations (in both Imperial and SI units). Method Selection: Quick Calc vs. CEMA Method Best Used For Accuracy Quick Calc (This Guide) Short transfer conveyors (< 15m / 50ft), Unit handling. Good for sizing. Typically over-estimates slightly (Safe). CEMA / ISO 5048 Long overland bulk conveyors, High-speed systems. ...

Gauss Elimination Solver: Video Demo & Series Recap (Part 6)

Figure 1: The completed Gauss Elimination Solver ready for action. Putting It All Together We have reached the conclusion of our 6-part series on building a Linear Equation Solver in Excel. Over the course of this tutorial, we have moved from the raw mathematical theory of Gauss Elimination to writing efficient VBA Code , and finally designing a professional User Interface that handles dynamic matrix scaling. Search for Engineering Simulation Tools Advertisement Video Demonstration Below is a video clip demonstrating the final result. You will see how the program takes user input, automatically resizes the matrix (as discussed in Part 5), and solves for the unknowns instantly. By following the code provided in Part 1 and Part 4 , you can build this exact tool yourself. Video 1: Live demonstration of the dynamic Excel Solver tool. Complete Series Recap If you missed any part of this tutorial, here...

Gauss Elimination: Dynamic N x N Scaling (Part 5)

Figure 1: Visualizing dynamic matrix scaling for large engineering systems. Scaling Up: Handling N x N Systems In Part 4 , we looked at the basic user interface. But engineering problems aren't fixed at 3x3. A truss analysis might need 10 equations; a thermal grid might need 100. A professional Excel tool must be Dynamic . It should automatically resize the input table based on the user's needs, clearing old data and preparing fresh cells for input. Search for Excel Power Programming Books Advertisement 1. User Input for Dimensions The process starts with a simple input request. In our design, clicking "Main Menu" > "New Equations" triggers a VBA InputBox or UserForm asking for the number of variables (N). Figure 2: The user selects "New Equations" from the main menu. For this example, let's input 10 to simulate a larger structural problem. ...

Gauss Elimination: Designing the Excel UI (Part 4)

Figure 1: The final professional user interface for the Excel Solver tool. Designing a Professional Engineering Tool In the previous parts of this series, we covered the theory and the core VBA code. Now, let's focus on the User Experience (UX) . A raw spreadsheet is fine for quick calculations, but if you want to distribute your tool to other engineers, it needs a clean interface. Below is a walkthrough of how a robust Gauss Elimination Solver should behave, using screenshots from our reference application. You can use this as a blueprint to design your own interface in Excel. Search for Excel Dashboard & UI Design Books Advertisement 1. The Startup Screen When a user opens your engineering tool, they shouldn't be bombarded with raw data. A clean "Welcome Screen" sets the tone. Design Tip: Use a simple splash screen image and a "Start" button to guide the user. Ensure your VBA macr...