SIMPLE COUNTER IN EXCEL USING VBA
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.
TO CREATE A SIMPLE COUNTER WITH A COUNT AND A RESET BUTTON . GRAPHICS ARE NEEDED IN SHEET ITSELF.
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.
A button is needed which will increase the counter when clicked.
A button is needed which will reset the counter to 0 when clicked.
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
- 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.
- 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.
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.
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.
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
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.