Visual basic for applications using excel 2

Creating a macro using VBA for excel

Open excel-press alt and f11-This will take you into VBA.Go to INSERT- then select Module

When you open up the module it looks like this:

When you start your macro code you must type sub –which stands for subroutine

Then next to sub –type: Createandlabelnewsheet

Now we need to label what the macro is going to do.Use the speech mark symbol ‘ before the description

To create our code in vba we need to specify ‘The thing.action (object.method)

Ie say what you want to make a change to then what you want to do to it

Now it’s time to refer to cells

This time instead of using OBJECT.Method we are going to use Object.Property

As we are going to change an attribute of an object.

Object.property = Value

Type in the following code:

Using functions to calculate values

This is what the macro looks like –pressalt then f11 to go to spreadsheet

The created by name in b2 is the name of the user logged in -In this case my wife who’s username is mum!

Now you need to save your program you have created as a macro enabled work book

Close down your work book.Then reopen it .To get to the vba editor then select alt and f11

Then pres f5 and then switch back to your spreadsheet to see the effect

Now we have created a macro in vba that we can run by opening the enabled spreadsheet and by going to the macro’s tab in developer.we can also create a button that we can assign this vba script to.

Press alt then f8 to take you to the macro dialogue box

Your page will then look like this

Then to test your macro press ctrl button followed by the letter t

Nb you must delete the spreadsheet tab that the macro creates to see it working

Everytime you press alt and the letter t it will open then a new worksheet in the same work book

Now because for your end user it might not be apparent that th keyboard shortcut is there it might be better to create a button drawn on the work book that can be labelled that a user can click on.It might make it simpler.

So go to developer tab select insert-

We are going to use the forms section.select button

Draw the button on eth page where you want it to go

Now slect the macro you created in vba

Now label up your button so that it shows what will happen when you click it

Click on any cell other than it will then activate it

2 | Page