In the video below I go through the steps to Create Your First VBA Macro. I take you through: adding the Developer tab to the Excel ribbon, the Visual Basic Editor Layout, the best way to set up your desktop and finally building that first macro.
For this demonstration we are building a simple complaints dash board for a plumbing service company. It contains the number of house calls and complaints by type. The owner wants you to highlight any complaint category that exceeds 2%.
The workbook to Create Your First VBA Macro containing the data and completed macro are available for free download below (The green button).
Sub Myfirstmacro() ' Date: 1/7/2019 ' Coder: Ray Mills ' start at the top of the list Range("c9").Select Do Until ActiveCell.Row = 15 If ActiveCell.Value >= 0.02 Then ActiveCell.Interior.Color = 255 End If ActiveCell.Offset(1, 0).Select Loop Range("a1").Select End Sub
1. Whenever you use a do loop be sure to be careful to avoid an infinite loop (one that will not stop until Excel hits row 1,048, 576) – that might take some time! In the case of my macro, a safer option would be: Do until activecell.row >= 15, Why? Because in our code above if it some how missed row 15 it would run until it ran out of rows.
2. Run your code in the VBE editor so if you want to stop the code executing, you can hit the escape key.
3. Get into the habit of annotating your work!
If you have a suggestion, comment or would like to talk to me about getting help with an automation project, you can reach me here.