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 🙂

### Like this:

Like Loading...

*Related*

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

Ref?

LikeLike

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