Hello George
I could really help you out with your challenges, but the one thing I always want to ask is (when someone wants to learn VBA in depth, not writing/recording macro):
how good are you in Excel without VBA?
You can perfectly write a loop in Excel without much knowledge. For instance a loop through some range in column A, and delete rows that have an empty cell in column A.
Code: Select all
Dim l As Long
For l = 20 to 2 Step -1
If Range("A" & l).Value = "" Then
Rows(l).Delete
End If
Next
(You loop backwards through the range, because deleting a row will shift up other rows and hence the counter is missing rows)
But if you know that loops are costly in Excel (for larger ranges of course, not for 20 rows), and you happen to know the "F5 > Special > Blanks" functionality for Special cells, then you'd code this as:
Code: Select all
On Error Resume Next
Range("A2:A20").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Or abbreviated:
'[A2:A20].SpecialCells(4).EntireRow.Delete
Much shorter code, faster to execute.
If you don't know an Autofilter (which I doubt, but hey), you will not code it in VBA. Get the picture? Even more, if you don't know that you can filter on 2 criteria in an Autofilter, you will not code it.
Use F1 and (even more) F2 extensively. Use F8 stop step through a macro and F9 to insert a breakpoint on a certain line. For debugging purposes:
- MsgBox to output to screen
- Debug.print to send it to the Immediate window (Ctrl-G to show)
- the Locals window in VBE
Avoid the use of Select and Activate, these are bad side products of the VBA macro recorder.
For instance, to clear range A3:B10 the macro recorder will note:
Code: Select all
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A3:B10").Select
Selection.ClearContents
Range("C4").Select
End Sub
Normal VBA coding would write it as:
Code: Select all
Sub Macro1()
Range("A3:B10").ClearContents
End Sub
However, use that macro recorder a lot in the beginning to see the methods and properties associated with certain actions in the spreadsheet.
In the beginning, declare your variables correctly, because than VBE will use intellisense and list methods and properties of objects. Quite handy
After a while you know lots of methods and properties and you will not declare your variables if you know what you're doing.
Indent your code with Stephen Bullen's indenter (
http://www.oaltd.co.uk/Indenter/Default.htm)
Books of John Walkenbach are always worth the money, in particular his Excel 2002 power programming with VBA book.
Websites? Chip Pearson's site is excellent:
http://www.cpearson.com/Excel/MainPage.aspx In general, any site of a MS Excel MVP is worth visiting.
Forums?
http://www.mrexcel.com,
http://www.ozgrid.com/forum, ... The Search function over there is incredible if you search for good terms.
Hope this helps,
Wim
