Excel Advanced: VBA

VBA (Visual Basic for Applications)

VBA is an extremely powerful programming language that can be used in Excel. VBA is useful when a process needs to be automated. Many people spend a lot of time doing repetitive processes in Excel. VBA can help you make week-long processes into automated instant processes.

You will need the “Developer” tab. Backstage view(File)> Options> Customize Ribbon> Check the box next to developer…

Developer tab

You will also need the VBA Analysis ToolPak… Click File> Options> Add-Ins> Click on “Analysis ToolPak – VBA”> Click “Go”>

VBA analysis Toolpak

Check “Analysis ToolPak – VBA”> Click “Ok”

Add-ins

Recording Macros

One easy way to start learning about VBA is recording macros and reading the VBA. Every time you do something in Excel, you are sending messages to the program to do a certain thing. VBA is a language that you can talk to the computer with.

Click on the developer tab, then click record macro.

recordmacro

It will ask you to give the macro a name. The picture says “Dallins Macro” but actually you can’t have spaces in the name, so don’t include any spaces.

namemacro

Next, click in cell A1. enter the numbers 1-10 down the “A” column like shown below and then press stop recording.

stoprecording

Click on “Visual Basic” to view the command that the computer received.

vbabutton

Select “Module 1” under the modules folder under VBA Project(“Name of your file”)… (Book1 is the name of the file in this case)

findmodule

This may look like gibberish to you but lets look at each line.

  • Sub – means you are starting a macro.
  • DallinsMacro () – the name of the macro
  • ‘ DallinsMacro – a single quote ” ‘ ” followed by any text means you are just leaving notes or comments in the code that the computer will not read
  • Activecell – refers to the cell you were clicked in before you ran the macro
  • .FormulaR1C1 – I never use this when coding but it essentially means that you are entering a formula in a Row(R) and Column(C) format.
  • = “1” – means you are entering a formula that is the number 1…
  • Range(“A2”) – refers to the cell reference “A2”
  • .Select – means you want to select the range you refereed to

This code by itself isn’t very useful, but we can alter it to make it more useful. On your excel sheet delete the numbers 1 – 10. Then, click on cell “C2”

deleteandselectC2

Replace the  all the “formulaR1C1” with the word “value” and the numbers inside quotes with just plain numbers. (Hint: Copying and pasting lines of code is a very useful practice in VBA. Try it out!) We are telling Excel to assign the number 1 to the value of the active cell, which is whichever cell you have selected before you run the macro.

value

 

Now go back to the VBA screen and press the Run (“Play”) button.

runmacro

The results will be similar except cell “C2” will have a 1 in it and cell “A1” will be blank.

activecell

Why is that? Because before you clicked run, you had selected cell “C2” and the first command in the code is to make the value in the active cell (“C2”) equal to 1. How could we make excel put a 1 in cell “A1” regardless of where we are clicked when we run the macro?

You can set a fixed start position for the macro. If you insert “Range(“A1″).Select” at the top of your code, the outcome will be that every time you run this code, A1 will be filled with the number 1, no matter where you are clicked when you run the macro.

startposition

Now what if I wanted the list to start in column B instead of column A?

 

Writing VBA

 

To open the VBA window you can either press ALT-F11 or you can go to the “Developer” tab and click on the “Visual Basic” button in the “Code” group.

Picture1

How to Start

    1. Insert a module… Insert> Module
    2. Type “Option Explicit” (Forces explicit declaration of all variables in a file)

Module option explicit

3. Begin your macro: “Sub Whateveryouwanttonamethemacro ()”

Important things to know:

Do Loops (You gotta loop your do!!)

Sub excel3 ()

Dim orow As Integer
Dim lastrow As Integer

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

orow = 1

Do Until orow > lastrow

Cells(orow, 1).Resize(1, 9).Interior.ColorIndex = 6

orow = orow + 2

Loop

End Sub

If/Then statements in VBA
      • If you have an “If” you need an “EndIf”
      • You need a “Then” for every “If” and “ElseIf”
      • Only the last statement needs an “Else” followed by an “EndIf”

if then else elseif

Using “Activecell”

Activecell.value
Activecell.offset(rows,columns)
Activecell.end(x1down)

Using “Cells”

Cells(row_index,column_index)

Assigning Values to Variables

Variable = activecell.value
Variable = activecell.offset(rows,columns).value
Cells(row_index,column_index) = Variable