Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litLIB: Rotation3D demo (Read 2379 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
litLIB: Rotation3D demo
04.04.10 at 12:34:07
litLIB: Rotation3D demo
The attached demo is to show how the Rotation3D function in litLIB works. From given coordinates of a 3D object the new coordinates are calculated after having rotated the object. To illustrate the process the spreadsheet visualises the 3D object.
To start we need a 3D object to rotate. I have chosen a simpel object sybmolised by circles. One circle in the origin and then along each coordinate axis one circle in the positive direction and one in the negative direction:
x      y      z
0      0      0
1      0      0
0      1      0
0      0      1
-1      0      0
0      -1      0
0      0      -1
You can of course replace these with your own coordinates if required.
To rotate this object each set of coordinates (each vector) needs to be multiplied by a rotation matrix. litLIB provides the matrix for you! You only need to decide how to rotate. The rotation matrix is created for a rotation across the x-axis (in the yz plane) and then for a rotation across the y-axis (in the xz plane) and finally for a rotation across the z-axis (in the xy plane).
To create the rotation matrix for a rotation of 10 degrees along all axis you need to select a block of 3 by 3 cells and then type the formula =Rotation3D(10,10,10,1). Press CTRL-SHIFT-ENTER to create the formula as an array formula. The last parameter, 1, is used to specify that we have used degrees and not radians for the angle.
The output is:
0.96984631      -0.171010071      -0.173648177
0.141314484      0.975082444      -0.171010071
0.198565734      0.141314484      0.96984631
This is the rotation matrix with which we need to multiply our coordinates of the 3D object. These are also array functions, so after typing them press CTRL-SHIFT-ENTER. For ease of use I have given the cells of the rotation matrix the name RotationMatrix. The formula to convert the first coordinates (assuming they are in A15:C15) is:
You need to copy this function down to create the coordinates for the other points. The result is:
x                  y                  z
0                  0                  0
0.96984631      -0.171010071      -0.173648177
0.141314484      0.975082444      -0.171010071
0.198565734      0.141314484      0.96984631
-0.96984631      0.171010071      0.173648177
-0.141314484      -0.975082444      0.171010071
-0.198565734      -0.141314484      -0.96984631
To finish the demo I thought it would be nice if you could see the rotation and that you can enter the angles through spinner controls.
The spinner controls are easy to create. Unhide the forms toolbar and paint the spinner buttons to the worksheet. Right click to give them properties. I have given them a cell link to link a cell with the spinner value. The rotation matrix function is changed to pick up the rotation angle from the linked cell. (Don't forget to press CTRL-SHIFT-ENTER again.)
The last thing that needs to be done is to visualise the results. I have selected a block of cells where I want to visualise the result and named the range drawing_area. Then I named the cells containing the coordinates of the rotated object Coordinates. This is for ease of use in the VBA macro to visualise the results. In the drawing area I drew 7 circles that will be positioned at the calculated coordinates.  
To visualise the rotated object the following macro was used. The spinner controls were all assigned to call this macro when clicked.
Option Explicit
Option Base 1

Sub Rotation()
    Dim rngDrawingArea As Range
    Dim rngCoordinates As Range
    Dim max(2) As Double
    Dim d(2) As Double
    Dim width As Double
    Dim height As Double
    Dim O(2) As Double
    Dim lng As Long
    Set rngDrawingArea = Range("Drawing_Area")
    Set rngCoordinates = Range("Coordinates")
    max(1) = 2 'Maximum length of the coordinate axis
    max(2) = 2

    width = rngDrawingArea.width
    height = rngDrawingArea.height

    O(1) = (width / 2) + rngDrawingArea.Left
    O(2) = (height / 2) + rngDrawingArea.Top

    'Step size in both directions
    d(1) = width / (2 * max(1))
    d(2) = height / (2 * max(2))
    For lng = 1 To rngCoordinates.Rows.Count
	    'Shapes 1 to 3 are the spinner controls; hence + 3
	  Shapes(lng + 3).Left = rngCoordinates(lng, 1) * d(1) + O(1)
	  Shapes(lng + 3).Top = rngCoordinates(lng, 2) * d(2) + O(2)
    Next lng
End Sub

The example worksheet is attached to this topic so you are free to try this yourself.
litLIB help: Rotate3D function
Back to top
« Last Edit: 04.04.10 at 17:54:55 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1