Excel - Cube Timer

Introduction

The Cube Timer project uses Excel's VBA to record the time it takes to solve a Rubik's cube and related puzzles. We also include timers for some related, but not always cubed shaped, objects such as the pyraminx and megaminx.

The Cube Timer consists of multiple components. The main component is the dashboard, but in the background we have multiple excel tables keeping tracking of everything. This includes regular excel tables as well as pivot tables. We also have the VBA component which keeps track of how some of the buttons work.

Dashboard

The dashboard being the main visual component that the user will interact with is composed of multiple components. To begin with we have the filter at the top left of the dashboard.

The filter controls which cube records we see in the Best Times and Monthly Average charts. For our current selection we see results for 2 by 2 to 5 by 5 cubes.

The other side of the dashboard consists of a collection of buttons which control which type of cube we are creating a record for. The active cube is determined by which button was pressed last.

The selected cube will display two charts. One chart is a histogram while the other is a sequential plot of times including the moving average of the last five records.

Once we have selected a cube as the active cube we can begin recording times. This is done pressing the Inspection button. This changes the Inspection text to Start and begins a 15 second timer for the solver to inspect the cube before starting. The solver must then press Start before the 15 seconds ends to begin the timer. To stop the timer the solver must press Stop (which is the text which shows up after Start is selected).

Image 4: Cube inspection and recent solves

In this image we can see the Inspection button. Directly below the button is a timer which keeps track of how much time has elapsed since the timer began. At the very bottom we display the 10 most recent solves including their date, time, and cube type.

VBA

There are a few different lines of code used in this program to make everything work. In this case I will include the VBA code used specifically for keeping track of time.

Sub StartCountdown()
    If Range("timer.button.label") = "Start" Then
        ' Check if the timer is finished and exit the macro if it is
        If Range("countdown.time").Value = 0 Then
            Range("timer.button.label") = "Inspection"
            Exit Sub
        End If
        ' Remove 1 second from the timer
        Range("countdown.time") = Range("countdown.time") - TimeValue("00:00:01")
        ' Set when the macro should run again - should be the same value
        ' as the previous line
        interval = Now + TimeValue("00:00:01")
        ' Make this macro run again in 1 second
        Application.OnTime interval, "StartCountdown"
    End If
End Sub
Sub DurationTimer()
    If Range("timer.button.label") = "Stop" Then
        ' Add 1 second from the timer
        Range("countdown.time") = Range("countdown.time") + TimeValue("00:00:01")
        ' Set when the macro should run again - should be the same value
        ' as the previous line
        interval = Now + TimeValue("00:00:01")
        ' Make this macro run again in 1 second
        Application.OnTime interval, "DurationTimer"
    Else
    End If
End Sub
Sub SpeedCubeTimer()
    If Range("timer.button.label") = "Inspection" Then
        Range("timer.button.label") = "Start"
        Range("countdown.time").Value = TimeValue("00:00:15")
        Application.Run "StartCountdown"
    ElseIf Range("timer.button.label") = "Start" Then
        Range("time.stamp.start").Offset(Range("count.of.timestamps") + 1).Value = Now
        Range("time.stamp.start").Offset(Range("count.of.timestamps"), 2).Value = Range("cube.type.select")
        Range("timer.button.label") = "Stop"
        Range("countdown.time").Value = TimeValue("00:00:00")
        Application.Run "DurationTimer"
        Range("temp.time") = Timer
    Else
        Range("time.stamp.start").Offset(Range("count.of.timestamps"), 1).Value = Timer - Range("temp.time")
        Range("timer.button.label") = "Inspection"
    End If
End Sub

Conclusion

The goal of this project was to test out some of the functionality of Excel and to create a useful macro using VBA. This goal has been accomplished. Still, this project does have some limitations since it requires data to initialize the tables and charts. So while I can record times for the 6 by 6, 7 by 7 and square-1, I will have to manually create the charts before the buttons can display them.

Overall, this project was a good test and shows what Excel is capable.

Back to projects...