December 30, 2005

Circular Shapes

At work I sometimes use Excel for drawing simple diagrams of machinery and parts. Every try drawing something like a cog? It's hard to get the right angles and spacing of the teeth so I tried using some VBA for a solution. Here's what I came up with.

Sub EquidistantRotatation()
    On Error Resume Next
    Dim i As Integer, rObject As Object, rAngle As Double
    Dim cLeft As Double, cTop As Double, cRadius As Double
    If ActiveSheet.DrawingObjects.Count Mod 2 = 1 Then
        rAngle = 360 / ActiveSheet.DrawingObjects.Count
    Else
        rAngle = 180 / ActiveSheet.DrawingObjects.Count
    End If
    ActiveSheet.DrawingObjects.Select
    Selection.ShapeRange.Rotation = 0
    For Each rObject In Selection
        With rObject
            .Left = Selection(1).Left
            .Top = Selection(1).Top
            .ShapeRange.Rotation = rAngle * i
        End With
        i = i + 1
    Next
    cRadius = Selection(1).Height / 2.5 ' adjust to preference
    cLeft = Selection(1).Left + (Selection(1).Width / 2) - cRadius
    cTop = Selection(1).Top + (Selection(1).Height / 2) - cRadius
    ActiveSheet.Shapes.AddShape(msoShapeOval, cLeft, cTop, cRadius * 2, cRadius * 2).Select
        ActiveSheet.DrawingObjects.Select
        Selection.ShapeRange.Group.Select
        Selection.Name = "rShapes"
End Sub

Quick and simple code, here's how to use it.

Copy a shape by pushing Ctrl + C, then keep pushing Ctrl + V until you have the number of shapes you need. Keep in mind that you only need half the number of shapes if working with even numbers like 4, 6, etc as they will overlap. (No need to halve in the case of odd numbers)

For example, Here's a picture of 12 shapes that I will use to make a cog with 24 teeth.



After running the code I get this. (Run the code from "Run Macro" on the Visual Basic toolbar for a nice animation effect!) You can adjust the circle diameter with changing the value for "cRadius". (Bigger numbers = smaller circles)



Do some experimentation to make stuff such as flowers,



Add some color,



Or change the lines,



Note: The code groups the shapes together. To select a single shape from a set of grouped shapes, click it twice but don't double click or the Format Autoshapes dialog box will appear (This may be what you want if formatting all grouped shapes). Wait a second or two between clicks instead. There's no need to Ungroup and Regroup.

New Year holidays start today so see you in 2006, take care ;-)  

Posted by andrewe at 18:52

December 21, 2005

New Cell Spotter

Today I modified the code for Cell Spotter.



As you can see some extra items have been added to the menu. In addition to the lines that appear when you double-click cells, you can also use "Highlight" to make the same type of lines appear around multiple selections - the cells are determined by selecting them with the mouse. Unlike the regular Cell Spotter lines, these lines don't disappear automatically unless you choose to delete them.



Also, the line color(s) and thickness can be altered by changing "Settings".

And finally, you can choose to turn Cell Spotter on or off as convenient. Any changes made (including "Settings") are recorded in the registry.

A little early for Christmas but I hope you like it. The link is here.  
Posted by andrewe at 22:06

December 09, 2005

And another one...

I decided to make yet another Mastermind game that uses colors instead of numbers. The code still works on the same principle, only the names have been changed to protect the innocent.



Click the colors before clicking the rows.

I was thinking about making this a 8 row - 10 row version like the last one, but with a bit of practice I am averaging 6 rows to win, the occasional loss is good for the soul ;-)

Here's the link.

Meanwhile some of my friends are making their own versions. I've seen one that looks particularly good, hopefully it will be ready soon.

Making Excel games is a great way to study and have some fun at the same time.



Update:
Andy Pope just told me his Code Cracker game is uploaded and waiting. All I can say is "Wow, this is truly amazing stuff!".



Here's the link to Andy's site - I'm sure you will agree he's a Mastermind's mastermind :-)  
Posted by andrewe at 20:24

December 05, 2005

When The Cat's Away...

...the mice will play :-)

Here's a pic of my "made for work" version of Mastermind.



Ignore the captions and look at the rows on the left. The first 4 numbers in each row are scored in the rows on the right when the "Calculate" button is pushed, the last 4 numbers being your score.

2 refers to a correct number in a correct position, 1 refers to a correct number in an incorrect position, 0 means that neither a number or position is correct.

So 2110 means a correct number in a correct position, 2 correct numbers but in the wrong position, and a number that just doesn't belong.

Keep in mind that the same numbers can repeat to make it more challenging. You can double-click the game userform itself to select between 8 or 10 tries.



If you score 2222 (all 4 numbers correct and in the right positions) or run out of tries, the drop down list at the bottom becomes enabled so you can check the secret combination. Push Reset to start a new game.

And the range box? Just something to add to the disguise...

Here's the download link, launch it from the Tools menu. Now get back to "work" ;-)  
Posted by andrewe at 21:37