Over at Daily Dose of Excel Dick has been asking for suggestions for excellent training, to which Jan Karel Pietersen replied that training should be relevant to the task at hand, and that “hello world” examples are useless.

John Walkenbach responded with the mysterious code below:

Sub Test()
Dim x As Long, y As Long
Dim x1 As String, x2 As String
For x = 0 To 5
x1 = x1 & Chr(x * (x * (x * (x * (-0.75 * x + 7.2917) - 22.5) + 16.708) + 28.25) + 72)
Next x
For x = 0 To 6
x2 = x2 & Chr(x * (x * (x * (x * (x * (0.425 * x - 6.8667) + 40.833) - 109.58) + 122.24) - 23.05) + 87)
Next x
MsgBox x1 & x2
End Sub

Paste it into the Visual Basic Editor and run it, and all will be revealed.

I have modified this valuable routine to work as a function that will accept a spreadsheet range of six columns by any number of rows as input, with the results shown below:

Messages from numbers

The code for the function is:
Function TextVal(CharVals As Variant) As String
Dim i As Long, x As Long, NumRows As Long
CharVals = CharVals.Value2
NumRows = UBound(CharVals)
For i = 1 To NumRows
For x = 0 To 5
TextVal = TextVal & Chr(x * (x * (x * (x * (CharVals(i, 6) * x + CharVals(i, 5)) + CharVals(i, 4)) + CharVals(i, 3)) + CharVals(i, 2)) + CharVals(i, 1))
Next x
Next i
End Function

The spreadsheet can be downloaded from Hworld-JW.xls

Those interested in how the numbers are derived, have a look at the second page of the spreadsheet (warning; some mathematics required).  The hint was given by JKP in this comment:

“John:
Yes nice one, isn’t it.
Proves you can fit a 5th order polynomial through six points exactly.”

And just in case fitting a 5th order polynomial to a string of Ascii text is not enough for you, I have now modified the code so it will handle the extended character set, so we can get:

The download spreadsheet now also includes this short UDF:

Function Charw(CVal As Long)
Charw = ChrW(CVal)
End Function

which gives access to the VBA Chrw function from a worksheet.

That could conceivably be of some practical use to someone 🙂