One thing I really like is to revisit a program I have written in order to improve it. Improve it so it runs better or so it is easier to maintain.

In Excel if you want 'police' to appear when you type 'pl' all you need is a table that pairs the two up. In some languages, like php, you can have arrays A where A('pl') = 'police', but I don't think Excel allows it.

For the moment I will settle with numeric codes 1 to 100, so A(1) = 'police'.

Here is the code for my new version of 'Fast data entry' from the other day.

---

Option Explicit
Const Max As Integer = 100
Dim A(Max) As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim I As Integer
If Sh.Name <> "tabla" Then
For I = 1 To Max
A(I) = Worksheets("tabla").Cells(I, 1)
Next
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "tabla" Then
If IsNumeric(Target.Value) Then
If Target.Value > 0 And Target.Value <= Max Then
If A(Target.Value) <> "" Then
Cells(Target.Row, Target.Column) = A(Target.Value)
End If
End If
End If
End If
End Sub

---



This is the substitution or code table the user will edit. As soon as the user goes to a new sheet, the values are read into a public array.

Why is this version better than the previous one? It uses no range names the user may inadvertently erase. Apart from that I don't think it is much better than the previous one. Actually it is a bit worse in a sense since the previous version allowed for 'pl' as code for 'police' while this one requires a number.

Anyway, it gave me some practice using Excel.

One thing I learnt was that if you write 'isnumber' it is changed to 'IsNumber' making you believe that it is a part of the language. When you select it and click F1 you are told that Help is not installed. When you select 'IsNumeric' and click F1, Help opens. Definitely a bug. In Excel.