BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 23 September 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Results of the August 2020 competition, and prize-winner
If you put the reds, greens and blues together from last month's puzzle pixel by pixel (unsurprisingly, everyone did this using VBA) you got this:
Not, I think, what most people expected to end up with.
Comments accompanying answers included:
- Well, I wasn't expecting Donald Trump to appear in my Excel file!
- My favourite world leader!
- Donald Trump - why!?
- I feel like I've been the equivalent of 'Rick-Rolled' on this one.
- The picture is of Donald Trump and I would therefore argue that your workbook is not harmless, despite your disclaimer!
- While I've been able to decode the puzzle, it scared the living Bejesus out of me! It's the one, the only, the Donald.
- That'll be a picture of Donald Trump . I'll go wash my screen now.
Possible Solutions
Various people submitted code answers. For what it's worth, here was my solution:
Option Explicit
Sub RecreatePicture()
'use the Red sheet as a template - assume all the same structure
'(also note all worksheets have been named in VBA to make coding easier)
'get red sheet range
Dim PixelRange As Range
Dim cRed As Range
Dim cBlue As Range
Dim cGreen As Range
Dim DebugRowCount As Integer
RedSheet.Select
Set PixelRange = Range("A1").CurrentRegion
'clear any old picture and go to top left
PicSheet.Select
Cells.Clear
'create tiny grid
Cells.EntireColumn.ColumnWidth = 0.63
Cells.EntireRow.RowHeight = 6
Range("A1").Select
'loop over cells
DebugRowCount = 1
For Each cRed In PixelRange.Cells
' If cRed.Row <> DebugRowCount Then
' DebugRowCount = cRed.Row
' Stop
' End If
Set cBlue = BlueSheet.Cells(cRed.Row, cRed.Column)
Set cGreen = GreenSheet.Cells(cRed.Row, cRed.Column)
PicSheet.Cells(cRed.Row, cRed.Column).Interior.Color = _
RGB(cRed.Value, cGreen.Value, cBlue.Value)
Next cRed
MsgBox "All done - time to guess who this is!"
End Sub
And here's a much shorter solution from our Wise Owl VBA guru (Mr. Flappy Owl himself) Andrew:
Sub Depixellate()
Dim Reds, Greens, Blues
Dim x As Long, y As Long
Reds = RedSheet.Range("A1").CurrentRegion
Greens = GreenSheet.Range("A1").CurrentRegion
Blues = BlueSheet.Range("A1").CurrentRegion
For x = LBound(Reds, 1) To UBound(Reds, 1)
For y = LBound(Reds, 2) To UBound(Reds, 2)
PicSheet.Cells(x, y).Interior.Color = RGB(Reds(x, y), Greens(x, y), Blues(x, y))
Next y
Next x
End Sub
The Winner!
Out of 19 entries, for the second week in a row it was Matthew Palmer of Public Health England who wins this month's £50 Amazon voucher - congratulations to him.
This proves that we really do award the prize randomly. In an ideal world the Excel random number generator wouldn't pick the same person twice in a row!