Visual Basic (Excel VB/VBA) Tutorial: How To Write Your First Program

This hub provides an introduction to Visual Basic, using embedded VBA capability found in Excel 2010. Let’s get started. We will need what is known as the Developer tab in Excel. Open up new Excel worksheet, which is given default name “Book1”. Immediately save it:

File name: VBA1

Save As Type: Excel macro-Enabled Workbook

Now we will write a simple and fun program. We will need to use the “Developer Tab”. In order to make it visible, do as follows:

1. File --> Options (click)

2. Customize Ribbon (click)

3. Check the “Developer” box and click “OK” at bottom.

4. You will now see “Developer” tab. Click it.

5. Section dealing with macros and VBA (Visual Basic) is now visible.

Next we place what is known as a command button. Do as follows: Click the “Insert” icon. Under “ActiveX Controls”, click the little box circled in red, see picture area labeled “A”. Then move your mouse down into the spreadsheet and click. A button will appear. Note the “Design Mode” icon is shaded (picture area “B”). We are in design mode, meaning we can edit the button without it activating anything. Once we get out of design mode, clicking on the button will start the program. Right click on the button and select “properties”. A properties window will open up, it will be kind of thin. You can widen it by placing mouse pointer over right edge and dragging right. ”. Note that if you click out on the spreadsheet, the properties window changes and is now showing properties for the spreadsheet. Re-select the button again to get its window back. See in the window that “caption” is set as “commandbutton1”. Click in the “commandbutton1” box to get a cursor, and change the caption name to “Press Me”. See that the button name has changed. In the properties box, click the “categorized” tab. Where you see “AutoSize” and “False”, edit to make it “True”. See that he button has sized itself to just fit the text. We will next put the button in a known position. In the properties box, edit the “Left” position to “396” and “Top” to “93”.

Close the properties box. Click on the “Design Mode” tab to get out of design mode, as we are done with the button. On the far upper left, click the “Visual Basic” tab. Nothing seen. As shown in picture section “C”, click the little “View Code” box and the grey area comes in white. In the drop down, select “CommandButton1” and the code appears as shown. There are only two lines of code, the name of the subroutine and the line “End Sub”. Place your cursor just to the left of “End Sub”, and hit enter 5 times. This will move it down and provide space for us to paste some code in. Now arrow key the cursor up 2 or 3 lines so it sits in between. Here is the code to paste in at the cursor:

Application.EnableCancelKey = xlDisabled 'prevents program from hanging up and giving error message

Dim wordstr As String, x As Single, y As Single, acell As String, iCount As Single 'Declare variables

Range("A1").Select 'start at cell A1 to get a reference position

For x = 1 To 5 'does 5 loops of message display

For y = 1 To 5 'picks the five words which make up the message

Select Case y

Case 1:

wordstr = "this" 'when y is 1, it displays "this"

acell = "G4" 'places it in cell G4

Case 2:

wordstr = "is" 'when y is 2, it displays "is"

acell = "I4" 'places it in cell I4

Case 3:

wordstr = "your" 'when y is 3, it displays "your"

acell = "K4" 'places it in cell K4

Case 4:

wordstr = "first" 'when y is 4, it displays "first"

acell = "K12" 'places it in cell K12

Case 5:

wordstr = "program" 'when y is 5, it displays "program"

acell = "G12" 'places it in cell G12

End Select

Range(acell).Select 'find the cell

ActiveCell.Value = wordstr 'place the word in it

Selection.ColumnWidth = 8 'set the column width to 8 to make sure the word fits

Application.Wait Now + TimeValue("00:00:01") 'one second delay

ActiveCell.Value = "" 'remove the word, clear the cell

Next y 'loop

Next x 'loop

Once you paste in the code, close the visual basic program editing box. Save your worksheet in order to keep the changes. Now hit the “Press Me” button and watch your first program run. It will loop 5 times then stop. I have added comments to the code to explain what each line does.