Open a website via VBA

Sometimes you may want to obtain data from an online source and import it into a spreadsheet.  This is easy to do, using the following statement:

Workbooks.Open Filename:="URL"

This statement opens the website in a spreadsheet, meaning that you can then use the same VBA commands as on any spreadsheet.  For example, if you want to obtain a number from the website (for example a stockquote), you can reference the cell containing the number required.  In order to find out which cell you must reference in the code, you must first open the website in Excel (using the command above) and find the correct cell in the spreadsheet.  Once you have the correct cell reference, you can then automatically open the website in a spreadsheet, store the number in a variable, and then close the website/spreadsheet.  Close the website with this statement:

Windows("Website Title").Close savechanges:=False

Note that to determine the Website Title, you must again use the method of opening the website in a spreadsheet; then look at the "file name" (displayed in the Excel header bar at the very top of the window).

For Loops

Like Do While and Do Until loops, For loops are used to repeat a block of code until (or while) a condition is met.  The syntax is:

For variable = Start To End
    Code to be repeated
Next

A For loop can also be used with the Step statement to alter the increment used.  For example, the code below increments the variable x from 0 to 10 in steps of 2, i.e. x = 0, 2, 4, 6, 8, 10.  Without the Step statement, the code would set x to 0, 1, 2, 3, ... 10.

For x = 0 To 10 Step 2
    Code to be repeated
Next

Note that the Step statement can also be used to run through a For loop "backwards":

For x = 10 To 0 Step -2

    Code to be repeated
Next

The Exit For statement can be used to exit a For loop before the end condition is met.  For example:

For x = 0 To 10 Step 2

    If intNum = 25 Then Exit For

Next

User-defined Functions

In Excel you can write 2 different types of macros, although they are often confused and used interchangeably.  The first is a sub routine (denoted by the use of "Sub" before the name of the routine).  The second is a function (denoted, logically, by the use of "Function" before the name of the function).  The difference between the two is quite important.

• A sub routine can change the value and/or format of a cell, but can only be invoked by an event occurring (e.g. the workbook being saved, or the value of a cell changing) or a user command (such as clicking on a button to run the sub routine).

• A function cannot change the value or format of a cell in the workbook, but they can be invoked directly from a cell.

The reason  for this difference is simple: Excel must keep track of which cells are dependent on others and which are independent; if you were able to call a sub routine from a cell and then change other cell values from the macro, Excel would no longer be able to track dependencies.

You might think that sub routines sound more flexible and therefore more widely used (and you would probably be right), however user-defined functions also have their, well, functions (I couldn't help myself)!  A classic example of this is a function that calculates an area based on dimensions (such as radius, diameter, length, height, etc.).  Functions can be much more complicated though.  For example, I recently wrote a function that would determine the type of element being considered (cylinder, rectangle, etc.), use that information to read in a formula from a separate worksheet (where all of the possible formulas were listed - a different one for each element type), and then calculate and output the second moment of area for that element.  To start with though, we'll write a quick function that just calculates the area of a circle or of a rectangle.

User-Defined Function: Area of a Rectangle or Circle

To begin, open a new workbook.  Add the following data to your worksheet, in 4 different columns:

Width	Height	Diameter	Area
10	50
5	12
6	13
		6
		10
		0.75

Now add a new Module to the workbook in the Visual Basic Editor (Alt+F11) under "Insert - Module".  The function must be written in a module, not in one of the Excel Objects (a sheet or the workbook).  Choose a name for your function - I'm going to use "Area".  A function requires you to specify the input when you declare the function.  In this case the input we require are the variables used to calculate the area of a circle or of a rectangle (width, height, and diameter).  Declare the function using the following line of code:

Function Area(dblWidth As Double, dblHeight As Double,
    dblDiameter As Double) As Double

Excel will automatically add End Function when you press enter after typing the line above.  The line above tells Excel that all of the inputs and the output are all double variable types.  Although not necessary, it's always a good idea to specify the variable type.  Now our function needs to work out if the equation for the area of a rectangle or for the area of a circle should be used.  We can do this by looking at the dblDiameter input - if this is empty, then we use the formula for a rectangle, if it's not empty (it has a value) then we'll use the formula for a circle.  To do this, add the code below between the Function declaration line above and the End Function line:

If dblDiameter = "" Then

    Area = dblWidth * dblHeight

Else

    Area = 2 * WorksheetFunction.Pi * (dblDiameter / 2) ^ 2

End If

Your final code should look like this:

Note that the underscores (_) are used for a break in the code - normally each line of code is a new statement, but using an underscore allows you to continue a statement onto a 2nd line of code.

Return to the workbook now; all that is left to do is to call the function for each row.  In the first cell under the "Area" header, type "=Area(B3,C3,D3)".  Note that the cell values B3, C3 and D3 should correspond to the width, height, and diameter entries for that row - these are the three cells that I have used, but since you may have used different cells make sure that you specify the correct ones.  This tells Excel to call the Area function, with the three correct inputs.  If your function is written correctly, the cell will now display the area of the rectangle/circle!  Simply fill down the rows to the last entry in the table to determine the area for all of the entries.

Worksheet Function

One of the most powerful features of programming with VBA in Excel is the ability to use functions that are built into Excel.  For example, say you're writing a macro that calculates the area of a circle from the radius or diameter (the formula is of course Area = 2 * pi * r^2).  Instead of typing out the constant pi to a certain number of decimal places, you can use the Pi() function built into Excel.  Normally if you were computing this formula in an Excel cell, you would just use Pi(); you can call the same function in VBA using the following line of code:

Application.WorksheetFunction.Pi

If you have a macro that uses this function, it would look something like this (using variables dblArea for the area of a circle, and sngRadius for the radius of the circle):

dblArea = 2 * Application.WorksheetFunction.Pi * sngRadius ^ 2

While this may not seem very useful for Pi (we can easily approximate the constant to 3.14159), it becomes invaluable when using other Excel functions - for example you might use Excel's Linear Regression function to output the slope of a best fit line, or VLookup to get the value of a cell in a range.  In all cases, you call up the function using Application.WorksheetFunction.Function - Excel helpfully suggests all possible functions when you type the period between WorksheetFunction and Function, so you can then just choose the function you need from the list of suggestions.

We will use this technique in the next tutorial post to learn how to write a user-defined function - also a type of macro, but different to a sub procedure, which is what we have looked at in the past.

Excel Screen Updating

It can sometimes be useful to stop Excel from updating the screen (or window) while a macro is running. This isn't commonly used when your macro is short and quick to run, but longer/slower macros can employ this as it speeds up the runtime of the macro. This is because Excel no longer has to refresh the screen every time the macro uses a Scroll, Activate, Select, etc. command or whenever formulas need to be re-calculated.

Switching off the screen updating is extremely simple to do. It's best to do it at the beginning of the macro, but you can also switch it on and off repeatedly as many times as you like in a macro (if there is something specific you want to have updated while the macro runs). To switch screen updating off add the following line to your code:

Application.ScreenUpdating = False

And to turn screen updating back on, use this line:

Application.ScreenUpdating = True

As mentioned above, you would usually put these lines at the beginning and end of your macro respectively; something like this:

Sub Macro()
    Application.ScreenUpdating = False

    'The rest of your code

    Application.ScreenUpdating = True
End Sub

And don't worry - when the macro ends and the focus switches back to Excel, screen updating is automatically enabled again, so while it's always a good idea to remember to enable screen updating again at the end of your code, it's not completely necessary!

Do Until Loops

Just like a Do While loop, a Do Until loop repeats a sequence of code until a certain condition is met. The syntax for this type of loop is the following:

Do Until condition
    'Code to be executed
Loop

Again like a Do While loop, Excel can check the condition either before entering the loop (as above) or after Excel has run through the code once, as shown below:

Do
    'Code to be executed
Loop Until condition

You can use the Exit Do statement to force Excel to exit the loop when desired (instead of waiting for the specified loop condition to be met).

We can use the previous tutorial (the spreadsheet with the list of students and grades) as an example. The previous loop we used (Do While Range("C" & i).Value <> "") can be replaced with a Do Until loop instead:

Do Until Range("C" & i).Value = ""

Your code should now look like this:


Note that the not equal sign (<>) must now be changed to an equals (=) sign. Clicking on the "Generate grade" button has the same effect as before, assigning the appropriate letter grade for each student based on their percentage score.

Do While Loops

A loop is used to repeatedly run through lines of code until or while a certain condition is met, usually for as long as a variable is equal to a specific value. Loops in VBA can take a number of different forms; this post will deal with Do While loops. The syntax for a Do While loop is the following:

Do While variable = value
    'Code to loop through
Loop

An example of this could be:

Do While strStudentName = "Dan"
    'Code to be looped through
Loop

Note that the = sign above can also be replaced with another condition, such as greater than (>), less than (<) or not equal to (<>). In the above cases, Excel will check the condition (variable = value) before entering the loop; it is also possible to have Excel check the condition after entering the loop, by typing the condition at the end:

Do
    'Code to be looped through
Loop While variable = value

Another useful tip can be to exit a Do While loop in the middle of the loop. This is done using Exit Do. Used with an If statement, you can have VBA exit the loop when a different variable (not used after the Do or While statement) becomes equal (or not equal) to a value.


Do While Tutorial

To practice using a Do While loop, open up the workbook we created in the last tutorial. Delete the letter grades in the "Grade" column and add a few more names and grades (percent values). Your worksheet should look something like this:

Now edit the "Generate grade" button code (note: a quick shortcut for accessing VBA code in a workbook is to press Alt+F11).

We're going to add a loop to go through the students one at a time, adding the letter grade corresponding to their percentage grade. In order to do so, we need to create a variable that increments by 1 each time we go through the loop and that corresponds to the row the student is entered in. We'll call this variable i, and it is an integer type. Declare it by adding it to the first line existing in the code; it should now read Dim intGrade, i As Integer.

We can then delete the last two "blocks" of code (where we referred to cells C4 and C5). Then, in the remaining block of code (which should refer to row 3, i.e. cells C3 and D3), replace any instances of 3 with i. For example, the line that used to read intGrade = Range("C3").Value should now read intGrade = Range("C" & i).Value. The If statement should also now use this format (i.e. wherever "D3" was entered before, replace it with "D" & i). Now create the initial value for i - Dan is the first student, in row 3, so at the very beginning (right after declaring the variables), we need to set i equal to 3, using the line i = 3. Finally, add in the Do While loop and a line to increment the value of i with each loop. This should look like this:

Do While Range("C" & i).Value <> ""
    intGrade = Range("C" & i).Value
    'If statement
    i = i + 1
Loop

This means that the code will loop through the If statement for as long as there is a student grade entered in column C, row i. You're finished code should look like this:


When you run your code, you should now get a letter grade for all of the students in the list - regardless of the number of students you have in the list! Previously our code would only work for 3 students, but now you can add 10, 20, 30 or 473 students and the macro will determine the letter grade for all students.

If Statements

A commonly used command in VBA is the If statement. It's used to check that a certain condition is met prior to executing your code. It can be a single condition, or you can have more complex If statements with multiple conditions and different code for each condition.

The syntax of an If statement is:

If Condition Then
    'Code to be executed
End If

The condition usually checks that a variable has a certain value. For example, you might have a worksheet in your workbook that you only want people with a password to be able to access. In this case you would probably have a user input (where the user would type the password, let's call the variable strInput) and then you could compare their input to the correct password. The If statement would look something like this:

If strInput = strCorrectPassword Then
    'Display protected worksheet
End If

Taking this idea further, you could also have a message appear if the user enters the wrong password. This would use an ElseIf command:

If strInput = strCorrectPassword Then
    'Display protected worksheet
ElseIf strInput <> strCorrectPassword Then
    Msgbox "The password you have entered is incorrect.", vbExclamation, "Incorrect Password"
End If

As you can see, by using the If... Then and ElseIf... Then statements you can create reasonably complex If statements for a large number of conditions. In addition to these two you can use the "Else" statement, which executes when none of the conditions in the If statement are met.

To learn about If statements we're going to write a macro that determines a letter grade for a group of students based on their numerical (%) grade. Open a new workbook and enter the following data into Sheet1, starting with the "Student" column in cell B2:

Student	Grade (%)	Grade
Dan	93
James	72
Laura	37

Then add a command button to the sheet and label it as "Generate grade". Your workbook should look like this:

Click on the button and then View Code. First we need to declare a variable to store the grade. An integer type will be fine for this:

Dim intGrade As Integer

We then need to read the first grade in from the worksheet and use an If statement to determine what range the grade falls in, in order to assign an A, B, C, etc. to the student. This should look like this:

intGrade = Range("C3").Value
If intGrade > 90 Then
    Range("D3").Value = "A"
ElseIf intGrade > 80 And intGrade < 90 Then
    Range("D3").Value = "B"
ElseIf intGrade > 70 And intGrade < 80 Then
    Range("D3").Value = "C"
ElseIf intGrade > 60 And intGrade < 70 Then
    Range("D3").Value = "D"
Else
    Range("D3").Value = "F"
End If

As you can see, this example demonstrates the use of If... Then, ElseIf... Then, and Else... It also shows how you can use 2 conditions for If or ElseIf statements (e.g. intGrade must be greater than 70 but less than 80).

The If statement above will only determine the letter grade for the first student, Dan. We now need to copy and paste the code above twice in order to determine the letter grade for the remaining 2 students. When you do this make sure you change the cell reference each time (so the first If statement block should read the grade from cell C3 and then output an A, B, C, etc. to cell D3; the 2nd should use cells C4 and D4, and the 3rd should use cells C5 and D5). This would be quicker and more efficient with a Loop, but we'll learn about those next time.

Your finished code should look something like this (some of the lines aren't visible in this screenshot, but it should give you the idea):

Now when you go back to the worksheet and click on the button, the macro should automatically give display an A for Dan, a C for James and an F for Laura.

Once you have finished this tutorial, save the workbook somewhere as we'll use it in the next tutorial to learn about Do While loops.

Variables - Types and Declaration

A variable is used almost every time you output a value or take input from a user. A variable is simply a (temporary) means of storing a value which is used within the macro. There are several different types of variable, used for storing different types or sizes of values:

Byte

Holds positive values from 0 to 255. Requires only a single byte of memory, so is very efficient.

Boolean

Only 2 possible values: True (-1) and False (0). Requires 2 bytes.

Currency

Stores numbers from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 as fixed-point data.

Date

Stores dates and times as real numbers using 8 bytes of memory. The number to the left of the decimal point is the date and the value to the right of the decimal point is the time.

Double

A floating-point data type, ranging from -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E45 to 3.402823E38 for positive values.

Integer

Holds positive and negative integer values from -32,768 to 32,767. In the past there was a performance advantage to using integers instead of long variables (integers take up less memory), however in recent versions of Excel integer variables are converted to long variables, meaning that any performance advantage no longer exists. Requires 4 bytes (formerly 2 bytes).

Long

Holds positive and negative numbers from -2,147,483,648 to 2,147,483,647, using 4 bytes of memory.

Object

A data type that stores the address of an object in Excel.

Single

A floating-point data type, ranging from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. Requires 2 bytes of memory.

String

A variable that stores text rather than numbers, from 0 to 65,535 characters. Can take any letter, number, spaces, punctuation or symbols.

Variant

The largest memory-wise of the variables at 16 bytes, a variant data type can store any kind of data (strings, numerical values, dates, etc.) If a variable is not explicity declared as one of the other variable types, Excel automatically assigns the variant data type to a variable.

Variable Declaration and Scope

When writing a macro you usually know what data each variable will be storing, so you can declare each variable as an appropriate data type. A variable is usually declared locally - within a procedure. When that procedure ends, the variable is erased from the computer's memory. Declaring a variable in this way is done for example as shown below:

Sub Macro() Dim intVariable As Integer End Sub

A variable can also be declared at the beginning of a module (which can contain more than one procedure). In this case, the data stored in the variable is valid for and can be used in all of the procedures within that module:

Dim intVariable As Integer Sub Macro() 'Code End Sub

Finally, a variable can be declared at the top of a public module. These variables are then valid for all procedures within all modules:

Public intVariable As Integer

It is also possible to declare more than one variable of the same type at the same time. This would be done like this:

Dim intHeight, intWeight As Single

I generally choose variable names that indicate the variable type; this makes it easier further down in the code to quickly identify what type of variable you're using for certain data. Note also that variable names must be continuous characters with no symbols or spaces, and cannot start with numbers.

So are you ready to write your first program with variables and user input?


Example Macro
The goal of this macro is to build on the first "Hello, World!" macro in my previous post by taking input from the user and displaying a message saying how old the user is.

Open a new workbook and set up a couple of cells as shown below (note that you must use the same cells - B2 and C2 - or edit the code below to read the correct cell for the user input). Then add a button and edit the caption of the button to read "Calculate age" (clicking cancel when the "Assign macro" window pops up after creating the button).

Select the button, then click on the View Code button in the toolbar. Declare two variables; the user's birthday (a date type variable) and the user's age (an integer type variable):

Sub Button1_Click()
    Dim intAge As Integer
    Dim dateBirthday As Date
End Sub

Now we need to read the user's birthday into the dateBirthday variable and then calculate the user's age from their birthday. Add the following lines to do so:

dateBirthday = Range("C2").Value

intAge = Application.WorksheetFunction.RoundDown((Date - dateBirthday) / 365, -0.1)

The calculation of intAge uses VBA's ability to call functions from Excel itself. In this case we need to round down - for example if the user is turning 38 in 2 months, using an integer variable for his age will result in it being rounded up to 38 - when he's actually still 37! Using the RoundDown function makes sure the calculated age is correct. And finally we display a message giving the user's age by adding this line:

Msgbox "The user is " & intAge & " years old.", vbOkOnly, "Age"

Your completed macro code should look like this:
You can now close the code editor window; enter your birthday into cell C2 and click the "Calculate age" button and, if your code is correct, you Excel should give you your age!

Setup and Hello World!

This post will run through a few quick points on setup, and then get you programming!

Start by opening up Excel. Depending on the version you have, you need to enable the Developer tools. I use Excel 2007, so the following instructions are for the 2007 version only. Click the Office button in the to left corner of the window and select "Excel Options". The "Popular" tab will automaticallybe highlighted. The third checkbox down from the top, labelled "Show Developer tab in the ribbon", should be checked (see screenshot below) - if it isn't make sure it is and then click OK.

Now you should have the "Developer" tab visible next to the other tabs (e.g. "Home", "Insert", etc.) Click on the Developer tab. In later tutorials I'll go through these controls in more detail, but for now we'll just take a quick look. The two sections of this tabe that will interest us the most are the "Code" and "Controls" sections. The controls allow us to add various, err, controls, to the workbook with which the user can interact. The Properties and View Code buttons allow us to modify those controls to look and behave the way we want.

The "Code" section contains a few different ways of viewing and editing the code in the macros. It also contains a button labelled "Macro Security", which we're going to click now. This brings up a box displaying macro settings. By default macros will be disabled and so you will not be able to run any. Select either "Disable all macros with notification" or "Enable all macros". The former option will ask for permission to run macros whenever you try to do so, whereas the latter will allow all macros to run. While enabling all macros could potentially be dangerous, if you aren't running macros from other sources there's no danger. Click OK.

We're now ready to start making our first macro!

Hello, World!

This macro will display a message box and a message when the user clicks a button.

The first step is to create the button. Under the "Developer" tab, click on "Insert" and choose a Button (the top left control, represented by a grey rectangle). Your mouse will change to crosshairs which you use to draw and position the new button. Click and hold anywhere in your worksheet and drag the mouse to create a button of the desired size. When you release the mouse button, a new window will show up, asking you to assign a macro to the button. For now just click Cancel.


The default caption of the button is "Button 1" - to change this, right click on the button and select "Edit Text". Delete the name and replace it with "Hello, World!", then click outside of the button. If you right click on the button again and select Format Control, further display options allow you to customize the button to look the way you want. Play around with them if you want to, then proceed to the next step.

Click on the button again (this time left click to just select the button without entering the Edit Text mode), then click on "View Code" in the Controls section of the Developer tab. This creates a new module (Module 1) and sub routine, Button1_Click(). In later tutorials I will explain more about sub routines and the VBA editing screen, but for now lets go ahead and code.

Between the two automatically generated lines "Sub Button1_Click()" and "End Sub", type the following code:

MsgBox "Hello, World!", vbOKOnly, "Hello, World!"

Your VBA editor window should now look like this:


Close the VBA editor window, save the worksheet somewhere, and then click on the "Hello, World!" button. If everything behaves as it's supposed to, you should now be looking at a message box saying "Hello, World!" - congratulations, you've programmed your first Excel macro!