6 ms·

An Enigma cipher machine implemented in an Excel spreadsheet by a non-programmer

103 points by willvarfar 15y ago

losethos 15y ago
God says... forgive duty Beginning wide talented tame swollen vagrant us expenses floods Insomuch carcase arranged voluptuous Gutenberg holiness Colorado picture Behold vex to boasting converted English perpetual fence mode deliverest ignorance Holy although exposed subjection beg kingdom why pitch settled examined Praetorian purposing Each prohibition affrighted posted ambition Tully truth approached feareth drove breadth womanish apparent God says... C:\LoseThos\www.losethos.com\text\words\AUGUST.TXT essence, yea lastly how inseparable a distinction there is, and yet a distinction. Surely a man hath it before him; let him look into himself, and see, and tell me. But when he discovers and can say any thing of these, let him not therefore think that he has found that which is above these Unchangeable, which Is unchangeably, and Knows unchangeably, and Wills unchangeably; and whether because of these three, there is in God also a Trinity, or whether all three be in Each, so that the three belong
deleted 15y ago
[deleted]
maukdaddy 15y ago
That is an incredibly impressive feat, especially considering there's no VBA involved.
- nhebb 15y ago
  There's a VBA module. Your security settings may have disabled it.
  - willvarfar 15y ago
    is there much in it? or is it recorded macro kind of stuff?
    - coroxout 15y ago
      From a quick look (so I may be mistaken), the "Enigma" worksheet has everything it needs to encode a single letter using worksheet formulae. The VBA just loops through the input text, feeding it into the worksheet character by character and incrementing the step counts, which are referred to by the worksheet formulae. It also looks up the initial rotor positions from the "Settings" table from the "keys" on the main interface worksheet. That's for encryption - there are another 30 lines of code for decryption which I haven't looked at. It looks hand-coded rather than recorded, but most of the VBA is either incrementing counters or calling worksheet functions, so the VBA isn't doing the heavy lifting. Your friend may not call himself a programmer but I do Excel/VBA work as part of my job, along with more hacker-approved languages, and I'm very impressed. One of us indeed!
redcodenl 15y ago
It looks very good! Don't say he's not a programmer... He is, but doesn't know it yet.
- ChrisAnn 15y ago
  One of us! One of us!
jrockway 15y ago
If you've written a working computer program, you're (now) a programmer.
- Tyrannosaurs 15y ago
  If you've written a working computer program, you're probably in the top 50% of the industry...
jtchang 15y ago
Whoa...that's awesome. Definitely a programmer. I don't care what you think of VBA...it is a great tool. I've seen some truly impressive programs written in it.
pierrebouchet 15y ago
It's funny how Excel allows non-programmers to end up programming without knowing it. Reminds me of http://news.ycombinator.com/item?id=3374431 http://news.ycombinator.com/item?id=3374431
- jurre 15y ago
  I agree, Excel is how I first got into programming and I think I'm not alone in that.
  - RegEx 15y ago
    Microsoft Access assignments at my community college inspired me to work with databases.
tmh88j 15y ago
He may not be a programmer by day, but he certainly has the mentality of one. This definitely is not the kind of macro that can be recorded and works immediately. For someone who is a "non-programmer" it's pretty impressive to figure out how to use nested loops for such an obscure task. How has nobody mentioned how cool this is?! VBA is an awesome tool. It has saved me hundreds of hours of tedious calculations and copy/pasting sessions.
- deleted 15y ago
  [deleted]
MaxGabriel 15y ago
Besides those the author recommends (which seem more general), can anyone recommend a book on WW2 cryptography?
- fduran 15y ago
  "The Code Book: The Science of Secrecy from Ancient Egypt to Quantum Cryptography" http://www.amazon.com/The-Code-Book-Science-Cryptography/dp/0385495323/ http://www.amazon.com/The-Code-Book-Science-Cryptography/dp/... has a chapter about the Enigma. This book is easy to read as a nice intro to many crypto topics. "Alan Turing: The Enigma" http://www.amazon.com/Alan-Turing-Enigma-Andrew-Hodges/dp/0802775802/ http://www.amazon.com/Alan-Turing-Enigma-Andrew-Hodges/dp/08... , supposedly the best biography of Turing, I enjoyed it. There's quite a bit on his work on the Enigma but not technical.
  - Seth_Kriticos 15y ago
    "The Code Book" is great. Very good read and very insightful in the toppic.
- willvarfar 15y ago
  (blogger) if you haven't read The Code Book by Simon Singh (as I recommended), you'll be surprisingly pleased by its depth. Everyone loves it. It was the book that inspired that spreadsheet; it clearly contains enough info to go and try and implement an Engima machine :) It also covers Lorenz (http://en.wikipedia.org/wiki/Lorenz_cipher http://en.wikipedia.org/wiki/Lorenz_cipher) and such. It covers lots of both older and newer systems too, and even has a puzzles section. If you want more of the human side, "Code Breakers: The Inside Story of Bletchley Park" is a dry non-technical memoirs by a those who were actually there. I would like to know more about Dilly's attacks on Spanish Enigma - http://www.schneier.com/blog/archives/2012/03/rare_spanish_en.html http://www.schneier.com/blog/archives/2012/03/rare_spanish_e... - anyone know a book with any detail? Anything on his earlier work too, even?
  - MaxGabriel 15y ago
    Thanks for everyone shabble, fduran and willvarfar's responses; I didn't realize The Code Book's depth and am really excited to see the positive responses here.
motters 15y ago
For another Enigma machine see https://launchpad.net/ubenigma https://launchpad.net/ubenigma
RexRollman 15y ago
What's the threshold for being considered a programmer, really? For example, I don't consider myself one, even though I have created scripts in PowerShell and Bash.

103 points by willvarfar 15y ago

God says... forgive duty Beginning wide talented tame swollen vagrant us expenses floods Insomuch carcase arranged voluptuous Gutenberg holiness Colorado picture Behold vex to boasting converted English perpetual fence mode deliverest ignorance Holy although exposed subjection beg kingdom why pitch settled examined Praetorian purposing Each prohibition affrighted posted ambition Tully truth approached feareth drove breadth womanish apparent God says... C:\LoseThos\www.losethos.com\text\words\AUGUST.TXT essence, yea lastly how inseparable a distinction there is, and yet a distinction. Surely a man hath it before him; let him look into himself, and see, and tell me. But when he discovers and can say any thing of these, let him not therefore think that he has found that which is above these Unchangeable, which Is unchangeably, and Knows unchangeably, and Wills unchangeably; and whether because of these three, there is in God also a Trinity, or whether all three be in Each, so that the three belong

deleted 15y ago

[deleted]

maukdaddy 15y ago

That is an incredibly impressive feat, especially considering there's no VBA involved.

nhebb 15y ago
There's a VBA module. Your security settings may have disabled it.
- willvarfar 15y ago
  is there much in it? or is it recorded macro kind of stuff?
  - coroxout 15y ago
    From a quick look (so I may be mistaken), the "Enigma" worksheet has everything it needs to encode a single letter using worksheet formulae. The VBA just loops through the input text, feeding it into the worksheet character by character and incrementing the step counts, which are referred to by the worksheet formulae. It also looks up the initial rotor positions from the "Settings" table from the "keys" on the main interface worksheet. That's for encryption - there are another 30 lines of code for decryption which I haven't looked at. It looks hand-coded rather than recorded, but most of the VBA is either incrementing counters or calling worksheet functions, so the VBA isn't doing the heavy lifting. Your friend may not call himself a programmer but I do Excel/VBA work as part of my job, along with more hacker-approved languages, and I'm very impressed. One of us indeed!

redcodenl 15y ago

It looks very good! Don't say he's not a programmer... He is, but doesn't know it yet.

ChrisAnn 15y ago
One of us! One of us!

jrockway 15y ago

If you've written a working computer program, you're (now) a programmer.

Tyrannosaurs 15y ago
If you've written a working computer program, you're probably in the top 50% of the industry...

jtchang 15y ago

Whoa...that's awesome. Definitely a programmer. I don't care what you think of VBA...it is a great tool. I've seen some truly impressive programs written in it.

pierrebouchet 15y ago

It's funny how Excel allows non-programmers to end up programming without knowing it. Reminds me of http://news.ycombinator.com/item?id=3374431 http://news.ycombinator.com/item?id=3374431

jurre 15y ago
I agree, Excel is how I first got into programming and I think I'm not alone in that.
- RegEx 15y ago
  Microsoft Access assignments at my community college inspired me to work with databases.

tmh88j 15y ago

He may not be a programmer by day, but he certainly has the mentality of one. This definitely is not the kind of macro that can be recorded and works immediately. For someone who is a "non-programmer" it's pretty impressive to figure out how to use nested loops for such an obscure task. How has nobody mentioned how cool this is?! VBA is an awesome tool. It has saved me hundreds of hours of tedious calculations and copy/pasting sessions.

deleted 15y ago
[deleted]

MaxGabriel 15y ago

Besides those the author recommends (which seem more general), can anyone recommend a book on WW2 cryptography?

fduran 15y ago
"The Code Book: The Science of Secrecy from Ancient Egypt to Quantum Cryptography" http://www.amazon.com/The-Code-Book-Science-Cryptography/dp/0385495323/ http://www.amazon.com/The-Code-Book-Science-Cryptography/dp/... has a chapter about the Enigma. This book is easy to read as a nice intro to many crypto topics. "Alan Turing: The Enigma" http://www.amazon.com/Alan-Turing-Enigma-Andrew-Hodges/dp/0802775802/ http://www.amazon.com/Alan-Turing-Enigma-Andrew-Hodges/dp/08... , supposedly the best biography of Turing, I enjoyed it. There's quite a bit on his work on the Enigma but not technical.
- Seth_Kriticos 15y ago
  "The Code Book" is great. Very good read and very insightful in the toppic.
willvarfar 15y ago
(blogger) if you haven't read The Code Book by Simon Singh (as I recommended), you'll be surprisingly pleased by its depth. Everyone loves it. It was the book that inspired that spreadsheet; it clearly contains enough info to go and try and implement an Engima machine :) It also covers Lorenz (http://en.wikipedia.org/wiki/Lorenz_cipher http://en.wikipedia.org/wiki/Lorenz_cipher) and such. It covers lots of both older and newer systems too, and even has a puzzles section. If you want more of the human side, "Code Breakers: The Inside Story of Bletchley Park" is a dry non-technical memoirs by a those who were actually there. I would like to know more about Dilly's attacks on Spanish Enigma - http://www.schneier.com/blog/archives/2012/03/rare_spanish_en.html http://www.schneier.com/blog/archives/2012/03/rare_spanish_e... - anyone know a book with any detail? Anything on his earlier work too, even?
- MaxGabriel 15y ago
  Thanks for everyone shabble, fduran and willvarfar's responses; I didn't realize The Code Book's depth and am really excited to see the positive responses here.

motters 15y ago

For another Enigma machine see https://launchpad.net/ubenigma https://launchpad.net/ubenigma

RexRollman 15y ago

What's the threshold for being considered a programmer, really? For example, I don't consider myself one, even though I have created scripts in PowerShell and Bash.