HELLO THERE!!!
How are you,guys? I hope everyone is fine and good always. This week I will introduce you guys to a new programming sort of feature I found when I was using Excel quiet some time ago. The feature actually simplifies your job when you have a lot of data in your Excel file. The feature is known as Excel Macro. There are many applications on how to use the macro that you can find on the net. For this post, I am going to show you guys on how to move the cell selector(the darkbox thing) to left,right,up and down.
Firstly, what you need is an Excel form. Next, you need to turn on developer tab. On how to do this, please do as instructed as in the link shown.
http://www.addintools.com/documents/excel/how-to-add-developer-tab.html
|
The developer tab of Excel
|
Next let's add some button. If you see, there are two types of buttons, one is forms button and another one is activex button. Choose the activex button and draw 4 buttons on your form.
|
Choose the activeX button not the form control |
Next, let's change the name of the buttons. The buttons default name is Command button. To change the name, right click on the button, go to the command button object and click edit.
|
Changing the name of the button |
You can also change the properties of the button by right clicking the button and choose properties. A properties pane will pop up. You can change many things such as button size and color in the properties name.
|
Properties pane
Double click one of the button, to open the visual basic editor as shown in the picture below. Now we can code the program that we want here.
|
|
Visual basic editor
There are two ways actually to code a macro, one is recording (i'll explain this in the other post) and another one is coding them. My personal opinion is to code the macro as it gives more flexibility of compared to recording them. Ok, now for the coding part:
1. This is the first thing you'll see in the editor when you double click one of the button
Private Sub Up_Click()
End Sub
It is quiet similar to Arduino style of coding, where the Private Sub Up_Click() is like a void loop in the Arduino. Any code is written between the sub and End Sub. Now the code we want to add is this:
Selection.Offset(-1, 0).Select
this one line code will move the code pointer up. If you change the -1 to 1, the cell pointer will move down. What about left and right? Simple just switch the 1 and 0 and you can move left and right. To move right:
Selection.Offset(0, 1).Select
if you change the 1 to -1, the pointer will turn left. Here is a demo video of the code in action.
You can try by changing the values of 1's and 0's to other value such as 2 or 5 and see what happens .Looks like that's all from me guys this week,see you guys next week and Happy Tinkering!!!
|
Comments
Post a Comment