BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 06 February 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.
Spot the mistakes in an Excel VBA macro
Remember Wally Owl, who made so many mistakes in his SQL query back in August? This time Wally's had a go at writing an Excel macro, with equally unfortunate results.
Wally's problem
Wally wants to automate adding an item to his shopping list:
Any added item should appear at the bottom of the list.
This is what Wally would like his macro to show first:
Initially Wally's user (probably himself, let's be honest) should type in what he or she wants to order.
After specifying what they want, the user should enter how much of it they want to buy:
The user can choose how much of this product they want to get.
If all goes well (and I have a hunch that it won't) the user should see their new item and amount:
The new item should be added to the bottom of the list (here I've added 1 kilogram of carrots).
Finally, Wally's users should see a reassuring error message saying what's just happened:
The final message box should look like this (obviously the exact text will depend on what the user ordered).
Wally's solution
Wally's written his macro in his favourite word-processing program, which unfortunately doesn't trap any syntax errors. He's sent it to you for checking.
Option Implicit
Sub Add-Item()
'add another item to your shopping list'
Dim ItemName, ItemAmount As String
'get the name of the item
ItemName = InputBox("Type in what you need", _
"Type product name here","Enter item")
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(xlToDown).Select
'go down to next blank cell
ActiveCell.Offset(0,1).Activate
'type the two inputs into cells
ActiveCell = ItemName
ActiveCell.Offset(0, 1).Value = ItemAmount
'celebrate completion!
MessageBox "You have added the following item to your list:" & _
vbCrLf(2) & _
"Item ==> " & ItemName & _
vbCrLf & _
"Amount ==> " & ItemAmount, vbOKOnly & vbExclamation, "Added item!"
End Sub
Sadly, Wally has made 10 mistakes which will stop his macro working as described. To enter the competition, please send your answer listing these mistakes (in any intelligible form) to this address:
Be warned that Wally has also included the odd red herring (some mistakes - despite being bad practice - won't actually stop the macro working).
The first correct answer drawn out of the randomised electronic Wise Owl sorting hat will win a £50 Amazon voucher!