Saturday, August 11, 2012

DXFert: An Example VBA Add-In with Custom Ribbon

I implemented this project in Excel 2010. Only the Ribbon modifications are likely to be version dependent (unless you go way, way back). The form and macro in this project should work in any version of Excel.  

To use the macro launchmyForm(control as IRibbonControl)without the Ribbon buttons, such as you would need to do in an earlier version of Excel, you will need to either change

   launchmyForm(control As IRibbonControl) to launchmyForm() 

or 

write another macro that calls launchmyForm(someDummyObject).

In this post, I am going to walk through the building blocks of the project. Except for Excel, everything you need for this project is freely available for download.

What You Will Need to Develop Your Own Similar Project
  1. Excel 2007 or later.
  2. DXF file format documentation from AutoDesk
  3. CustomUI Editor Tool from Open XML Developer
  4. A program to allow you to test your DXF output (if that's what you're going for), such as DWG TrueView.
Overview

You need to 
  1. create a UserForm within a macro-enabled workbook (*.xlsm)
  2. write a one-liner macro that invokes the UserForm
  3. create a Ribbon interface which references the one-liner macro, and, finally,
  4. save the project as an Add-In (*.xlam) and activate it.
Creating the UserForm

If you don't have the developer tab available to you,...why ever not? Turn the silly thing on! To learn about developing user forms in Excel generally, there are lots of examples on the web, but I'll just give you a few key pieces of code that were important in my project.

Here's the code I used to open a file SaveAs dialog to help the user indicate the name and path for the file that the results will be exported:


This should work in very early versions of Excel all the way up to the present (version 2010) and probably future versions as well.  My main beef with it is there is a lack of control.  I would like it to complain about the selection of a file which already exists, but if I really wanted to I could include that complaint in my macro code right before I follow through with the export (which I didn't do—but you can, if you wish).

When you want to terminate the form (either by cancelling or after successful completion), you'll want use something like this:


You can learn a lot about the DXF format by looking at my code in the download.  (Hint:  If you have the Add-In added, you don't need to open up the file separately to see the code.  Just go to the Visual Basic Editor (button on the developer tab) and locate the user form DXFout_Form.  Right-click it and choose View Code.)  See page 143 of the AutoDesk's DXF format documentation for a description of the fields in the polyline Entity.

The one-liner code which the Ribbon will call is pretty easy:


The Ribbon expects to pass this control parameter to the subroutines it calls, whether you need to use the parameter or not.

Create the Ribbon Interface

I overcomplicated this my first few tries at it. Which is what a lot of people on the internet do. If you want your add-in to have its own ribbon elements, you create those elements using CustomUI Editor (referred to above) within a regular *.xlsm file. When you are ready to finalize it as an Add-In you just save it as an add-in and activate it. The same custom UI that showed up in the original *.xlsm file, will then show up all the time. No fuss, no muss. No Workbook_AddinInstall() code, no SetCustomUI(longComplicatedXMLString), no nothing, it just works! You do have to look at a bit of XML code, mind you.

But first, close down your *.xlsm file and open up CustomUI. Open up your *.xlsm file from within CustomUI. Go to the Insert menu and select Office 2010 Custom Part and, likewise, Office 2007 Custom Part. Now it is time for something ugly. Here is the xml code I used in my project:

For 2010 (customUI14):



For 2007 (customUI):



If you look at the <mso:button> tag you'll notice the launchMyForm bit.  That's the name of the procedure that is to be called.  One parameter is passed, namely, the a reference to the ribbon control that called the subroutine.

Save As an Add-In

At any point, you can turn your *.xlsm project into an Add-In.  (You can even continue editing it while it is an Add-In.)  Go to File, Save As, and select Excel Add-In from the file type options in the SaveAs dialog box.
Posted by Darren Irvine at 1:59 AM 0 comments
Labels: , , , ,

Friday, August 3, 2012

AutoDesk Design Review 2013 (0x2: Failed to download component.)

I said yes to updating my Design Review (2011) to the current version (2013) and got the above error message.  The solution in my case was to download the MSI file.  (Note:  click on the language you want under the Choose Your MSI column after instruction 2 at the linked location.)

After unzipping the .zip file you can just run the Setup.exe file.  The principle difference is that the downloading has already taken place and the setup program will look at your local or network location for the relevant files to install.

Now, to use Design Review 2013 without AutoCAD installed, you need to install TrueView.  If you have a version of AutoCAD installed on your computer which is temporarily without a current license (but it is installed on your computer), you'll need to explicitly tell Design Review to use TrueView.

To do so:

Within Design Review 2013, go to the main drop down menu (big "D" in top-left)
→ Options...(at the bottom middle right of drop down)
→ Click on the DWG Viewing tab
→ and check the box beside Open DWG files using DWG TrueView
→ Click OK.
Posted by Darren Irvine at 6:37 PM 1 comments
Labels: , ,

Friday, July 6, 2012

Unfolded Approximation to a Hemisphere

The animated GIF featured below are a sort of slide show which give increasing levels of precision in polyhedral approximations to a hemisphere. The SVG one works properly in Google Chrome, but it may not work in all browsers. In theory, you could print these out, cut along the outline, and fold along the internal lines and you would have a polyhedral approximation to a sphere—if you use a lot of tape and your very best dexterity. More precisely, all of the corners would be coincident with the hemisphere being approximated. Notice that as the number of fold lines (latitudinal-ish lines) increases, for a given number of segments (longitudinal-ish divisions) the extent of the unfolded approximation approaches the grey line, which (probably) represents the limiting extent of these approximations. Specifically, this circle has radius of πr/2 or one fourth the circumference of the approximated sphere.
I wrote the code which generates this sequence of pictures in Maxima, a computer algebra system. The code is below and you can modify the range variable near the end to change the level of precision in the resulting pictures. The pattern for each range item is

[number of segments, [starting latitude divisions, ending latitude divisions]]

The "segments" are longitudinal segments like an orange. The latitude divisions are the number of latitude divisions in each of the segments.

Posted by Darren Irvine at 1:08 PM 0 comments
Labels: , , , ,

Thursday, June 28, 2012

Mathematics: Principles and Formulae

I gather that when most people think about mathematics, they think about two things, and in this order: numbers and formulas. In doing mathematics, most students are quick to add something else, though not by name: recipes – usually grossly oversimplified recipes. It is this tendency that runs them into so much trouble and many are the able teachers of mathematics that have tried to slap them (figuratively) into using something better, namely, principles.

A famous work of Sir Isaac Newton was titled, in Latin, Philosophiæ Naturalis Principia Mathematica ("Mathematical Principles of Natural Philosophy"). But you don't need to be as smart as Sir Isaac Newton to understand the importance of principles as superior to recipes. First of all, what's the difference?

A recipe is a sequence of steps or components with no (particular) reference to reasons. Computers do recipes - algorithms. Students that try to be computers (and nothing more) short-change themselves because there is already a much more efficient tool for that work than themselves. (And thus it is little wonder they complain about the pointlessness of the learning they are engaged in. Often of their own accord, they are not learning the really important lessons from mathematics that they should be.) Principles help me to justify, or at least partly understand, the steps of the recipe and apply it usefully. The ability Principles are one of the key advantages you have over your calculator.

People try to simplify the setting up and solving of proportion problems. They do it this way: "cross-multiply and divide." They don't really know why. It is only a recipe to them. How do you know which numbers to multiply and divide? It increases the number of things to memorize and (I have observed) it distracts people from the real principles. Forget about cross-multiplying and dividing. If I know a/b = c/d, I do what I always do with equations: do "opposites" and do the same thing to both sides (subject to the constraint that the operation must be permissible on the values in the equation). If I want to isolate a, I have a divided by b, so I do the opposite of divide by b, namely, I multiply by b, and I do it to both sides. The end. "Cross-multiple and divide" doesn't bridge well into solving other forms of equations, is unnecessary, gives little clue as to why it is supposed to work, and increases the number of things to memorize. How do you solve an equation?
  1. Do "opposites."
  2. Do the same thing to both sides.
  3. Eliminate any apparent solutions which do not satisfy the original equations or the constraints of the problem (which may be implicit, such as, "negative area is not allowed").
Throw "cross-multiply and divide" and its ilk into the garbage bin.

Here are a few very generic principles to apply to problem solving:
  1. Don't ask "What should I do?", ask "What is true?"  You won't make very much ground figuring out what to do if you haven't established a few things that you know about the situation. If you want something to do, then do this: find out more true things and organize them. This often starts with writing down some given information. Perhaps you print a few formulas and consider which variables you know and which you don't. Ask yourself, "Do I know any other equations that involve these unknowns? Are they valid in this situation?" Don't worry too much about whether an equation is going to give you the final answer, but concern yourself with whether it actually holds true in the situation. Thomas Edison didn't invent so many things by only trying things he knew would work. You also will write down formulas and find that they do not help you solve the problem – even though they are true and valid in the situation, they might not be useful to you. It isn't a mistake (yet), to write it down as a tool in the toolbox.
  2. Don't ask "Why can't I do this?", ask, "Can this method or statement be proven correct?"  People want to apply rules that are applicable to one type of operation to a completely different operation. They see that they are allowed to make the statement a (b + c) = a b + a c and want to know why they can't say log(b + c) = log(b) + log(c).  This is one or both of two fundamental misunderstandings. Either it is assuming that the parentheses have the same meaning in both cases (which means they don't know what a function is and certainly not what a log is) or they misunderstand the importance of proof. This later misunderstanding in particularly important. Often the student assumes that a similar appearance means a similar treatment is permitted. The fact is, the statement a (b + c) = a b + a c, where a, b, and c are numbers can be proven. Our acceptance of the statement is based on proof from simpler principles. We don't "just know" that that's true (although this example is among the more intuitive that I could have chosen). Similarly, we don't "just know" that the statement  log(b + c) = log(b) + log(c) is true. If we can prove somehow that it is true, then we know we're allowed to make this expansion. On the other hand, if we can find a counter-example, we know it is not true. So here's a counter-example to the above (very, very silly) suggestion: log(10 + 10) = log 20 < 2, but log(10) + log(10) = 1 + 1 = 2; so they are clearly not equal. 
    1. This doesn't mean you need to do proofs, but you should be satisfied that a proof exists for what you are doing and that you are not assuming something which cannot be established.
      1. (Did you notice yourself proving your own work while you applied that principle? Tricky, huh?)
  3. Look for patterns. Patterns have some kind of rule behind them. If you can determine the rule that makes the pattern, it may reduce work and memorization required. The rule will be more generic than the sampling that you noticed the pattern in – otherwise, it is a false pattern, because a counter-example exists. Don't assume the pattern is real. Test it, prove it, disprove it, as may be necessary.
  4. Make analogies. The main reason for teaching the principle of proportional triangles is not to teach students how to solve triangle problems – although there's good uses for that. Proportions are so common place that everyone should get comfortable with the archetypical example: proportional triangles. There are analogies between force and momentum, between torque and force, momentum and rotational inertia, fluid pressure and electricity. Some things are more intuitive to us than others and if an analogy exists between something you are familiar with and something you are not, it can help you with a "working understanding" of the unfamiliar concept.
Understanding principles helps you to evaluate proposed solutions or solution methods. Recipes can't do that very effectively. Recipes also don't work well on a "new to you" problem. Knowing principles is a key point of difference between people who know how to implement a given method of solution and someone who can actually develop a method of solution to a problem they have not previously seen. This is true problem solving. Perhaps the most important thing anyone can do to improve their problem solving ability is to prize principles like gold and recipes as mere silver, maybe only bronze.
Posted by Darren Irvine at 12:11 AM 0 comments
Labels: ,

Friday, April 6, 2012

Angle Between Intersecting Planes

In a practical sense, there are a few different ways that you can go about determining the angle between two planes.  But before we get to specific methods and tools, let's try to lay the ground work – let's try to understand what we really mean by an angle between two planes.

Definition:  Angle Between Intersecting Planes

Two non-parallel planes intersect at a line.  The angle between intersecting planes is measured perpendicularly to this line of intersection.  In more detail, we can say, for planes P1 and P2 with line of intersection L:
  1. The vertex of the angle between the planes is any given point on the line of intersection (that is, just pick one).
  2. One arm of the angle is coincident with P1 and the other arm is coincident with P2.
  3. Both arms of the angle are perpendicular to L.
Discussion of Definition

We note in passing that there is no need for the two arms of the angle to have any certain length nor is there a need for the lengths to be constrained by any relationship concerning relative lengths – they may differ.  Items 1 and 2 (above) should come as no surprise, but not everyone will immediately understand item 3.  Indeed, if you consider only the two trivial cases – (i) two coincident planes (technically excluded from discussion here) or (ii) perpendicular planes – item 3 appears superfluous.  To see that it is necessary, consider  planes which meet at a 45° angle (as an example).  Imagine putting a 45-45-90 triangle in place so that two of its edges are in contact with a plane each.  Suppose you made one of those planes rest on the top of the triangle and allowed that plane to rotate freely around the line of intersection (L).  Now, keeping the vertex of the triangle (the vertex on the line of intersection) in place and rotating the triangle around that point, you will find that the plane which is resting on the triangle will lift up – the angle between the planes will increase.  By the time you have rotated the triangle so that the vertical edge is against the "resting" plane, the angle between the planes will be 90°.
Here is a video where I demonstrate this scenario:


Perpendicular Angle Between Planes vs. Non-perpendicular Angle Between Planes
(Criteria 1, 2, and 3) vs. (Criteria 1 and 2)

In the above scenario we used the same triangle at different angles and changed the angle between the planes.  Now let's leave the planes be and change the triangle.  Let's draw the orthographic views of planes (represented as rectangles) intersecting at angle i, according to our criteria and draw two triangles in place:  one at 90° to the line of intersection and one at θ to the line of intersection.


I've been lazy in my drawing as the angle for β that I really want is in the plane of the triangle that's on an angle and not the angle of the orthographic projection of that triangle (which is what I have just drawn).  But bear with me.  The angle of intersection is given as i and we take the dimensions L and θ to be given.  From this information we can find formulae for R, h, and β.  Here are some formulae we can take directly off of the drawing:

\[\sin \theta = \frac{R}{L}\] \[\tan \beta = \frac{h}{L}\] \[\tan i = \frac{h}{R}\]

Manipulating these equations gives

\[R = L \sin \theta \] \[h = L \sin \theta \tan i \] \[\tan \beta = \sin \theta \tan i \]

Observe that β ≠ i.

Calculations Without a Scientific Calculator

You can obtain a rise and run for β without a scientific calculator by using a little measurement and geometry.  Suppose you are able to obtain a rise and run for each of angles i and θ.  You might do this by measuring either a shop drawing or a physical situation.  Choose the run arbitrarily and measure the rise which corresponds to it.  The hypotenuse (for θ) can either be measured or calculated using the Pythagorean theorem.  The formulae for the rise and run of β are simply

rise of β = (rise of θ) × (rise of i)

run of β = (hypotenuse of θ) × (run of i)
Posted by Darren Irvine at 1:07 PM 0 comments
Labels: ,

Saturday, March 31, 2012

Defining a User Coordinate System

In AutoCAD you have User Coordinate Systems (UCS) and the World Coordinate System (WCS). The WCS is the system which your model information is stored in. The UCS is what you draw in. When you first begin a drawing, the current UCS is the same as the WCS.

First, let's familiarize ourselves with how the WCS looks. I've drawn a pyramid in AutoCAD and shown it from several different angles:





The WCS is displayed in all four pictures (and, naturally, is exactly the same in each picture).  One of the techniques for dealing with 3D when you are "doing the math" (perhaps in your own code) and want to make sure the math (or code) you're writing matches the geometry you're working with is known as "the right hand rule."  (We're going to use this for some math coming up in a few paragraphs.) For each picture, look at the WCS and orient your right hand (which should be open face initially) so that your fingers are pointing in the direction of the x-axis and your palm is open toward the direction of the y-axis.  (Don't think about your arm, just your hand. The arm will just go wherever it needs to go, though it may look funny to onlookers.)  Curl your fingers into your palm and stick your thumb out. If you've done it correctly, your thumb should be pointing in the direction of the z-axis.  If you can do this with all four pictures, you're well on your way to being able to understand the rest of the math in this post.

In math notation, we might write the relationship above as something like z = x × y.  The relationships which apply to the WCS (and all UCSs) are

  • x = y × z
  • y = z × x
  • z = x × y

Two of the best ways to change the UCS in AutoCAD are

  • Rotate around a chosen axis:  this is one of the more intuitive ways to adjust the current UCS when you don't have "pickable points" in the plane you want the new xy-plane to be in.
  • Pick three points:  this is a way to put the current UCS in line with a face or in line with somewhere you want to put a face.  Lining up with an existing face of a 3D drawing only requires to you to pick three of the corners of the face.  Depending on the order you pick those points in, you will get different directions for the axes, but the xy-plane of the new UCS will coincide with the three points you pick.
So, how do you define a UCS based on a three points?  Three points define a plane, but that isn't enough to define a UCS.  You need to know where the origin is, which direction the new x-axis is to go, which way the new y-axis is to go, and you also need to pick a direction for the z-axis that is consistent with the way the z-axis relates to the xy-plane in the WCS.  Note that if we just solved the equation of a plane (Ax + By + Cz + D = 0) for the three points we start with, we would not know which way the z-axis goes.  Is it above the plane? below? and why?

To obtain consistent definitions for these axes we use the cross product (which I tried to sneak in earlier:  z = x × y).  Here's the definition of the cross product:

        a × b = (a2 b3 - b2 a3, a3 b1 - b3 a1, a1 b2 - a2 b1),

where a = (a1, a2, a3) and b = (b1, b2, b3). Think about these vectors as direction vectors as opposed to points.  One of the important properties of the cross product is that it produces a vector which is perpendicular to both of the vectors that form the product. That is, a × b is perpendicular to a and to b. To determine which way a × b is pointing, point your fingers (using your right hand) in the direction of a with your palm facing in a direction such that you can curl your fingers toward b.  Your thumb is pointing in the direction of a × b.

Here's how a UCS is defined based on three points. The first point (P0) indicates the position of the new origin. The second point (P1) defines the direction from the first point the x-axis goes in. So, the direction of the new x-axis is x = P1 − P0. The third point (P2) determines not only the plane, but also which side of the x-axis the y-axis will go (within the defined plane). At the same time, we are also defining, albeit indirectly, the direction of the z-axis by means of the right hand rule. We define our UCS accordingly:
  • Origin: P0
  • x-axis: x = P1 − P0
  • z-axis: z = x × (P2 − P0)
  • y-axis: y = z × x
Notice the z-axis is defined before the y-axis as well as the order of factors which produce y. Also observe that the order in which you select the points affects which way your axes are oriented, even though it will not affect what plane is the xy-plane of your new UCS. Given any three points, there are three possible origins - so I need to specify which is the origin. With that selected, I have two options for which will indicate the direction of my x-axis. So, there are six possible UCSs given the same three points in different orders.

Depending on the use of your UCS (if you are programming one in your own software) you may wish to normalize the direction vectors. Normalized vectors (which have length equal to unity) have some useful properties which may save some computation in later calculations. Normalizing is straightforward and simply requires you to divide the value of each component of the vector by the current length of the vector.
Posted by Darren Irvine at 5:04 PM 0 comments
Labels: , ,

Saturday, February 25, 2012

Best Fit Circle: find the center using Excel

Finding the center of a best fit circle depends on minimizing the same function we were concerned with in finding the radius, except that we are going to view it as a function of the center instead of as a function of the radius:

where a and b are the x- and y-coordinates, respectively, of the center and r is given by
What we have here is a function of two variables. It looks like three variables until you realize that r is calculated in terms of the other two. So, we can do a three dimensional plot and see what the scoop is. I used Maxima to do this and obtained a very good view of the surface near the best fit center of the points I have been using in all of my investigations of this problem. Here is the 3D plot of SSE(a,b):

What we are most encouraged to see in this graph is that it looks very smooth and it looks like there is exactly one point that is the lowest point. This lowest point is where the SSE function is minimized and constitutes the best center of the circle. (It might be that there are a few local minima somewhat close together that we could see if we zoomed up really tight to the bottom and we are probably happy with any of these as the "answer". Welcome to numerics.)

These formulae can be used in Excel. Designate two cells for each of the values a and b. You don't know what these are, but start with some guesses for these. You will reference these guesses in your Excel formulae. Put your points in consecutive rows after the pattern (x, y, se, R) where se references the x and y for that line as well as the values for a, b, and r. R will only reference x, y for that line and a, b from above. r above is the average of all the R values in the rows (don't include the 1/n in the R)--you may want to create a cell to contain this average and reference it in your se columns. Use absolute references for a, b, and r (if you have a cell for it) so you can copy and paste the formula easily. Make a sum formula at the bottom of your SSE column and it represents your SSE function as above. You want to use the Excel solver now. The SSE cell is the cell you tell it to minimize and the a and b cells you designate as the cells to be modified. The solver will tweak with the a and b values in an attempt to make SSE as small as possible. (The instructions about r and R might seem circular until you actually implement them. Follow through to the end and you'll see it really isn't circular.) Don't try too hard to follow the instructions--try to do the likely intent (as always).

For a Maxima approach see here.
Posted by Darren Irvine at 2:42 PM 0 comments
Labels: , ,
Subscribe to: Posts (Atom)