Visual Basic in Excel / PDF

Visual Basic in Excel / PDF

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

6.      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)

7.      Get back to the worksheet by clicking on the blue X of the command panel (or hit Alt_F11).

8.      Click on Exit Design Mode of the Control Toolbox.

9.      Click on the Command Button and it will perform the sum.

10.  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.......

Visual Basic in Excel / PDF