Author Archives: Gene Dan

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:

Twice the sum of the first 100 integers.

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:

The basic interface.

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:

I do the operations three at a time.

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:

The database is located on a hidden page

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]