PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

SIMPLE COUNTER IN EXCEL USING VBA

INTRODUCTION

It’s always a great feeling to watch your things work. Similar is the case with the programming. When we program something and it starts working, happiness is the only thing what we get.

After we have got through all the basics of the VBA, let us start making small projects .

Projects help us to make use of the different tools to apply together and clarify our doubts.

We’ll make a simple counter with user interface in the sheet only.

OBJECTIVE

TO CREATE A SIMPLE COUNTER WITH A COUNT AND A RESET BUTTON . GRAPHICS ARE NEEDED IN SHEET ITSELF.

PRE PLANNING

USER INTERFACE

Let us pre plan the design and working of the application.

USER INTERFACE –

COUNTER:

We need a counter label or text which will change its value.

COUNT BUTTON:

A button is needed which will increase the counter when clicked.

RESET BUTTON:

A button is needed which will reset the counter to 0 when clicked.

VBA PROGRAMMING

We need to program the two buttons.

When COUNT button is clicked, the counter should go up by one.

When RESET button is clicked, the counter should reset to 0.

The counter can be made from the text and giving the design using the wordart.

LET US START ONE BY ONE.

PREPARATION FOR THE PROJECT

DESIGNING THE USER INTERFACE

COUNTER

STEPS

- Select a cell and enlarge it.
- Fix the font to be 96 or bigger.
- Use the word art to give it a lovely look. (INSERT WORD ART )
- To make the digits look like a counter
- Go to the cell and right click.
- Go to format cells.
- Go to Custom and click 0000 and check if the sample becomes 0001 or not.
- Click ok
- Out counter is ready.

BUTTONS

STEPS

- Insert a Shape as you want your button to be. (INSERTING SHAPES IN EXCEL)
- Give it a lovely shape and color.
- Change its text to COUNT.
- Copy the same button and paste a little down the previous button.
- Change its name to RESET.
- Our buttons are ready too.
- The buttons we created looked somewhat like in picture shown below.
- So now the complete USER INTERFACE IS READY.

THE COMPLETE USER INTERFACE

The complete user interface looks like this.

THE COUNTER IS IN THE CELL D3. It can be in any cell but we have to change the code accordingly.

CODING VBA

We need very few things for this counter to work.

A variable which can sustain its values even between different calls of the procedure as the counter is not going to be always on a run.

When clicked, the counter needs to increment its value.

When RESET, counter needs to set the value again to zero.

Let us try to code it. The code will be somewhat like the one given below.

CODE

Go to DEVELOPER TAB and click VISUAL BASIC on the leftmost area of the screen.

The Visual basic window will open.

Go to the PROJECT EXPLORER and double click the file SHEET1 (or whatever sheet name you have given)

Paste the code given below in the sheet and save.

‘gyankosh.net
‘simple counter

Dim A As Integer ‘module level declaration so that all the procedures can work on this.

Sub INCREMENT()

A = A + 1 ‘Incrementing the variable
Range(“D3”).Value = A ‘Setting the value in D3.

End Sub
Sub reset()

A = 0 ‘Setting the value to 0;
Range(“D3”).Value = A ‘Setting the value to cell D3

End Sub

EXPLANATION

There is just one statement in the code.

Range(“d3”) refers to CELL D3

.VALUE refers to the value of D3.

So we put the value of D3 as String “WELCOME TO GYANKOSH.NET”

RUNNING THE CODE AND OUTPUT

After the code is also ready.

Go back to the SHEET and right click the COUNT BUTTON and choose ASSIGN MACRO.

A list of macro will open. Choose the one which we want to associate with the button.

With our code, two macros are generated as we have made two sub procedures in our code.

To COUNT assign the macro named INCREMENT.

Similarly assign RESET with the macro named RESET.

The counter is working as per expectations.