Module 1 - Getting Started in VBA
Lesson 1.1 - Your First Program
Topic 1.1.4 - Running a Procedure

After you've written a subroutine and, optionally, saved your code, you can run it to make it carry out the instructions you've written.  This part of the lesson shows you how to do exactly that!

Files Needed

You can use the file you have created over the previous parts of this lesson.  If you don't have this code you can click here to download a copy.

Completed Code

You can click here to download a file containing the completed code.

To begin this part of the lesson, you need a copy of the basic procedure we created in the previous parts.  You can either use the version you have written, download a copy from the link in the Files Needed section above, or copy and paste the code shown below into a new module:

Sub My_First_Program()

 

'create a worksheet

Worksheets.Add

 

'enter values into cells

Range("A1").Value = "Wise Owl"

Range("A2").Value = Date

 

'format cells

Range("A1:A2").Interior.Color = rgbCoral

 

End Sub

Running Code from the VBE

You can run a procedure from within the VBE.  Start by clicking somewhere within the subroutine you want to run:

Click in subroutine

Click anywhere between Sub and End Sub to select the procedure.  If you don't do this, when you choose to run the code you'll see a dialog box asking you which procedure you want to run.

 

You can now choose one of the options from the table below to run your code:

Menu Keyboard Toolbar
Run | Run Sub/UserForm F5

If anything goes wrong when you do this you can learn about how to solve problems in the next part of the lesson.

Assuming that you didn't encounter any errors, you can now switch back to the Excel window (you can press Alt + F11 to do this) to see the results:

Results

You should see a new worksheet with values and formatting applied to these cells. We've increased the column width so that we can read the date.

 

Running Code from Excel

While we're in the Excel window, it's worth mentioning that you can also run your subroutine from here.  To do this, pick one of the options from the table below:

Ribbon Keyboard
Developer | Macros Alt + F8

You can then pick which subroutine you want to run from the dialog box which appears:

Select sub

Excel refers to your subroutines as macros. Pick one from the list and either double-click its name or click Run to run it.

 

You should now have another new worksheet containing your name and the current date.

You can now either continue with the Extra Practice section below, or move to the next part of this lesson to learn how to solve problems in your code.

Although you don't have much to practise yet, you may want to experiment with making some small changes to your code. 

  1. Add a line which writes the current time into cell A3:
Add time

Remember that you can press Ctrl + Spacebar after the = sign to see the IntelliSense list.

 
  1. Run the subroutine again, either from the VBE or Excel, and check the result in the Excel window:
Added time

You should find that the new worksheet contains the current time.

 
  1. Edit the line which formats the cells so that it includes cell A3:
Alter line

Alter this line so that it formats every cell between A1 and A3.

 
  1. Edit the same line and pick a different colour for the cells:
Change colour

Pick from any of the rgb colours in the IntelliSense list (press Ctrl + J to make the list appear).

 
  1. Run the subroutine again and check the results in the Excel window:
New colour

Your cells should be a different colour on the new worksheet.

 
  1. Add a line which changes the Color property of the Font of the cells:
Font colour

You can use the same range of colours for the font as for the interior of the cells.

 
  1. Add a new comment followed by a line of code which changes the width of column A to fit the text within it:
Autofit

The AutoFit method makes the column as wide as its widest value.

 
  1. Run the subroutine once more and check the results in the Excel window:
Final result

The final result of your hard work!

 
  1. Feel free to continue experimenting with different interior and font colours.  For reference, the final version of the subroutine is shown below:

Sub My_First_Program()

 

'create a worksheet

Worksheets.Add

 

'enter values into cells

Range("A1").Value = "Wise Owl"

Range("A2").Value = Date

Range("A3").Value = Time

 

'format cells

Range("A1:A3").Interior.Color = rgbCornflowerBlue

Range("A1:A3").Font.Color = rgbWhite

 

'change column width

Columns("A").AutoFit

 

End Sub

  1. When you've finished, move to the next part of this lesson to learn how to solve problems in your code.
This page has 0 threads Add post