VISUAL BASIC: the basics

Now that you know the meaning of VBA (if you just landed on this page, VBA stands for Visual Basic for Applications) and his the programming language that allows you to program in all the Microsoft Office application including Excel. Now let's see what it can do......

It can do Every......wait for it!!! (link to how i met your mother TV series ..... Thing....... IT can DO EVERYTHING......

Why can VBA do everything? Because it is a programming language and therefore, as it is a tool, VBA gives you the opportunity to create anything you want. This is the magic of programming in Visual Basic. Programming is a creative process that enables you, like a painter or a sculptor to let your imaginations and the skills you developed by training on VBA create almost any application. So let's try together to achieve this.

You can follow the first example here under which is creating a VBA button on one sheet and by pressing on it, it will jump to another sheet. This is especially useful when you have many sheets.

There are other examples that you can jump to like a VBA timer like in the example here. It gives you a signal every time a certain period has passed.

But it can be fun too, like here..... (you have to accept the macro to see it work). this macro will make your screen dance, the best is to watch at night this psychedelic show. Not for people who do not like blinking lights (see it here).

How to begin creating a Button to Jump on another Excel Sheet. Let's do it step by step.

The easiest way to start with Visual Basic is to create a MACRO.

We are going to create a first easy and useful macro. Ahh, before I forget, a macro is a recorded series of commands that you can execute by calling the macro. The macro can be called by using a button or a combination of keys (like CTRL-SHIFT-ALT-M)  for example. This is what we are going to see here under. Macros are written in Visual Basic. So recording a macro automatically creates Visual Basic code.

 



 

First you have to activate the developer ribbon. This is done HERE.

Create a new Workbook and create 2 spreadsheets.

spreadsheet

becomes

spreadsheet

Go back to Sheet 1.

 

Now we are going to insert a Button around the cell A1. For this press the Insert Tab in the DEVELOPER RIBBON.

insert Button

Then press the top left item, which is a button.

The cursor will change into a cross.

create button

Draw a rectangle around the cell A1 area.

As soon as you release the mouse button, the following window will open.

macro1

This window allows you to assign macros to elements like buttons for example and also to record new MACRO.....so PRESS RECORD......

The following windows opens:

macro recording

Enter a comment if you wish and press OK.

Then the window disappears and press the Sand press the Spreadsheet 2 tab at the bottom in order to move the this sheet and press the STOP RECORDING BUTTON.

stop recording macro button excel

Now you should have a button called Button1 in the sheet 1.

button in sheet excel

Now GO to Sheet 2 and do exactly the same.

Create a button, a draw it into the cell A1.

create button

You can see the window, lists the old button1_click macro and the new one will be called Button2_click.

Press RECORD...

record

Enter a comment if you wish and press OK.

1) Then the window disappears

2) Press the Spreadsheet 1 tab at the bottom in order to move to this sheet

3) Then press the STOP RECORDING BUTTON.

stop recording macro button excel

You will have a new button 2 in sheet 2.

button 2 sheet 2 excel

Now press ESCape in order to deselect the button.

 

RESULT:

if you press the button 1 it goes to sheet 2 and if you press the button 2 it goes to sheet 1......

THIS WAS YOU FIRST MACRO...... and by the way, there is visual basic code behind.....

You can see it here.

By pressing the MACRO button in the DEVELOPER RIBBON.

macro button developer ribbon

Press the Edit button in the following windows to make the code appear.

EDIT MACRO EXCEL

You can see the code highlighted

code vba

Or here. Even the Comments you entered are seen in the code.

 

Sub Button1_Click()
'
' Button1_Click Macro
' goes to sheet 2
'
Sheets("Sheet2").Select

End Sub
Sub Button2_Click()
'
' Button2_Click Macro
' goes to sheet 1


Sheets("Sheet1").Select
End Sub

The code was really very simple coding but this was your first VBA. Congratulation.

You can download this example here.

Congratulation for your first "automated VBA code generation".....