Hello World (again)

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:

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 🙂

This entry was posted in Excel, Maths, UDFs, VBA and tagged , , , , . Bookmark the permalink.

2 Responses to Hello World (again)

  1. superfox8 says:

    “That could conceivably be of some practical use to someone”



  2. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s