Pre-requisites: You should be reasonably comfortable with Excel and know the basics of a programming language. Specificially: How to use variables and create object variables, what procedures are (called functions in other languages). You should also be familiar with the OOP terms: objects, properties and methods.
The most important tool at your disposal is the Macro Recorder in Excel, which will generate VBA code which you can then edit and improve upon. In Excel 2003 and earlier these options are available from the Tools menu, Macro menu item. It 2007+ it is best to display the Developer Tab in the Ribbon. In Excel 2007 choose the Office Button, Options and click 'Show Developer Tab..'. In 2010, right-click the Ribbon somewhere and choose Customize the Ribbon - there is a checkbox on the right to display the Developer tab.
Choose the option to Record Macro, give it a name, and then perform a few simple Excel actions: enter some text, do some formatting, create a formula, etc.. It doesn't matter too much as the code that is generated will have similarities to that described below. DON'T FORGET to stop the recording when you've finished! NOTE: In Excel 2007+, if you want to save the macros in a file, then you need to explicitly save the file as Macro Enabled, which has the file extension of .xlsm (or .xslb).
Open the Macros dialog (on the left of the Developer tab or via Tools, Macro, Macros in 2003 - or use Alt-F8) and press Edit to open the Visual Basic Editor.
You will discover code similar to the following:
Sub Macro1()
'random comment lines (denoted by an apostrophe in VB)
ActiveCell.FormulaR1C1 = "Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Andy"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B2").Select
ActiveCell.FormulaR1C1 = "4/27/2013"
Range("B2").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Columns("B:B").EntireColumn.AutoFit
Range("A4").Select
ActiveCell.FormulaR1C1 = "10"
Range("A5").Select
ActiveCell.FormulaR1C1 = "20"
Range("A6").Select
ActiveCell.FormulaR1C1 = "40"
Range("A7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("A8").Select
End Sub
As we will discover shortly, this code is very inefficient and we can greatly improve upon it. Nevertheless, the macro-recorder will remain a useful tool for discovering the names of methods and properties. It is also worth noting that if you record a macro in a slightly different way, perhaps using shortcut-keys or the shortcut-menu, then you may uncover slightly different code. Use this to your advantage.
Recorded code makes extensive use of ActiveCell, Select and Selection. (You will also encounter ActiveSheet, ActiveWorkbook and, for example, ActiveChart.) This is a very inefficient way to work and it is not necessary to activate or select anything in order to work with it.
If you highlight a few cells in Excel then the ActiveCell is the one that is white, Selection refers to the whole highlighted region. If you are working with a single cell then ActiveCell and Selection are the same.
We don't need to activate a cell to put a value into it:
Range("A3").Value = 41 'or
Range("A1").Offset(2, 0).Value = 41 'Offset(rowoffset, coloffset)
Range("A1").Cells(3, 1) = 41 'Value is the default property, which is assumed
Cells(3, 1) = 41 'assumes A1 as the starting point
The recorder uses a cryptic FormulaR1C1 version (I call it "row and column version") of a formula. The above could be replaced with:
Range("A7").Formula = "=SUM(A4:A6)"
When running macros, Excel continually refreshes the screen, causing noticeable flicker and seriously impacting performance. This is largely unnecessary and we can turn this aspect on and off. For most macros I tend to include this line very near the top of the code:
Application.ScreenUpdating = False
You should set this to True at the end of the code, although Excel will update the screen when the procedure finishes anyway. Other features (properties) that you can control are Application.Calculation and Application.DisplayAlerts. Be careful with DisplayAlerts though: if you close a file it won't prompt you to save it if this feature is set to False.
Setting Application.Calculation to xlCalculationManual can improve peformance but (as with .DisplayAlerts) make sure that you set it back to, for example, xlCalculationAutomatic at an appropriate point.
A Template
Here is a basic template that you might adopt, although I prefer not to tinker too much with Application.Calculation if my code involves a lot of work with formulas:
Sub Better()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
dim calc As Integer
Application.ScreenUpdating = False
calc = Application.Calculation 'remember users choice
Application.Calculation = xlCalculationManual
Set wb = Workbooks.Open("C:\Path to\SomeFile.xlsx") 'the extension is not always necessary
Set ws = wb.Worksheets("TheOneIWantToWorkWith")
'working with ranges - see below
'more code here..
wb.Save 'or .SaveAs "C:\Some Folder\The file.xlsx"
wb.Close
Set rng = Nothing 'in reverse order..
Set ws = Nothing
Set wb = Nothing
Application.Calculation = calc
Application.ScreenUpdating = True
End Sub
Working with Ranges
Defined Names (formerly Range Names) are extremely useful in both Excel and VBA. Very often we work with tables of data, having column headings. We can name these columns of data according to their headings. Click into your table and press Ctrl-A to select the table, which is referred to as the CurrentRegion in VBA. On the Formulas tab, choose Create from Selection to create defined names based on the column headings (the top row). In Excel 2003- this is the Insert menu, Name and Create. Note that spaces will be replaced with underscores and non-alphanumeric characters will be removed. (Sometimes an additional underscore will also be added to the name at the end or beginning.) You can check, and test, these names by clicking into a cell and pressing F5 (the GoTo dialog). The statement that creates these defined names programmatically is:
Range("A1").CurrentRegion.CreateNames True, False, False, False
The we can make good use of these defined names in our code:
Range("someHeading").Font.Bold = True
'although it is generally preferable to qualify the Range with a
'reference to the worksheet:
ws.Range("someHeading").Font.Bold = True
Note: It is very important that users don't put odd values in any cell immediately adjacent to the table, otherwise selecting the CurrentRegion will include that (nonsense) column.
We can name individual cells, and other ranges, as well:
Range("A1").Name = "tada"
Range("tada").Value = 54
Range("B1:C20").Name = "myData"
Range("myData").Value = 43 'fill the whole range with a value
So, typical code could be:
Dim ws As Worksheet
Dim rngData As Range
Set ws = Worksheets("Sheet20")
Set rngData = ws.Range("C4").CurrentRegion
rngData.CreateNames True, False, False, False
Range("someHeading").Font.Italic = True
Range("someHeading").Cells(1, 1).Font.Color = vbRed 'the first cell in this area
rngData.Select 'not really necessary
Set rngData = Nothing
Set ws = Nothing
It might be useful to know how many rows there are in a range:
Dim lRows As Long
lRows = Range("someHeading").Rows.Count
If necessary we can use For..Each to efficiently loop through a range:
Dim rng As Range
For Each rng In Range("someHeading")
rng.Font.Color = vbGreen
Next rng
Some other very useful methods, and properties, when working with ranges are:
UsedRange, Intersect, EntireColumn, EntireRow, Row(s), Column(s), Resize
I encourage you to explore these. (One way to do so is to press F2 in the VB Editor to display the Object Browser - it's in the View menu.) However, if some interest is expressed, I might produce a Part 2 for this tutorial, to demonstrate their use.
Good luck, and I hope that you've found this tutorial useful. Andy.
This post has been edited by andrewsw: 03 October 2013 - 10:27 AM
MultiQuote
| 