## 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:

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```

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: Function Charw(CVal As Long)
Charw = ChrW(CVal)
End Function

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”

Ref?

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.