VBA Introduction - Excel

Hi Everybody, today i'm gonna teach you how to code in Excel, Word, PowerPoint, etc. But you should ask you: Is that possible? Yes it is, with VBA or Visual Basic for Application. In fact you can code in any program that belongs to Microsoft Office to make your life easier using Macros.

Everybody can create macros using the recorder, but this option limits a lot, because the steps that you recorder are just the ones that always the Macro is going to execute I mean is to static, but what happens if you want to make it a little bit more dynamic, well that's where VBA appear.

This time we are going to create a Macro in Excel and with this we are going to learn:

• Format options
• Conditional Sentences
• With Use

Ok fisrt of all we need to Open Excel and activate the Developer Tab:

• Go to File
• Click on Options

• Click on Customize Ribbon

• Select Developer in the Main Tabs bucket

• Click Ok

Now once we have the developer Tab, to create a Macro we have to click on VisualBasic Option:

 

Then a new window will appear the name of this window will be something like: Microsoft Visual Basic for Application... This is the enviroment where we are going to code:

To code a macro go to View->Code and then a new window will appear, in this window we are going to code. As you see this windows is blank, to create a Macro, write the following:

Sub MainMacro()

     

End Sub

 

With this 2 lines of code we have create a macro called: MainMacro, and now the funny part: LET'S CODE!!! This program will do the following:

• If a cell contains "RED", the Font color will be red
• If a cell contains "BORDER", this cell will have border
• If a cell contains "CHANGE", the cell will change its value for something that the user will type
• And if a cell contains "ALL", the font color will be green with border and the content will change for something that a user will type

As you can see the macro will do easy things. Now the code:

Sub Macro()

    Dim cel As Range

    For Each cel In Selection.Cells

        With cel

            If .Value = "RED" Then

                .Font.Color = vbRed

            End If

            If .Value = "BORDER" Then

                .Borders(xlEdgeBottom).LineStyle = xlContinuous

                .Borders(xlEdgeRight).LineStyle = xlContinuous

                .Borders(xlEdgeTop).LineStyle = xlContinuous

                .Borders(xlEdgeLeft).LineStyle = xlContinuous

            End If

            If .Value = "CHANGE" Then

                .Value = InputBox("Enter the New Value")

            End If

            If .Value = "ALL" Then

                .Font.Color = vbGreen

                .Borders(xlEdgeBottom).LineStyle = xlContinuous

                .Borders(xlEdgeRight).LineStyle = xlContinuous

                .Borders(xlEdgeTop).LineStyle = xlContinuous

                .Borders(xlEdgeLeft).LineStyle = xlContinuous

                .Value = InputBox("Enter the new Value")

            End If

        End With

    Next

End Sub

 

Now the explanation:

• With this the Macro will change the color
• With this the Macro will add the borders
• With this the Macro will ask the new Value
• With this the Macro will have the current cell
• With this the Macro go over the selected cells
• With this, well is an option that cut our lines of code

Now before we run the Macro, we need to select the cells where the Macro will apply, to do this we just need to select the cells

 

Then we need to run the macro, just click on the play button on the Microsoft Visual Basic for Application window

 

Once we click the button the following window will appear, now we just need to click on Run

 

 

Now after we run the Macro the result will be the following

 

 

 

As you have noticed, the window to input the new values is this one:

 

 

And well now What have we learned?

 

• Format options

To change the format in a Macro, you can use the following line:

cell.font.color=vbWhite or cell.Borders(xlEdgeBottom).LineStyle = xlContinuous

The first one will change the Font color and the second one will add a border at the bottom of a cell

• Conditional Sentences

In this example we have used the If and the For each clause, the use of this clauses is the same as in VB:

For Each value In ValueArray
     If value="something" Then
          ...
     End If
     ...
Next

• With use

The With clause help us to reduce our lines of code and to make our coding easier, for example, let's say we have the following code:

cell.Value="Value"
cell.Font.Color=vbWhite
cell.Font.Bold
cell.Font.Italic

We can replace that code with this:

With cell
     .Value="Value"
     .Font.Color=vbWhite
     .Font.Bold
     .Font.Italic
End With

As you have noticed we replace the cell. with the With clause to make our code easier and with a better understanding. I mean, if we use the same variable but with different properties we can apply the With clause to have a better understanding of our program.

Well that's it for today, If you have doubts you can leave them in the corresponding area, and i hope i have helped you at least a little bit. Well see you chavoz ^_^/ and thanks to my girlfriend because she is the expert on this.