Hey everyone,

I mentioned last week that I began working on an arithmetic trainer for one of my side projects. I began programming a little more than a year ago and I wrote one of my first programs using Python, a simple multiplication tutor that tests the user on the multiplication of two-digit numbers. I've always wanted to to get good at arithmetic but somehow after I learned the times table in elementary school I stopped taking it seriously for various reasons, mainly because I was a kid and didn't know any better. Anyway, the importance of arithmetic has waned over the years since the use of calculators has made mental computation a trivial task. However, I think dependence on technology makes kids overlook many of the creative methods our predecessors used to simplify tedious calculations. For example, legend has it that when Gauss was a schoolboy, his teacher told the class to find the sum of the integers from 1 to 100, just to assign them busywork while he was grading papers. However, instead of taking the direct approach and solving for $latex 1+2+cdots+100$ one term at a time, Gauss cleverly solved it using the following mental image:

First, write down the sum on one line, and then write it down again, except with the terms in the opposite order. You can see that the image above gives you 100 groups of 101, so you can multiply 100 by 101 to get 10,100. Since you've written down the sum of 1 to 100 twice, you have to **divide by 2 to get the answer: 5,050**. Isn't that so much faster than adding them one at a time? Indeed, the general formula, $latex frac{n(n+1)}{2}$ gives you the sum of the first $latex n$ consecutive integers.

Unfortunately, I think the 1 through 10 arithmetic of the modern-day education system prevents our elementary students from discovering a lot of interesting patterns, especially when it comes to the squares of numbers, for example $latex 9*16 = 144 = 12^2=3^2*4^2=3^2*2^4$. There exist of course, countless patterns amongst the first 100 natural numbers. I've decided that the mastering of the basic arithmetic operations over the first 100 positive integers should give a young student a solid background and an ample set of numbers from which to identify patterns. However, it's hard to train yourself without a pseudo random number generator giving you some variation, so, to facilitate the education of arithmetic, I decided to write a program in VBA to improve upon what I had done with my first Python program. I decided that the user needed more functionality - the user needs the ability to select the subset of numbers with which he/she decides to practice, as well as the operator to use in the calculations. Furthermore, the user needs to have the program monitor their performance over time. Thus, I developed my program through Microsoft Excel and you can see the basic interface here:

In this example, I chose to have the program train me on all the multiples of 6 from 0-20. The program builds an array of factors, shuffles them into a random order, and prints them on the screen. You can see from the picture that I completed all the operations in about 13.83 seconds - a relatively good performance. I can do it much faster in my head but since I have to physically type the numbers I have to slow down somewhat. I like to read the numbers in groups of three and then simultaneously multiply these groups three at a time in my head before typing them out:

The graph shows my progress over 52 attempts and you can see that I've improved over time. I programmed Excel to generate an embedded chart on the click of the button "Progress Chart." The program fits a power curve to the data. I initially had the program fit a linear curve but I decided since most people should hit a wall at around the 11-12 second range, a power curve would better demonstrate the diminishing marginal returns of efficiency with each iteration.

I had Excel time the user during each attempt, and store the data once the user finished, given that they finished with 100% correct answers. The program prints the data into a database located on a hidden sheet:

From here you can see that I've logged 415 attempts. The first column records the date of the trial, the second records the selected factors, the fourth records the operation, the fifth records the completion time, the sixth records the type of data (either all permutations of the selected factors or a limited subset). The program uses this database to create hidden pivot tables from which to generate charts. I got the program to display the chart embedded on a UserForm by saving the chart as a temporary file, uploading it onto a UserForm, and then deleting the temporary file. Unfortunately there's a current bug where if you have a file called "chart.gif" anywhere in the workbook directory, Excel will override this file! I'll change the coding to give the chart a more unique name, like"chartgd01" or something like that. You normally don't think of Excel as a teaching tool but it can definitely do much more than organize information. This is just one example.

You can try out the program by downloading it here. You need Excel 2007, and you need to enable macros to run this. You can find the instructions on the second sheet. The program is currently in prototype stage and is not finished yet. For example, the quotient operation is not yet functional. If you find any bugs, please tell me! On a side note, I've bridged the page deficit for my European History book and I'm now on track on page 835 out of about 1130. Right now I'm covering the nationalist movements following the Napoleonic wars.

Here's the source code. I apologize for the lack of comments. I'll include them for the next version. I've divided the program into two modules and one UserForm, along with an event handler that tells the program when the user has finished:

**Module 1:**

[code]Option Explicit

Sub mutli()

Dim factor As Integer, upper As Integer, lower As Integer, slower As Integer, supper As Integer, limitsize As Long

Dim x As Integer, y As Integer, z As Integer

Dim farray() As Variant, ansarray() As Variant, sarray() As Variant, printarray() As Variant, finalarray() As Variant, limitedarray As Variant

Dim prodcount As Long

factor = Range("FACTOR1L")

lower = Range("FACTOR2L")

upper = Range("FACTOR2U")

slower = Range("FACTOR1L")

supper = Range("FACTOR1U")

limitsize = Range("SIZE")

Sheets("Trial").Select

Range("A2:D1048576").Clear

Range("STATUS").Value = False

ReDim farray(lower To upper) As Variant

ReDim sarray(slower To supper) As Variant

ReDim limitedarray(1 To Range("SIZE"), 1 To 2)

For x = lower To upper

farray(x) = x

Next x

For y = slower To supper

sarray(y) = y

Next y

prodcount = WorksheetFunction.Count(farray) * WorksheetFunction.Count(sarray)

If Range("LIMITEDSAMPLE") = True And (Range("SIZE") > prodcount) Then

MsgBox "Error: sample size is larger than the possible number of permutations. Select a smaller sample or set ""Limited Sample"" to ""FALSE""."

Debug.Print "hithere"

Exit Sub

End If

ReDim finalarray(1 To prodcount) As Variant

z = 1

For x = slower To supper

For y = lower To upper

finalarray(z) = x & ":" & y

z = z + 1

Next y

Next x

finalarray() = ShuffleArray(finalarray)

ReDim printarray(1 To prodcount, 1 To 2)

For x = 1 To prodcount

printarray(x, 1) = Left(finalarray(x), WorksheetFunction.Search(":", finalarray(x)) - 1)

printarray(x, 2) = Right(finalarray(x), Len(finalarray(x)) - WorksheetFunction.Search(":", finalarray(x)))

Next x

If Range("LIMITEDSAMPLE") = True Then

For x = 1 To Range("SIZE")

limitedarray(x, 1) = printarray(x, 1)

limitedarray(x, 2) = printarray(x, 2)

Next x

End If

If Range("LIMITEDSAMPLE") = True Then

Range("A2:B" & Range("SIZE") + 1).Value = limitedarray

Application.Names.Add Name:="END", RefersTo:=Range("C" & limitsize + 2)

Application.Names.Add Name:="PRODUCTCOUNT", RefersTo:=limitsize

ElseIf Range("LimitedSample") = False Then

Range("A2:B" & prodcount + 1).Value = printarray

Application.Names.Add Name:="END", RefersTo:=Range("C" & prodcount + 2)

Application.Names.Add Name:="PRODUCTCOUNT", RefersTo:=prodcount

End If

Range("C" & WorksheetFunction.Count(Range("A:A")) + 2).Value = "END"

Range("ANSWER").Select

Range("FTIME") = Timer

Range("FTIME").Font.Color = RGB(255, 255, 255)

End Sub

Function ShuffleArray(InArray() As Variant) As Variant()

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' ShuffleArray

' This function returns the values of InArray in random order. The original

' InArray is not modified.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long

Dim L As Long

Dim Temp As Variant

Dim J As Long

Dim Arr() As Variant

Randomize

L = UBound(InArray) - LBound(InArray) + 1

ReDim Arr(LBound(InArray) To UBound(InArray))

For N = LBound(InArray) To UBound(InArray)

Arr(N) = InArray(N)

Next N

For N = LBound(InArray) To UBound(InArray)

J = Int((UBound(InArray) - LBound(InArray) + 1) * Rnd + LBound(InArray))

If N <> J Then

Temp = Arr(N)

Arr(N) = Arr(J)

Arr(J) = Temp

End If

Next N

ShuffleArray = Arr

End Function

Sub check()

Dim x As Integer, y As Integer, z As Integer, currentrow As Long

Dim resultsinfo() As Variant

Dim finishtime As Single

Dim checkcount As Variant

Application.ScreenUpdating = False

finishtime = Timer - Range("FTIME")

checkcount = Right(Names("PRODUCTCOUNT").Value, Len(Names("PRODUCTCOUNT")) - 1)

ReDim resultsinfo(1 To 6) As Variant

y = 0

If Range("Operator").Value = "Product" Then

For x = 1 To checkcount

If Range("A" & x + 1) * Range("B" & x + 1) = Range("C" & x + 1) Then

Range("D" & x + 1).Value = "TRUE"

y = y + 1

Else

Range("D" & x + 1).Value = "FALSE"

End If

Next x

ElseIf Range("Operator").Value = "Sum" Then

For x = 1 To checkcount

If Range("A" & x + 1) + Range("B" & x + 1) = Range("C" & x + 1) Then

Range("D" & x + 1).Value = "TRUE"

y = y + 1

Else

Range("D" & x + 1).Value = "FALSE"

End If

Next x

ElseIf Range("Operator").Value = "Difference" Then

For x = 1 To checkcount

If Range("A" & x + 1) - Range("B" & x + 1) = Range("C" & x + 1) Then

Range("D" & x + 1).Value = "TRUE"

y = y + 1

Else

Range("D" & x + 2).Value = "FALSE"

End If

Next x

End If

Range("GRADE") = y / (checkcount)

resultsinfo(1) = Now

resultsinfo(2) = Range("FACTOR1L").Value & ":" & Range("factor1u").Value & "-" & Range("Factor2L").Value & ":" & Range("Factor2U").Value

resultsinfo(3) = Range("OPERATOR").Value

resultsinfo(4) = finishtime

If Range("LIMITEDSAMPLE") = True Then

resultsinfo(5) = "Limited"

ElseIf Range("LIMITEDSAMPLE") = False Then

resultsinfo(5) = "Full"

End If

resultsinfo(6) = checkcount

currentrow = WorksheetFunction.CountA(Sheets("Database").Range("A:A")) + 1

If (Range("GRADE") = 1 And Range("STATUS") = False) Then

Range("STATUS").Value = True

For z = 1 To 6

Range("FTIME") = finishtime

Sheets("Database").Range(Chr(64 + z) & currentrow).Value = resultsinfo(z)

Next z

ElseIf Range("STATUS") = False Then

Range("FTIME") = finishtime

Range("STATUS").Value = True

End If

Range("FTIME").Font.Color = RGB(0, 0, 0)

createpivot

Application.ScreenUpdating = True

End Sub

Sub createpivot()

Dim resultscache As PivotCache

Dim resultstable As PivotTable

Dim factorpt As PivotTable

Sheets("Pivot").UsedRange.Clear

Sheets("FormPivot").UsedRange.Clear

Set resultscache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Database").UsedRange)

Set resultstable = Sheets("Pivot").PivotTables.Add(PivotCache:=resultscache, TableDestination:=Sheets("Pivot").Range("A1"))

Set resultscache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Database").UsedRange)

Set factorpt = Sheets("FormPivot").PivotTables.Add(PivotCache:=resultscache, TableDestination:=Sheets("FormPivot").Range("A1"))

With resultstable

.PivotFields("CompletionTime").Orientation = xlDataField

.PivotFields("Date").Orientation = xlRowField

.PivotFields("Operation").Orientation = xlPageField

.PivotFields("Factors").Orientation = xlPageField

End With

With factorpt

.PivotFields("Factors").Orientation = xlRowField

End With

End Sub

[/code]

**Module 2:**

[code]

Option Explicit

Sub showchart()

UserForm1.Show

End Sub

[/code]

**UserForm1:**

[code]

Option Explicit

Private Sub ComboBox2_Change()

End Sub

Private Sub CommandButton1_Click()

Dim chartrange As Range

Dim progresschart As Chart

Dim factors As String

Dim factoritem As Variant

Dim inlist As Boolean

Dim chartcount As Long

inlist = False

factors = UserForm1.selectedfactor

For Each factoritem In UserForm1.selectedfactor.List

If factors = factoritem Then

inlist = True

End If

Next factoritem

If inlist = False Then

MsgBox prompt:="Invalid selection", Buttons:=vbExclamation

Exit Sub

End If

With Sheets("Pivot").PivotTables(1)

.PivotFields("Operation").CurrentPage = UserForm1.selectedoperator.Value

.PivotFields("Factors").CurrentPage = UserForm1.selectedfactor.Value

End With

Set chartrange = Sheets("Pivot").Range("A5:B" & WorksheetFunction.Count(Sheets("Pivot").Range("B:B")) + 3)

Set progresschart = Sheets("Pivot").Shapes.AddChart.Chart

With progresschart

.SetSourceData Source:=chartrange

.ChartType = xlLine

.ChartTitle.Text = "Trial v.s. Completion Time - " & factors

.SeriesCollection(1).Trendlines.Add

.SeriesCollection(1).Trendlines(1).Type = xlPower

.ShowAllFieldButtons = False

.SetElement (msoElementLegendNone)

With .Axes(xlValue)

.HasTitle = True

.AxisTitle.Text = "Completion Time (Seconds)"

End With

With .Axes(xlCategory)

.HasTitle = True

.AxisTitle.Text = "Trial"

End With

End With

With Sheets("Pivot").ChartObjects(1)

.Width = 485

.Height = 330

End With

Sheets("Pivot").ChartObjects(1).Chart.Export Filename:=ThisWorkbook.Path & "chart.gif", filtername:="GIF"

UserForm1.Image1.Picture = LoadPicture(ThisWorkbook.Path & "chart.gif")

For chartcount = 1 To Sheets("pivot").ChartObjects.Count

Sheets("Pivot").ChartObjects(chartcount).Delete

Next chartcount

Kill (ThisWorkbook.Path & "chart.gif")

End Sub

Private Sub userform_activate()

Dim x As Long

Dim inlist As Boolean

inlist = False

With Me

.Left = Application.Left + Application.Width / 2 - .Width / 2

With selectedoperator

.AddItem ("Difference")

.AddItem ("Product")

.AddItem ("Sum")

.AddItem ("Quotient")

End With

.selectedoperator.Value = Range("OPERATOR").Value

.selectedfactor.Value = Range("FACTOR1L").Value & ":" & Range("FACTOR1U").Value & "-" & Range("Factor2L").Value & ":" & Range("Factor2U").Value

End With

For x = 2 To WorksheetFunction.CountA(Sheets("FormPivot").Range("A:A")) - 1

selectedfactor.AddItem (Sheets("FormPivot").Range("A" & x))

If selectedfactor.Value = Sheets("FormPivot").Range("A" & x) Then

inlist = True

End If

Next x

createpivot

If inlist = True Then

CommandButton1_Click

End If

End Sub

[/code]

**Event Handler:**

[code]

Private Sub TextBox1_Change()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lower, upper As Integer

If Not Intersect(Target, Range("END")) Is Nothing Then

check

End If

End Sub[/code]