WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 520 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

The answer to our February 2020 VBA competition (and its winner)
We are pleased to announce that our February 2020 competition was won by Ken Warthen, whose name was the first chosen randomly out of our electronic sorting hat. This blog lists the 10 VBA mistakes Wally Owl made.

Posted by Andy Brown on 12 March 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.

# Answer to (and winner of) our February 2020 competition

There were 8 entrants for our February competition (Andrew, Christopher, David, Gary, James, Ken, Neil and Paul - you may notice they all have something in common!).  I typed these names into our Excel electronic sorting hat, then:

1. Used the RANDBETWEEN function to generate random numbers between 1 and 100 next to each;
2. Pressed F9 five times with my eyes shut to recalculate;
3. Copied / pasted values to freeze these numbers;
4. Sorted the people into descending order by number.

At the end of this fair and transparent process, I'm pleased to say that we have our first international winner: Ken Warthen of Access Developer Resources.  A £50 (or dollar equivalent) Amazon voucher is on its way digitally to him.

Our correct macro last month should have read:

Dim ItemName, ItemAmount As String

'get the name of the item

ItemName = InputBox("Type in what you need", _

"Enter item", "Type product name here")

ItemAmount = InputBox( _

prompt:="Type amount needed", _

Default:="Type amount here", _

Title:="Amount")

'go to top item cell

Range("A1").Select

'go to bottom of column

ActiveCell.End(xlDown).Select

'go down to next blank cell

ActiveCell.Offset(1, 0).Activate

'type the two inputs into cells

ActiveCell = ItemName

ActiveCell.Offset(0, 1).Value = ItemAmount

'celebrate completion!

MsgBox "You have added the following item to your list:" & _

vbCrLf(2) & _

"Item ==> " & ItemName & _

vbCrLf & _

"Amount ==> " & ItemAmount, vbOKOnly + vbExclamation, "Added item!"

End Sub

Here are Wally Owl's errors:

If you find this too hard to read, you can download the Word document listing the mistakes here.

Because of a bit of ambiguity in the question, I decided to accept people who spotted at least 9 of the 10 answers, although coincidentally Ken's winning answer correctly listed all 10 mistakes.