VBA Lesson 11: VBA Code General Tips
- Always write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it does not capitalize any letters.... check your spelling.

- If you want to enter the name Peter in a cell you will write:
Range("A1").Value= "Peter"
But if you want the name "Peter" (between quotes) you need to double the quotes write:
Range("A1").Value= ""Peter""
This applies to message boxes and userforms.

You should also add comment to your VBA procedures to make them easier to understand. Any line of code that starts with an apostrophe is considered a comment and the font will be green in the code window. For example:
' In this section we do this and this
You can also add a comment at the end of any line of code by adding a space at the end of the line then a comment preceded by an apostrophe. For example:
Range("A1").Value= ""Peter"" ' Entering a first name between quotes

- All VBA procedures (macros) start with Sub with a set of parentheses at the end
Sub proWhateverName()
We always use the prefix " pro" at the beginning of a procedure and we use upper case letters at the beginning of a new word in the name of the procedure. For example: proWhateverName above or proAddData

- A VBA procedure always ends with End Sub but you can always exit a procedure at any point with:
Exit Sub

- All VBA sentences must be on a single line. When you need to write long sentences of code and you want to force a line break to make it easier to read you must add a space and an underscore at the end of each line. Here is an example of a single sentence broken into 3 lines:

Range("A1:E9").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

- Do not hesitate to use the macro recorder to avoid typos.


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

There are three sections in this website and in the downloadable tutorial on VBA for Excel.

Section 1: Excel Macros Programming: 10 lessons
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will learn about security and discover "events" (an event is what starts the macro).
Section 2: Excel VBA Vocabulary: 13 lessons
Developing a macro is like talking with Excel and to do so you need to use a language called VBA. There are over a thousand VBA words but you will really need only 100 to talk with Excel about workbooks, worksheets, cells, databases, loops and to develop simple and powerful macros.
Section 3: Forms and Controls in VBA for Excel: 10 lessons
The form is a small window that allows the user to submit values that will be used by the macro. To these forms you add controls (command buttons, text boxes, list boxes and others).


--------------------------------------------------------------------------------
VBA Lesson 12: VBA for Excel for the Application
Application is a VBA object, IT IS EXCEL. For example:
Application.Quit
will close Excel all together.

ScreenUpdating
When you do not want to see your screen follow the actions of your VBA procedure (macro), you start and end your code with the following sentences:
Application.ScreenUpdating = False
Then at the end:
Application.ScreenUpdating = True

CutCopyMode
After each Copy/Paste or Copy/PasteSpecial operation, you should empty the clipboard with the following line of code to make sure that the computer memory does not overload.
ActiveSheet.Paste
Application.CutCopyMode=False

VBA Lesson 13: VBA for Excel for the Workbooks
To develop a VBA procedure that is triggered by an event relating to the workbook (when you open it, when you save it, when you close it) see the VBA lesson on events.

ThisWorkbook

ThisWorkbook is the workbook within which your VBA procedure runs. So if you write:
ThisWorkbook.Save
The workbook within which your VBA procedure (macro) runs will be saved.

If you want to close the workbook within which your VBA procedure (macro) runs without saving it you will write these two lines of code:
ThisWorkbook.Saved=True
ThisWorkbook.Close

Workbooks and Windows

When you work with two workbooks you will move from one to the other with:
ThisWorkbook.Activate
Windows("theOtherWorkbookName.xls").Activate


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

Case Study

The client is an analyst with an international company. Each week he receives Excel workbooks (spreadsheets) with data from colleagues around the world. He needs a VBA procedure that will create an Excel database assembling the data from all the workbooks received during the week. The number of workbooks received can vary from 50 to 75 and they are all saved in a special directory on the server.

See how it is done with "Dir" (Excel 1997 to 2007) or "FileSearch" (Excel before 2007 ) in "vba-example-conso-databases.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros)).

VBA Lesson 14: VBA for Excel for the Worksheets
To develop a VBA procedure that is triggered by an event relating to the worksheet (when you select it, when you leave it...) see the VBA lesson on events.

Sheets

You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis

You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False

The name of a sheet must not have more than 31 characters and should not include certain special characters like " ? : \ / [ ]" . If you do not respect these rules your procedure will crash.

The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces
Sheets("Sheet1").Name= " Balance: Introduction" because of the special character :
Sheets("Sheet1" ).Name= " " because the name cannot be blank

You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select
You must take two steps:
Sheets("Results").Select
Range("A1").Select

VBA Lesson 15: Moving around the Worksheet in VBA for Excel
A lot of VBA beginners start their career using Cells. For example:
Cells(1,1).Select is the same thing as Range("A1").Select and
Cells(11,31).Select is the same as Range("AE11").Select.

We strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AE is column 31.

The only time that you will use Cells is when you want to select all the cells of a worksheet. For example:
Cells.Select
To select all cells and then to empty all cells of values or formulas you will use:
Cells.ClearContents

Range
To select a single cell you will write:
Range("A1").Select

To select a set of contiguous cells you will use the colon and write:
Range("A1:G5").Select

To select a set of non contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select

To select a set of non contiguous cells and a range you will use both the colon and the comma:
Range("A1,A5,B4:B8").Select

Offset
The Offset property is the one that you will use the most with Range to move around the sheet.

To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select

To move one cell down from the selected cell:
ActiveCell.Offset(1,0).Select

As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:
Range("A1").Offset(5,6).Select

You will use very often the following piece of code . It selects a cell and 4 more to the right to be copied/pasted somewhere else:
Range(ActiveCell,ActiveCell.Offset(0,4)).Copy
Notice the comma after the first ActiveCell and the double closing parentheses before the Select.


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros


--------------------------------------------------------------------------------

Case Study

The client is an analyst with an nation wide company. Each week he receives Excel workbooks (spreadsheets) with a table from colleagues around the country. He needs a VBA procedure that will consolidate from all the workbooks (spreadsheets) received during the week. The number of workbooks received can vary from 50 to 75 each week and they are all saved in a special directory on the server.

See "vba-example-conso-tables.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros)).

VBA Lesson 16: Message Boxes (MsgBox) in VBA for Excel
In VBA for Excel the message box (MsgBox) is the primary tool to interact with the user. You can use it to inform, alert or ask the user (with a Yes/No message box) if he is sure that he wants a certain critical procedure to run (deleting things).

The code in VBA for Excel to generate the following basic message box is:
MsgBox "Thank you"



If you want you want part of your message to be between quotes like the "A1" below you need a double set of quotes as in:
MsgBox "The result is in cell ""A1"" "



If you to show the value of a cell you will use:
MsgBox "The result is " & Range("A1").Value
do not forget the space after "is" .



Input Boxes

You need to declare (create) a variable to receive the answer to a VbYesNo messsage box or an input box.

For the input box you will write:
varAnswer = InputBox("For how many years?", "Duration")

and for the message box you will write
varAnswer = MsgBox("Do you want to continue?", vbYesNo, "Alert" )

VBA Lesson 17: VBA Excel to work with Databases
Excel offers you very powerful database functionalities like sorting and filtering. When you work in an Excel database you must first make sure that all filters are off. To this end you will start your procedure with these two "If" statements.

Range("A3" ).Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

You should never write the code for filtering or sorting a set of data use the macro recorder and then copy/paste the code.


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

Case Study

The client receives from the database people an Excel file containing the sales for all the stores. He needs a VBA procedure to create a separate workbook for each store. Discover the macro explained step by step in "vba-example-filtering-workbooks.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros))


--------------------------------------------------------------------------------

Case Study

For another report the client above want a macro to split the data received from the database people and create a sheet for each store . receives from the database people an Excel file containing the sales for all the stores. Discover the macro explained step by step in "vba-example-filtering-worksheets.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros))


--------------------------------------------------------------------------------
VBA Lesson 18: VBA for Excel to Manage Errors
After you have written a macro and VBE has helped you correct most of the errors and even after you have tested your macros there might still be error messages generated and you or the user will end up seeing this dialog window:



This window is not a problem if you are the only user of the workbook. But in other cases where you distribute your workbooks to many users you will probably prefer to have a more useful error message like the following:



Here is the basic way to handle errors.

A VBA procedure usually runs from Sub to End Sub. In a VBA procedure that includes an error handler it will run from Sub to Exit Sub but if there is an error happening during the execution it will jump to a special address where the error handler resides.

So to add error handling capabilities to any of your VBA procedures you follow these steps:
1- Add an Exit Sub right before End Sub
2- Between these two lines add an address ending with a colon (addJump: for example)
3- Make room between the address line and End Sub to enter your code handling the error.
4- At the beginning of your code enter On Error GoTo addJump
5- As in the example below the error handler can be a message box giving your coordinates to the user: MsgBox "An error has occurred, call Peter at 1 613-749-4695"

Example 1:

Sub proTestErrorHandler()

On Error GoTo addJump

Workbooks.Open "xxxxxx"
Exit Sub

addJump:

MsgBox "An error has occurred, call Peter at 1 613-749-4695"

End Sub

Copy/Paste the examples in a module of your own and run them. As the workbook xxxxxx can't be found you will see a message box saying An error has occurred, call Peter at 1 613-749-4695 .


--------------------------------------------------------------------------------

VBA Lesson 19: VBA for Excel Variables
You will start developing complex and sophisticated programs in Excel and you will start working with very large sets of data when you discover the variables.

A variable is an object that you create and in which you can store text, dates, numbers or almost anything else. Why should you use variable? The first good reason is to make your code dynamic, to avoid hard coding some values.

Hard Coding vs Dynamic Coding

You are hard coding when you write:
Workbooks.Open "MyFile.xls"

You are dynamically coding when you enter the name of the file in an cell (A1) of your Excel sheet and you write.
varWorkbook=Range("A1").Value
Workbooks.Open varWorkbook
At this point you or the user can change the name of the workbook to open in cell A1 instead of going to the VBA code in the Visual Basic Editor.

You will also create variables to count the number of rows, store the result in a variable and then do something as many time as there are rows.

For varCounter = 1 to varNbRows
Selection.Value=Selection.Value*2
Selection.Offset(1,0).select
Next

In the VBA procedure above the value in each cell is multiplied by 2 then the cell below is selected. This action is repeated as many times as there are rows in the set of data.


--------------------------------------------------------------------------------
VBA Lesson 20: VBA for Excel Statements
Among the VBA statements yu will discover If, Then, ElseIf, End If, Do, Loop, For, Next, While, With, the powerful "Select Case" statement and others.

A lot of visitors ask us how they can delete the entire lines when a certain cell is empty. First enter xxx where you want the loop to stop. Select the cell at the top of the column and run this macro.

Sub proDelete()

Do Until Selection.Value = "xxx"
If Selection.Value = "" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop

Range("A1").Select

End Sub

Exiting a Loop

In the loop above if you want the loop to stop when it finds the value 99 you can add this line of code within the loop:
If Selection.Value = 99 Then Exit Do

Exit allows you to get out of almost anything like:
Exit Sub
Exit For
Exit Do


--------------------------------------------------------------------------------
VBA Lesson 21: VBA for Excel Functions
There are three topics in this lesson:
- using Excel functions within macros,
- using VBA functions within macros,
- creating new Excel functions with VBA.


--------------------------------------------------------------------------------

Excel Functions

Some of the functions that you find in Excel are available through macros in this form:
Range ("C1").Value= Application.WorksheetFunction.Sum(Range("A1:A32"))
this sentence sums the values of cell A1 to A32 and stores the total in cell C1.


--------------------------------------------------------------------------------

VBA Functions

Here are two VBA functions that you will use within your Excel macros:

LCase, UCase
The " If" statements are case sensitive. When you test a string of characters and you do not know if the user will enter upper case or lower case letters, use the LCase or UCase functions within your " If" statement so that however the user enters his answer the statement will work.

If LCase(Selection.Value)= "yes" then...
or
If UCase(Selection.Value)= "YES" then...


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

Case Study

The client needed a new Excel function that would transform numbers into letters. For example when 32.65 is entered in a cell the result that appears in the other cell is: Thirty-two dollars and 65 cents. This function works up to the millions and with any currency that you may choose. In "vba-numbers-letters-var-currencies.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros)) you will discover this function. Use the new Excel function as it is, add it to your workbooks (spreadsheets) or modify it.


--------------------------------------------------------------------------------
VBA Lesson 22: External Data and SQL in VBA for Excel
You have seen in lesson 17 how to use Excel database functionalities with VBA (sort, filter, etc...). In this lesson we discover how to use VBA to extract data from external databases.

With VBA you can use the powerful importing functionalities of Excel. You can even design queries to extract data from any external database submitting criteria directly in Excel. SQL is the language used to extract data from a database through a query. Do you need to learn SQL? The answer is NO.

The database people can develop the SQL sentences for you or you can use Access or any query builder program to do so easily (WISIWIG).

Once you have the SQL sentence you can connect to any database.

Here is a basic piece of code to connect to an Access database and extract data using SQL. The table's name is tbDataSumproduct and the three columns(fields) containing the data are Month, Product and City.

Sub proSQLQuery1()
Dim varConnection
Dim varSQL

Range("A1").CurrentRegion.ClearContents

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\test.mdb; Driver={Driver do Microsoft Access (*.mdb)}"

varSQL = "SELECT tbDataSumproduct.Month, tbDataSumproduct.Product, tbDataSumproduct.City FROM tbDataSumproduct"

With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=ActiveSheet.Range("A1"))
.CommandText = varSQL
.Name = "Query-39008"
.Refresh BackgroundQuery:=False
End With

End Sub


--------------------------------------------------------------------------------

Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros



--------------------------------------------------------------------------------

Case Study

The client has an Access database. He needs a VBA procedure in an Excel workbook (spreadsheet) to extract data from the Access database using dynamic criterias submitted in the Excel workbook. The small Access database is included with the tutorial and the macro is explained step by step in "vba-access-query.xls" (one of the 25 case studies included with the downloadable tutorial on VBA (Macros))

VBA Lesson 23: Working with other Programs in VBA for Excel
API Working with Windows

API stands for Application Programming Interface and consists of a collection of functions that provide programmatic access to the features of the operating system (Windows). When you use API's within VBA for Excel not only do you control Excel but also most parts of Windows.

Working with other Microsoft programs using VBA within Excel

Within Excel you can open another program and even act within it using VBA. For example here is a short macro that opens Word, then a new document to copy/paste the content of 2 cells from Excel to word and save the word document in the same directory as the workbook in which the macro runs:

Sub proWord()
Dim varDoc As Object

Set varDoc = CreateObject("Word.Application")

varDoc.Visible = True
Sheets("Sheet1").Range("A1:b1").Copy
varDoc.documents.Add
varDoc.Selection.Paste
varDoc.activedocument.SaveAs ThisWorkbook.Path & "/" & "testWord.doc"
varDoc.documents.Close

varDoc.Quit

End Sub

Notice that you use VBA for Word within the object varDoc. If you do not know VBA for Word remember that there is also a Macro Recorder in Word. The object varDoc can be visible or you can work within it without bringing it on screen with:
varDoc.Visible = False