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.