Learning VBA

[George Regateiro]

Can anyone provide any resources that you have used to learn VBA? I am just getting my feet wet in it (we just retired TM1Web). I have a programming background so the syntax is not an issue. I am trying to get a good feel for the object model as it relates to Excel. So far I have not run accross any very useful sites in my web searches. Any help would be appreciated. My next step is one of the many books, but I was hoping to save $50 or so.

[Wim Gielis]

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

[lotsaram]

There are many many excellent websites, forums and blogs out there that deal with Excel VBA, and many of the site admin and posters here are very proficient with VBA for that matter.

John Walkenbach's spreadsheet page http://spreadsheetpage.com is a good resource, I haven't ever purchased one of his books but PUP is a great addin and if you pay a little bit extra you get the source VBA - highly recommended.

Also Chip Pearson and Jon Peltier's websites are good resources. (http://www.cpearson.com/excel/topic.aspx , http://peltiertech.com/Excel/index.html)

For blogs try pointy haired dilbert, daily dose of excel and Jorge Camoes at Excel charts.com (http://chandoo.org/wp/ , http://www.dailydoseofexcel.com/ , http://www.excelcharts.com/blog/)

For forums the best are Mr Excel and Ozgrid.

HTH

[lotsaram]

Now that's weird, must be some internet timezone kookiness going on because when I posted my response there were no other posts yet Wim's post apparently was a couple of minutes before. In any case it is good to see that we are in virtual 100% agreement on where the best sources of Excel information are

A work of caution though George as generally I would regard it as bad practice to do anything more than the simplest necessary coding in VBA. Excel models overly heavy with complex VBA are another codebase to maintain and are a much more significant maintenance and key man risk than TM1 as spreadshets tend to be dispersed and subject to version control and security issues whereas at least an OLAP model is centralized. Usually one of the most significant benefits of an Excel integrated OLAP solution (not limiting myself to TM1 here as the same should apply for Infor PM, Palo, PowerOLAP, etc.) is to avoid or eliminate the complexity of Excel data models with consolidations and linkages managed by data tables, custom ranges, ini files, MS Access and the like all tied together by VBA (been there done that, not going back.) Rather than obscure the OLAP tool (and all the power and flexibility that comes with it) through a glossy and presentable layer of custom VBA dialogs and menus (which comes at a high price in terms of development time and loss of flexibility), much better IMO to invest in training your end users and keep the front end clean and simple.

[George Regateiro]

Thanks for all the replies.

A couple points

1) You are right, I started out with the Macro Recorder and quicly found out that it produced a large amount of code for even the simplest tasks. But like you said it was a good starting point to get the jist of what I was trying to accomplish.

2) My intention is not to created a lot of code in VBA. We have some specific needs that can be addressed better through VBA, but I have no intention of coding entire solutions. We are just looking to fill some gaps and provide some niceities that TM1 does not or it is not worth the processing time of going back to the server. I can already see where a large VBA code base would become very hard to manage.