Saturday, May 17, 2008

Motion Simulation in Solid Edge using sketches together with Microsoft Excel VBA Programming Part I

Solid Edge has a very useful application called "Motion" which allows a user to simulate the movement of mechanical parts. This enables the machine designers to test and make sure that the movement of their mechanism is the same as they expect before manufacturing parts. This helps save a lot of money.

However, instead of using "Motion" application, I would like to show you how to simulate the motion of mechanical parts using a sketch in Solid Edge together with Microsoft Excel.


Most of the time, I started my mechanical design with the concept and layout using sketches. If we could simulate the motion of mechanical parts in a sketch, it would help me a lot.

I got this idea when I was playing with sketches and constraints in Solid Edge. Solid Edge allows us to change the dimensions easily by just rotating the mouse wheel.

Watch the following video to see how easy to make the motion in sketch.


So what I have to do is just to have Microsoft Excel VBA changes the dimensional values in Solid Edge sketch.

Here is the results of this idea ...


Let's see how to do this in the next post...

Read more info about Solid Edge: Ultimate 3D CAD Software

Friday, May 9, 2008

Microsoft Excel: Goal Seek Technique for solving mechanical engineering design's problems

Problems in mechanical design often require the solution of nonlinear equation such as

3x3 - 20x2 + 1000x + 12000 = 0

These problems are sometimes stated in the form, find the zeros or roots of the function

f(x) = 3x3 - 20x2 + 1000x + 12000 = 0

that is, find the values of x that make f(x) = 0. Therefore, they are sometimes called root solving problems. The equation shown above is a typical equation encountered in the problem of finding the maximum stress at a point. While such equations can be solved by trial and error (simply guessing value until the left-hand side of the equation is nearly zero), there exist simple, systematic ways to solve such problems. Two such methods - the Method of False Position and Newton-Raphson method.

The Method of False Position is a systematic method of narrowing down the region in which the root exists. And another one is the Newton-Raphson method of root solving. It is more sophisticate than the Method False Position. It uses the slope of the function to estimate the location of the root. For most functions, this method converges much more quickly to the solution than does the Method of False Position, and the Newton-Raphson method is the method of choice in most cases (it is my favorite root finding technique, I would like to share you the Excel VBA of the Newton-Raphson method later). However, there are certain types of functions that are not solved very well by the Newton-Raphson method, so the Method of False Position is a good method to use on these functions. The Newton-Raphson method uses the tangent line to the curve at the point xn to estimate the location of the root. The slope of the tangent line at xn is just the derivative of the function evaluated at xn. More info about these root solving techniques will be shared later.

In this post, I would like to share a very simple Excel technique to you in order to help you solve your problem easily on your desktop PC. It is called "Microsoft Excel Goal Seek" technique. Knowledge of Microsoft Excel VBA programming is not required for this technique.

Microsoft Excel: Goal Seek Technique for solving mechanical engineering design's problems

1. Open Microsoft Excel and enter the value and formula as shown below (Click at the picture for larger view)

2. After that, select Tools > Goal Seek...

3. Enter the values as shown below. This is to let Microsoft Excel change x [cell E14] until the value of f(x) [cell E16] is zero (or nearly). Then click OK.

4. You will see the following screen if the solution is found. Please note that the solution of x that gives f(x) = 0 also depends on the initial value of x you entered. Sometimes Microsoft Excel Goal Seek tells you that there is no solution found, but when you change the x value (=value in cell E14 for this case) to any other value instead of your first initial value, Microsoft Excel Goal Seek may find the solution.

Remark: The current value [f(x)] is not zero because of iteration process, but there is no problem if it is within your tolerances.

What if you want to design your shaft that has fatigue loading using the maximum-shear-stress theory combined with the Soderberg line for fatigue, which the diameter and safety factor are related by the following equation?

For example, if you want to the know value of maximum Mm that can apply to your shaft with known diameter (d) and other design factors shown in the equation, what you need to do is to set the several cells for each input factor e.g. d, n, Mm, Ma, Ta, etc. in Microsoft Excel. Next step is to set the formulas for both of your left-hand side and right-hand side of the equation.

So your LHS is something like =PI()*E20^3/32 (suppose that the value of d is in cell E20), then do in the same way for your RHS. Next, enter formula in another cell to calculate the difference between LHS and RHS. I suppose that you enter this formula in cell F11
Then use Microsoft Excel Goal Seek to find value of Mm that make difference between LHS and RHS equal zero.

So values in Goal Seek dialogue box will be...

Set cell: F11
To value: 0
By changing cell: E12 (suppose this the value of Mm)

Imagine that if you want to fix the value of Mm and find value of d instead. What you have to do is just to change the value in "By changing cell:" to the cell that stores the value of d. So it gives more flexibility to you.

Another technique is Microsoft Excel Solver. It is more sophisticate and very useful tools that can help you solve optimization problem. But it requires more customization, we will talk about this later.

Hope this is useful for you :)