How to get started with Excel VBA

We need to enable programming in Excelthe “Developer menu”

Objectives 1) To understand the components of the VBA interface 2) Understand the difference between objects and properties3) To use these principles to effect changes in the behaviour of chosen objects4) To be able to get objects to move around the screen

Make sure the Developer is ticked

Go to DeveloperVisual Basic

Programming window opens up, right click on Sheet1 and choose “Insert Form”

Like so

Ensure that you save your work as an excel macro enabled workbook at this stage otherwise you will not be able to access your work again in VB

Click view toolbox insert image

Rename the image box Imglightsoff and import the picture of the light bulb which is off The border style property should be set to 0

Insert another image box and import the picture of the other light bulb which is switched on

Insert a command button and call it cmdlights and move the image of the light on to cover the image of the light off

Double-click on the form to enter the “code” window. Most things are typed between Sub and End Sub which are created for you by VB

Declaring a variable name as Boolean

Use the select if to turn the lightbulb on or off, When the lights is true the light bulb is on and when the lights are false the light bulb is turned off

End if part

Press F5 of the run command to execute the program

Changes the caption on the command button

Lights off Lights on

Activities

Try to get the light bulb when it is switched on to move randomly around the screen .

Insert the following code, this is called a procedure which only activates when the lights = true statement is true . The command calls this procedure and makes the lightbulb move around the screen

Add 3 scroll bars from the toolbox call them HSBRED, HSBGREEN and HSBBLUE. Change the maximum value on 255

Create a new procedure called sub showformcolour()Generates a random number between 0 and 255 Creates a random number to change the colour background of the form Sets the background colour of the form based on the 3 prime colours

Now run the program

Extension Activities

1) See if you can randomly change the background colour of each individual colour on the form.

2) Insert a message using a label 3) Insert a message using a msgbox