Primer for Visual Basic in Excel

Introduction

Visual Basic (VB) is the controlling language that enables you to customize Microsoft Excel, Word, and PowerPoint. VB is a cumbersome language, and one way to learn it is to have someone else write the computer program for you. The way to do this is to create a MACRO. Any time you create a macro, Excel writes a program (a Module) for the macro in VB.

Getting To the Visual Basic Editor

ALT_F11

Travels between the Visual Basic Editor and the Excel Worksheet. You can also click on the bottom panel or on the top left Blue Excel icon or Click on Tools – Macro – VB Editor

Writing Visual Basic Programs Automatically by Creating Macros

Macros are automated procedures. If, for example you want to create a MACRO that adds the numbers in row 1 and colors the sum box yellow take the following steps

Numbers / 3 / 2 / 5 / 6 / 3 / Sum =
  1. Click on Tools - Macro - Record New Macro
  2. Name your macro and give it a keystroke (e. g., CTRL_T)
  3. In Cell H1 write = SUM(B1:F1)
  4. Color Cell H1 Yellow
  5. In the little Macro Box (or on Tools – Macro – Stop Recording) click Stop Recording.
  6. Click CTRL_T to perform your task automatically.
  7. To see the code you created, Click on Tools – Macro – Macros – Edit

VB Code for the Macro

Sub Macro1()

' Macro1 Macro

' Macro recorded 4/26/04 by stan

' Keyboard Shortcut: Ctrl+t

Range("H1").Select

ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-2])"

Range("H1").Select

With Selection.Interior

.ColorIndex = 6

.Pattern = xlSolid

End With

End Sub

Note: Green Lines Beginning with ' represent Comments or Remarks. These are dead lines as far as the program is concerned. They take no actions but are put there by the programmer as reminders of what the program or specific command does.
Running and Debugging VB Programs

To make a program work you must RUN it. However, if the program has syntax errors it will not run. It must then be corrected and restarted or RESET.

All programs can be run by

  1. Pressing the function key, F5 or by
  2. Clicking on RUN-Run/SubUserForm at the top menu bar in the VB Editor

If your program is contained in a

  1. MACRO Then return to the Excel Worksheet and hit the Shortcut Key you created.
  2. COMMAND BUTTON Then return to the Excel Worksheet and hit the button

If the program has syntax errors a pop up box will appear. To find the line in the program that contains the error

  1. Click on DEBUG. The first line with an error will be painted yellow.
  2. Find and correct the error and RESET the program by clicking on Run - Reset
  3. Run the program again. If another error appears repeat steps 1 - 3.

VB Program Structure and Elements

VB Programs may seem complicated but often have a simple structure. A typical skeleton structure is given below. Note that all programs start with a MACRO or SUB () type statement and ends with End Sub. Often this is automatically done for you as are some comments (Green Lines starting with ' )

  1. Name Your “Babies” – all Constants and Variables (e. g., Dim j as Integer)
  2. Get data from Worksheet or from a file
  3. Solve an Equation or Perform a Task
  4. Put solution or work back on Worksheet

Writing Your Own Programs and Command Buttons

VB programs can be made user friendly by placing them in Command Buttons on the Excel Worksheet. Follow the steps below to create a Command Button to add the numbers in Cells B2-H2 and write the sum in Cell I2. You will have to write one line of code in VB or create a Macro and then copy and paste its code to the Command Button VB sheet (see below).

Day / Sunday / Monday / Tuesday / Wednesday / Thursday / Friday / Saturday / Total
Work Hours / 19 / 5 / 6 / 4 / 9 / 12 / 16
  1. Click on Tools - Customize - Toolbars
  2. Check Control Toolbox (This will make it appear in the worksheet)
  3. Click on Command Button in the Control Toolbox
  4. Move mouse to the worksheet, click, hold and drag it, and release.
  5. Double click in the Command Button - this brings up the Visual Basic Sheet for the Code.

Private Sub CommandButton1_Click()

' Program Goes Here *** Either write it or copy and paste it from a Macro

End Sub

  1. In the VB Code type in the command

Cells (2,9) = Cells (2,2) + Cells (2,3) + Cells (2,4) + Cells (2,5) + Cells (2,6) + Cells (2,7) + Cells (2,8)

  1. Get back to the worksheet by clicking on the blue X of the command panel (or hit Alt_F11).
  2. Click on Exit Design Mode of the Control Toolbox.
  3. Click on the Command Button and it will perform the sum.
  4. Click on the Command Button every time you enter new numbers to get a revised sum.

You can also Copy and Paste a VB program from a Macro to the VB Sheet for the Command Button

Code for EXCEL Macros and Functions and Command Buttons

The programs below are useful examples that show how to

  1. Size and Color Excel cells
  2. Get data from Excel Worksheet, Process it and then Put it Back into Excel Worksheet
  3. Create repetitive steps using FOR-NEXT loops
  4. Create arrays of repetitive steps using nested FOR-NEXT loops
  5. Create choices using IF and SELECT CASE
  6. Insert Data from Files and Insert Pictures in Excel

Sub Macro1()
' Resizes cells of an entire worksheet as small squares and colors paints them turquoise

Cells.Select
Selection.RowHeight = 4
Selection.ColumnWidth = 0.5

Selection.Interior.ColorIndex = 9
End Sub

Sub Macro2()

' Get the data in Cell A2 and double it in Cell A3

Range("A3").Select

' Double the number in the previous row R[-1]

ActiveCell.FormulaR1C1 = "=2*R[-1]C"

End Sub

Sub Workbook_Open()

' Get the data in Cell A2 (Row, Col) = (2, 1) and double it in Cell A3 (Row, Col) = (3, 1)

' This Program uses cell Reference. IT ALSO CONTAINS THE SKELETON STRUCTURE.

' 1. Name your babies!

Dim x as Single

' 2. Get data value from worksheet

x = Cells(2, 1).Value

' 3 and 4. 3. Do work (calculation) and 4. Put calculated value back in worksheet

Cells(3, 1).Value = 2 * x

End Sub

1 / 5 / 3 / 9 / 2

Sub Macro3()

' Using the Worksheet above, get the data in A1, double it in A2

‘Then copy the formula in Row 2 for all data points in Row 1 (A1:E1)

Range("A2").Select

ActiveCell.FormulaR1C1 = "=2*R[-1]C"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:E2"), Type:=xlFillDefault

Range("A2:E2").Select

End Sub

Repetition and Loops

The beauty of the computer is that it can perform multiple or repetitive steps very quickly. This is done using loops. There are several types of loops including 1. (For – Next) and 2. ( Do Until – Loop).

We use the For – Next Loop. Its syntax is

‘ 1. Start (Open) the Loop This loop prints the word, Hello 10 times. The symbol, j is

For j = 1 to 10

‘ 2. Do the work

Print “Hello”

‘ 3. End (Close) the Loop

Next j

Arrays can be created using Multiple Loops. The proper syntax is to embed one loop in another. Such loops are called Nested Loops. Syntax for a 2-D array is

‘ 1. Open the First or Outer Loop

‘ 2. Open the Second or Inner Loop

‘ 3. Do the Work

‘ 4. Close the Second or Inner Loop

‘ 5. Close the First or Outer Loop

Note: Work can also be done between steps 1. and 2. and between steps 4 and 5.

Sub Workbook_Open()

' This program does same job as Macro3() but uses a FOR-NEXT Loop with specified limits

' Limitation - must be modified to accept different number of entries

Dim x as single

Dim col as integer

For col = 2 To 5

x = Cells(1, col)

Cells(2, col) = 2 * x

Next row

End Sub

Sub Macro3C()

' FIRST, IN EXCEL, enter values and name m, cc, and dq then calculate dT in cell E2

Range("E2").Select

Selection.FormulaArray = "=dq/(m*cc)"

End Sub

1 / 1 / 3 / 2 / Number / Frequency
2 / 1 / 2 / 1 / 1
1 / 2 / 3 / 2 / 2
3 / 3 / 1 / 2 / 3

Sub Macro3D()

' Determine the number of cells with specified values for the Worksheet above

Range("G2:G4").Select

Selection.FormulaArray = "=FREQUENCY(R[-1]C[-6]:R[2]C[-3],RC[-1]:R[2]C[-1])"

End Sub

Sub Workbook_Open()

' This Program prints the color index in Column A and paints the color in Column B

' To find color index key hit F1 on the word, ColorIndex

Dim i As Integer

For i = 1 To 52

Cells(i, 1) = i

Cells(i, 2).Interior.ColorIndex = i

Next i

End Sub

Sub Macro4()

' FIRST, In Excel Worksheet, Enter # of Tens in Row 1 (B1 to K1) and # of Units in Column A

' Sum 10* number in Row 1 plus number in Column A

' This is code if we write formula in Cell B2 then copy and drag down, then copy and drag across
Range("B2").Select

ActiveCell.FormulaR1C1 = "=RC1+10*R1C"

Selection.AutoFill Destination:=Range("B2:B11")

Range("B2:B11").Select

Selection.AutoFill Destination:=Range("B2:K11"), Type:=xlFillDefault

End Sub

Sub Macro4B()

' FIRST, In Excel Worksheet, Enter # of Tens in Row 1 (B1 to K1) and # of Units in Column A

' Sum 10* number in Row 1 plus number in Column A

' The code below was created by highlighting B2:K11 and entering the formula with CRTL_SHFT_ENTER

Range("B2:K11").Select

Selection.FormulaArray = "=RC[-1]:R[5]C[-1]+10*R[-1]C:R[-1]C[5]"

End Sub

Sub Workbook_Open()

' This Program uses Nested Loops and Cell Reference to create the numbers from 0 to 99 in a 2-D Array

' This writes numbers equal to the col - 1 plus 10* the (row - 1)

Dim row As Integer, col As Integer

For row = 1 To 10

For col = 1 To 10

Cells(row, col) = col - 1 + 10 * (row - 1)

Next col

Next row

End Sub

Name / Grade / Test
John / 65
Mary / 95
Sue / 35

Sub Macro(5)

'Assign Grade using If Statement and Color using Conditional Formatting

Range("B2:B4").Select

Selection.FormulaArray = "=IF(RC[1]:R[2]C[1]<60,""F"",IF(RC[1]:R[2]C[1]<90,""P"",""A""))"

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""F"""

Selection.FormatConditions(1).Interior.ColorIndex = 3

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""P"""

Selection.FormatConditions(2).Interior.ColorIndex = 6

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""A"""

Selection.FormatConditions(3).Interior.ColorIndex = 4

End Sub

Sub Workbook_Open()

' This Program assigns grades and colors using Cell Reference the If Statement in a Loop

Dim row As Integer, score As Integer, hue As Integer

Dim Grade As String

For row = 2 To 4

score = Cells(row, 3)

If score < 60 Then

Grade = "F"

hue = 3

ElseIf score < 90 Then

Grade = "P"

hue = 6

Else

Grade = "A"

hue = 4

End If

Cells(row, 2) = Grade

Cells(row, 2).Interior.ColorIndex = hue

Next row

End Sub

Sub Workbook_Open()
' This Program uses nested FOR-NEXT loops and SELECT CASE to color cells

'FIRST IN EXCEL Enter numbers from 1 to 64 in cells A1 to H8

' Spectrum colors are #11, 5, 8, 4, 6, 45, 3

Dim row As Integer, col As Integer

Dim x As Integer, hue As Integer

For row = 1 To 8

For col = 1 To 8

x = Cells(row, col).Value

Select Case x

Case 0 To 9

hue = 11

Case 10 To 19

hue = 5

Case 20 To 29

hue = 8

Case 30 To 39

hue = 4

Case 40 To 49

hue = 6

Case 50 To 59

hue = 45

Case 60 To 69

hue = 3

End Select

Cells(row, col).Interior.ColorIndex = hue

Next col

Next row

End Sub

Sub Macro5b()

' This program uses a nested FOR-NEXT loops to create a grid of rectangles colored by RGB values

‘ NOTE: It runs slowly for large grids

Dim i As Integer, j As Integer

Dim x As Single, y As Single, dx As Single, dy As Single

dx = 5

dy = 5

For i = 1 To 10

For j = 1 To 10

x = 5 * i

y = 5 * j

ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, dx, dy).Select

Selection.ShapeRange.Fill.ForeColor.RGB = RGB(10 * i + 100, 250 - 10 * j, 0)

Selection.ShapeRange.Line.Visible = msoFalse

Next j

Next i

End Sub

Sub Macro6()

' This Macro inserts a picture from a file, moves and resizes the picture

ActiveSheet.Pictures.Insert("C:\images\braz_04.jpg").Select

Selection.ShapeRange.IncrementLeft 82.5

Selection.ShapeRange.IncrementTop 84#

Selection.ShapeRange.ScaleWidth 1.59, msoFalse, msoScaleFromTopLeft

Selection.ShapeRange.ScaleHeight 1.59, msoFalse, msoScaleFromBottomRight

End Sub

Sub Workbook_Open()

‘ Program to put data from a file with 60 rows and 120 columns into an Excel Worksheet

‘ For this program you must know file size

Dim Row As Integer, Col As Integer, z As Integer

Open "c:\s200\nyc_grid.txt" For Input As #1

For Row = 1 To 60

For Col = 1 To 120

Input #1, z

Cells(Row, Col) = z

Next Col

Next Row

Close #1

End Sub

Private Sub Workbook_Open()

‘ Program to put data from file named in Worksheet 1 onto a new worksheet.

‘ Then in Excel change text to columns

Dim row As Integer, A As String

Dim B As String

row = 1

A = Cells(row, 1)

Sheets.Add

Sheets("Sheet4").Select

Sheets("Sheet4").Name = "Data"

Open A For Input As #1

Do Until EOF(1)

Line Input #1, B

Cells(row, 1) = B

row = row + 1

Loop

End Sub

The Input Box: An Automated Procedure to Put Data Into VBX Programs From the Excel Worksheet

The Input Box tells the user what data to enter in a program. The user friendly program below to find the cube of a number is run with a command button. After clicking on the command button, an Input Box pops up asking the user for the number he wants to cube. The

Private Sub CommandButton1_Click()

Dim x As Single, y As Single

x = InputBox("Enter number to cube")

y = x ^ 3

Cells(2, 2) = y

End Sub